4
Datatypes and Host Variables

This chapter provides the basic information you need to write a Pro*C/C++ program. This chapter contains the following topics:

• Oracle Datatypes
• Host Variables
• Indicator Variables
• VARCHAR Variables
• Cursor Variables
• CONTEXT Variables
• Universal ROWIDs
• Host Structures
• Pointer Variables
• Globalization Support
• NCHAR Variables

This chapter also includes several complete demonstration programs that you can study. These programs illustrate the techniques described. They are available on-line in your demo directory, so you can compile and run them, and modify them for your own uses.

Oracle Datatypes

Oracle recognizes two kinds of datatypes: internal and external. Internal datatypes specify how Oracle stores column values in database tables, as well as the formats used to represent pseudocolumn values such as NULL, SYSDATE, USER, and so on. External datatypes specify the formats used to store values in input and output host variables.

For descriptions of the Oracle internal (also called built-in) datatypes, see Oracle9i SQL Reference.

Internal Datatypes

For values stored in database columns, Oracle uses the internal datatypes shown in Table 4-1

Table 4-1 Oracle Internal Datatypes

These internal datatypes can be quite different from C datatypes. For example, C has no datatype that is equivalent to the Oracle NUMBER datatype. However, NUMBERs can be converted between C datatypes such as float and double, with some restrictions. For example, the Oracle NUMBER datatype allows up to 38 decimal digits of precision, while no current C implementations can represent double with that degree of precision.

The Oracle NUMBER datatype represents values exactly (within the precision limits), while floating-point formats cannot represent values such as 10.0 exactly.

Use the LOB datatypes to store unstructured data (text, graphic images, video clips, or sound waveforms). BFILE data is stored in an operating system file outside the database. LOB types store locators that specify the location of the data.

NCHAR and NVARCHAR2 are used to store multibyte character data.

External Datatypes

As shown in Table 4-2, the external datatypes include all the internal datatypes plus several datatypes that closely match C constructs. For example, the STRING external datatype refers to a C null-terminated string.

Table 4-2 Oracle External Datatypes

Brief descriptions of the Oracle datatypes follow.

VARCHAR2

You use the VARCHAR2 datatype to store variable-length character strings. The maximum length of a VARCHAR2 value is 64K bytes.

You specify the maximum length of a VARCHAR2(n) value in bytes, not characters. So, if a VARCHAR2(n) variable stores multibyte characters, its maximum length can be less than n characters.

When you precompile using the option CHAR_MAP=VARCHAR2, Oracle assigns the VARCHAR2 datatype to all host variables that you declare as char[n] or char.

On Input

Oracle reads the number of bytes specified for the input host variable, strips any trailing blanks, then stores the input value in the target database column. Be careful. An uninitialized host variable can contain NULLs. So, always blank-pad a character input host variable to its declared length, and do not null-terminate it.

If the input value is longer than the defined width of the database column, Oracle generates an error. If the input value contains nothing but blanks, Oracle treats it like a NULL.

Oracle can convert a character value to a NUMBER column value if the character value represents a valid number. Otherwise, Oracle generates an error.

On Output

Oracle returns the number of bytes specified for the output host variable, blank-padding if necessary. It then assigns the output value to the target host variable. If a NULL is returned, Oracle fills the host variable with blanks.

If the output value is longer than the declared length of the host variable, Oracle truncates the value before assigning it to the host variable. If there is an indicator variable associated with the host variable, Oracle sets it to the original length of the output value.

Oracle can convert NUMBER column values to character values. The length of the character host variable determines precision. If the host variable is too short for the number, scientific notation is used. For example, if you SELECT the column value 123456789 into a character host variable of length 6, Oracle returns the value '1.2E08'. If a NULL is selected explicitly, the value in the host variable is indeterminate. The value of the indicator variable needs to be checked for NULL-ness.

NUMBER

You use the NUMBER datatype to store fixed or floating-point numbers. You can specify precision and scale. The maximum precision of a NUMBER value is 38. The magnitude range is 1.0E-130 to 9.99...9E125 (38 nines followed by 88 zeroes). Scale can range from -84 to 127.

NUMBER values are stored in a variable-length format, starting with an exponent byte and followed by 19 mantissa bytes. The high-order bit of the exponent byte is a sign bit, which is set for positive numbers. The low-order 7 bits represent the magnitude.

The mantissa forms a 38-digit number with each byte representing 2 of the digits in a base-100 format. The sign of the mantissa is specified by the value of the first (left-most) byte. If greater than 101 then the mantissa is negative and the first digit of the mantissa is equal to the left-most byte minus 101.

On output, the host variable contains the number as represented internally by Oracle. To accommodate the largest possible number, the output host variable must be 22 bytes long. Only the bytes used to represent the number are returned. Oracle does not blank-pad or null-terminate the output value. If you need to know the length of the returned value, use the VARNUM datatype instead.

There is seldom a need to use this external datatype.

INTEGER

You use the INTEGER datatype to store numbers that have no fractional part. An integer is a signed, 2-byte or 4-byte binary number. The order of the bytes in a word is system dependent. You must specify a length for input and output host variables. On output, if the column value is a real number, Oracle truncates any fractional part.

FLOAT

You use the FLOAT datatype to store numbers that have a fractional part or that exceed the capacity of the INTEGER datatype. The number is represented using the floating-point format of your computer and typically requires 4 or 8 bytes of storage. You must specify a length for input and output host variables.

Oracle can represent numbers with greater precision than most floating-point implementations because the internal format of Oracle numbers is decimal. This can cause a loss of precision when fetching into a FLOAT variable.

STRING

The STRING datatype is like the VARCHAR2 datatype, except that a STRING value is always null-terminated. When you precompile using the option CHAR_MAP=STRING, Oracle assigns the STRING datatype to all host variables that you declare as char[n] or char.

On Input

Oracle uses the specified length to limit the scan for the null terminator. If a null terminator is not found, Oracle generates an error. If you do not specify a length, Oracle assumes the maximum length of 2000 bytes. The minimum length of a STRING value is 2 bytes. If the first character is a null terminator and the specified length is 2, Oracle inserts a null unless the column is defined as NOT NULL. If the column is defined as NOT NULL, an error occurs. An all-blank value is stored intact.

On Output

Oracle appends a null byte to the last character returned. If the string length exceeds the specified length, Oracle truncates the output value and appends a null byte. If a NULL is SELECTed, Oracle returns a null byte in the first character position. If a NULL is selected explicitly, the value in the host variable is indeterminate. The value of the indicator variable needs to be checked for NULL-ness.

VARNUM

The VARNUM datatype is like the NUMBER datatype, except that the first byte of a VARNUM variable stores the length of the representation.

On input, you must set the first byte of the host variable to the length of the value. On output, the host variable contains the length followed by the number as represented internally by Oracle. To accommodate the largest possible number, the host variable must be 22 bytes long. After SELECTing a column value into a VARNUM host variable, you can check the first byte to get the length of the value.

Normally, there is little reason to use this datatype.

LONG

You use the LONG datatype to store fixed-length character strings.

The LONG datatype is like the VARCHAR2 datatype, except that the maximum length of a LONG value is 2147483647 bytes or two gigabytes.

VARCHAR

You use the VARCHAR datatype to store variable-length character strings. VARCHAR variables have a 2-byte length field followed by a <=65533-byte string field. However, for VARCHAR array elements, the maximum length of the string field is 65530 bytes. When you specify the length of a VARCHAR variable, be sure to include 2 bytes for the length field. For longer strings, use the LONG VARCHAR datatype. If a NULL is selected explicitly, the value in the host variable is indeterminate. The value of the indicator variable needs to be checked for NULL-ness.

ROWID

Before the release of Oracle8, ROWID datatype was used to store the physical address of each row of each table, as a hexadecimal number. The ROWID contained the physical address of the row and allowed you to retrieve the row in a single efficient block access.

With Oracle8, the logical ROWID was introduced. Rows in Index-Organized tables do not have permanent physical addresses. The logical ROWID is accessed using the same syntax as the physical ROWID. For this reason, the physical ROWID was expanded in size to include a data object number (schema objects in the same segment).

To support both logical and physical ROWIDs (as well as ROWIDs of non-Oracle tables) the universal ROWID was defined.

You can use character host variables to store rowids in a readable format. When you SELECT or FETCH a rowid into a character host variable, Oracle converts the binary value to an 18-byte character string and returns it in the format

BBBBBBBB.RRRR.FFFF 

where BBBBBBBB is the block in the database file, RRRR is the row in the block (the first row is 0), and FFFF is the database file. These numbers are hexadecimal. For example, the rowid

0000000E.000A.0007 
points to the 11th row in the 15th block in the 7th database file. 

Typically, you FETCH a rowid into a character host variable, then compare the host variable to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement. That way, you can identify the latest row fetched by a cursor.

DATE

You use the DATE datatype to store dates and times in 7-byte, fixed-length fields. As Table 4-3 shows, the century, year, month, day, hour (in 24-hour format), minute, and second are stored in that order from left to right.

Table 4-3 DATE Format

The century and year bytes are in excess-100 notation. The hour, minute, and second are in excess-1 notation. Dates before the Common Era (B.C.E.) are less than 100. The epoch is January 1, 4712 B.C.E. For this date, the century byte is 53 and the year byte is 88. The hour byte ranges from 1 to 24. The minute and second bytes range from 1 to 60. The time defaults to midnight (1, 1, 1).

Normally, there is little reason to use the DATE datatype.

RAW

You use the RAW datatype to store binary data or byte strings. The maximum length of a RAW value is 65535 bytes.

RAW data is like CHARACTER data, except that Oracle assumes nothing about the meaning of RAW data and does no character set conversions when you transmit RAW data from one system to another.

VARRAW

You use the VARRAW datatype to store variable-length binary data or byte strings. The VARRAW datatype is like the RAW datatype, except that VARRAW variables have a 2-byte length field followed by a data field <= 65533 bytes in length. For longer strings, use the LONG VARRAW datatype.

When you specify the length of a VARRAW variable, be sure to include 2 bytes for the length field. The first two bytes of the variable must be interpretable as an integer.

To get the length of a VARRAW variable, simply refer to its length field.

LONG RAW

You use the LONG RAW datatype to store binary data or byte strings. The maximum length of a LONG RAW value is 2147483647 bytes or two gigabytes.

LONG RAW data is like LONG data, except that Oracle assumes nothing about the meaning of LONG RAW data and does no character set conversions when you transmit LONG RAW data from one system to another.

UNSIGNED

You use the UNSIGNED datatype to store unsigned integers. An unsigned integer is a binary number of 2 or 4 bytes. The order of the bytes in a word is system dependent. You must specify a length for input and output host variables. On output, if the column value is a floating-point number, Oracle truncates the fractional part.

LONG VARCHAR

You use the LONG VARCHAR datatype to store variable-length character strings. LONG VARCHAR variables have a 4-byte length field followed by a string field. The maximum length of the string field is 2147483643 (2**31 - 5) bytes. When you specify the length of a LONG VARCHAR for use in a VAR or TYPE statement, do not include the 4 length bytes.

LONG VARRAW

You use the LONG VARRAW datatype to store variable-length binary data or byte strings. LONG VARRAW variables have a 4-byte length field followed by a data field. The maximum length of the data field is 2147483643 bytes. When you specify the length of a LONG VARRAW for use in a VAR or TYPE statement, do not include the 4 length bytes.

CHAR

You use the CHAR datatype to store fixed-length character strings. The maximum length of a CHAR value is 65535 bytes.

On Input

Oracle reads the number of bytes specified for the input host variable, does not strip trailing blanks, then stores the input value in the target database column.

If the input value is longer than the defined width of the database column, Oracle generates an error. If the input value is all-blank, Oracle treats it like a character value.

On Output

Oracle returns the number of bytes specified for the output host variable, doing blank-padding if necessary, then assigns the output value to the target host variable. If a NULL is returned, Oracle fills the host variable with blanks.

If the output value is longer than the declared length of the host variable, Oracle truncates the value before assigning it to the host variable. If an indicator variable is available, Oracle sets it to the original length of the output value. If a NULL is selected explicitly, the value in the host variable is indeterminate. The value of the indicator variable needs to be checked for NULL-ness.

CHARZ

When DBMS=V7 or V8, Oracle, by default, assigns the CHARZ datatype to all character host variables in a Pro*C/C++ program. The CHARZ datatype indicates fixed-length, null-terminated character strings. The maximum length of a CHARZ value is 65534 bytes.

On Input

The CHARZ and STRING datatypes work the same way. You must null-terminate the input value. The null terminator serves only to delimit the string; it does not become part of the stored data.

On Output

CHARZ host variables are blank-padded if necessary, then null-terminated. The output value is always null-terminated, even if data must be truncated. If a NULL is selected explicitly, the value in the host variable is indeterminate. The value of the indicator variable needs to be checked for NULL-ness.

CHARF

The CHARF datatype is used in EXEC SQL TYPE and EXEC SQL VAR statements. When you precompile with the DBMS option set to V7 or V8, specifying the external datatype CHAR in a TYPE or VAR statement equivalences the C type or variable to the fixed-length, null-terminated datatype CHARZ.

However, you might not want either of these type equivalences, but rather an equivalence to the fixed-length external type CHAR. If you use the external type CHARF, the C type or variable is always equivalenced to the fixed-length ANSI datatype CHAR, regardless of the DBMS value. CHARF never allows the C type to be equivalenced to VARCHAR2 or CHARZ. Alternatively, when you set the option CHAR_MAP=CHARF, all host variables declared as char[n] or char are equivalenced to a CHAR string. If a NULL is selected explicitly, the value in the host variable is indeterminate. The value of the indicator variable needs to be checked for NULL-ness.

Additional External Datatypes

Datetime and Interval Datatypes

The datetime and interval datatypes are briefly summarized here.

ANSI DATE

The ANSI DATE is based on the DATE, but contains no time portion. (Therefore, it also has no time zone.) ANSI DATE follows the ANSI specification for the DATE datatype. When assigning an ANSI DATE to a DATE or a timestamp datatype, the time portion of the Oracle DATE and the timestamp are set to zero. When assigning a DATE or a timestamp to an ANSI DATE, the time portion is ignored.

You are encouraged to instead use the TIMESTAMP datatype which contains both date and time.

TIMESTAMP

The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus the hour, minute, and second values. It has no time zone. The TIMESTAMP datatype has the form:

TIMESTAMP(fractional_seconds_precision) 

where fractional_seconds_precision (which is optional) specifies the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0 to 9. The default is 6.

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE (TSTZ) is a variant of TIMESTAMP that includes an explicit time zone displacement in its value. The time zone displacement is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time--formerly Greenwich Mean Time). The TIMESTAMP WITH TIME ZONE datatype has the form:

TIMESTAMP(fractional_seconds_precision) WITH TIME ZONE

where fractional_seconds_precision optionally specifies the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0 to 9. The default is 6.

Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instant in UTC, regardless of the TIME ZONE offsets stored in the data.

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) is another variant of TIMESTAMP that includes a time zone displacement in its value. Storage is in the same format as for TIMESTAMP. This type differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone displacement is not stored as part of the column data. When users retrieve the data, Oracle returns it in the users' local session time zone.

The time zone displacement is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time--formerly Greenwich Mean Time). The TIMESTAMP WITH LOCAL TIME ZONE datatype has the form:

TIMESTAMP(fractional_seconds_precision) WITH LOCAL TIME ZONE

where fractional_seconds_precision optionally specifies the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0 to 9. The default is 6.

INTERVAL YEAR TO MONTH

INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields. The INTERVAL YEAR TO MONTH datatype has the form:

INTERVAL YEAR(year_precision) TO MONTH

where the optional year_precision is the number of digits in the YEAR datetime field. The default value of year_precision is 2.

INTERVAL DAY TO SECOND

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. The INTERVAL DAY TO SECOND datatype has the form:

INTERVAL DAY (day_precision) TO SECOND(fractional_seconds_precision)

where:

• day_precision is the number of digits in the DAY datetime field. It is optional. Accepted values are 0 to 9. The default is 2.

fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. It is optional. Accepted values are 0 to 9. The default is 6.

Avoiding Unexpected Results Using Datetime

Host Variables

Host variables are the key to communication between your host program and Oracle. Typically, a precompiler program inputs data from a host variable to Oracle, and Oracle outputs data to a host variable in the program. Oracle stores input data in database columns, and stores output data in program host variables.

A host variable can be any arbitrary C expression that resolves to a scalar type. But, a host variable must also be an lvalue. Host arrays of most host variables are also supported.

Host Variable Declaration

You declare a host variable according to the rules of the C programming language, specifying a C datatype supported by the Oracle program interface. The C datatype must be compatible with that of the source or target database column.

If MODE=ORACLE, you do not have to declare host variables in a special Declare Section. However, if you do not use a Declare Section, the FIPS flagger warns you about this, as the Declare Section is part of the ANSI SQL Standard. If CODE=CPP (you are compiling C++ code) or PARSE=NONE or PARSE=PARTIAL, then you must have a Declare Section.

Table 4-4 shows the C datatypes and the pseudotypes that you can use when declaring host variables. Only these datatypes can be used for host variables.

Table 4-4 C Datatypes for Host Variables

Table 4-5 shows the compatible Oracle internal datatypes.

Table 4-5 C to Oracle Datatype Compatibility

One-dimensional arrays of simple C types can also serve as host variables. For char[n] and VARCHAR[n], n specifies the maximum string length, not the number of strings in the array. Two-dimensional arrays are allowed only for char[m][n] and VARCHAR[m][n], where m specifies the number of strings in the array and n specifies the maximum string length.

Pointers to simple C types are supported. Pointers to char[n] and VARCHAR[n] variables should be declared as pointer to char or VARCHAR (with no length specification). Arrays of pointers, however, are not supported.

Storage-Class Specifiers

Pro*C/C++ lets you use the auto, extern, and static storage-class specifiers when you declare host variables. However, you cannot use the register storage-class specifier to store host variables, since the precompiler takes the address of host variables by placing an ampersand (&) before them. Following the rules of C, you can use the auto storage class specifier only within a block.

To comply with the ANSI C standard, the Pro*C/C++ Precompiler provides the ability to declare an extern char[n] host variable with or without a maximum length, as the following examples shows:

extern char  protocol[15]; 
extern char  msg[]; 

However, you should always specify the maximum length. In the last example, if msg is an output host variable declared in one precompilation unit but defined in another, the precompiler has no way of knowing its maximum length. If you have not allocated enough storage for msg in the second precompilation unit, you might corrupt memory. (Usually, "enough" is the number of bytes in the longest column value that might be SELECTed or FETCHed into the host variable, plus one byte for a possible null terminator.)

If you neglect to specify the maximum length for an extern char[ ] host variable, the precompiler issues a warning message. The precompiler also assumes that the host variable will store a CHARACTER column value, which cannot exceed 255 characters in length. So, if you want to SELECT or FETCH a VARCHAR2 or a LONG column value of length greater than 255 characters into the host variable, you must specify a maximum length.

Type Qualifiers

You can also use the const and volatile type qualifiers when you declare host variables.

A const host variable must have a constant value, that is, your program cannot change its initial value. A volatile host variable can have its value changed in ways unknown to your program (for example, by a device attached to the system).

Host Variable Referencing

You use host variables in SQL data manipulation statements. A host variable must be prefixed with a colon (:) in SQL statements but must not be prefixed with a colon in C statements, as the following example shows:

char    buf[15];
int     emp_number; 
float   salary; 
... 
gets(buf); 
emp_number = atoi(buf); 

EXEC SQL SELECT sal INTO :salary FROM emp 
     WHERE empno = :emp_number; 

Though it might be confusing, you can give a host variable the same name as an Oracle table or column, as this example shows:

int     empno; 
char    ename[10]; 
float   sal; 
... 
EXEC SQL SELECT ename, sal INTO :ename, :sal FROM emp 
     WHERE empno = :empno; 

Restrictions

A host variable name is a C identifier, hence it must be declared and referenced in the same upper/lower case format. It cannot substitute for a column, table, or other Oracle object in a SQL statement, and must not be an Oracle reserved word.

A host variable must resolve to an address in the program. For this reason, function calls and numeric expressions cannot serve as host variables. The following code is invalid:

#define MAX_EMP_NUM    9000 
... 
int get_dept(); 
... 
EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES 
    (:MAX_EMP_NUM + 10, 'CHEN', :get_dept()); 

Indicator Variables

You can associate every host variable with an optional indicator variable. An indicator variable must be defined as a 2-byte integer and, in SQL statements, must be prefixed with a colon and immediately follow its host variable (unless you use the keyword INDICATOR). If you are using Declare Sections, you must also declare indicator variables inside the Declare Sections.

This applies to relational columns, not object types.

The INDICATOR Keyword

To improve readability, you can precede any indicator variable with the optional keyword INDICATOR. You must still prefix the indicator variable with a colon. The correct syntax is:

:host_variable INDICATOR :indicator_variable 

which is equivalent to

:host_variable:indicator_variable 

You can use both forms of expression in your host program.

Possible indicator values, and their meanings, are:

Example of INDICATOR Variable Usage

Typically, you use indicator variables to assign NULLs to input host variables and detect NULLs or truncated values in output host variables. In the example later, you declare three host variables and one indicator variable, then use a SELECT statement to search the database for an employee number matching the value of host variable emp_number. When a matching row is found, Oracle sets output host variables salary and commission to the values of columns SAL and COMM in that row and stores a return code in indicator variable ind_comm. The next statements use ind_comm to select a course of action.

EXEC SQL BEGIN DECLARE SECTION; 
    int    emp_number; 
    float  salary, commission; 
    short comm_ind;  /* indicator variable  */
EXEC SQL END DECLARE SECTION;
    char temp[16];
    float  pay;      /* not used in a SQL statement */
...
printf("Employee number? "); 
gets(temp);
emp_number = atof(temp);
EXEC SQL SELECT SAL, COMM 
    INTO :salary, :commission:ind_comm 
    FROM EMP 
    WHERE EMPNO = :emp_number; 
if(ind_comm == -1)    /* commission is null */
    pay = salary; 
else
    pay = salary + commission; 

INDICATOR Variable Guidelines

The following guidelines apply to declaring and referencing indicator variables. An indicator variable must

• Be declared explicitly (in the Declare Section if present) as a 2-byte integer.
• Be prefixed with a colon (:) in SQL statements.
• Immediately follow its host variable in SQL statements and PL/SQL blocks (unless preceded by the keyword INDICATOR).

An indicator variable must not:

• Be prefixed with a colon in host language statements.
• Follow its host variable in host language statements.
• Be an Oracle reserved word.

Oracle Restrictions

When DBMS=V7 or V8, if you SELECT or FETCH a NULL into a host variable that has no indicator, Oracle issues the following error message:

ORA-01405: fetched column value is NULL

When precompiling with MODE=ORACLE and DBMS=V7 or V8 specified, you can specify UNSAFE_NULL=YES to disable the ORA-01405 message.

VARCHAR Variables

You can use the VARCHAR pseudotype to declare variable-length character strings. When your program deals with strings that are output from, or input to, VARCHAR2 or LONG columns, you might find it more convenient to use VARCHAR host variables instead of standard C strings. The datatype name VARCHAR can be uppercase or lowercase, but it cannot be mixed case. In this Guide, uppercase is used to emphasize that VARCHAR is not a native C datatype.

VARCHAR Variable Declaration

Think of a VARCHAR as an extended C type or pre-declared struct. For example, the precompiler expands the VARCHAR declaration

VARCHAR   username[20];
 

into the following struct with array and length members:

struct 
{ 
     unsigned short  len; 
     unsigned char   arr[20]; 
} username; 

The advantage of using VARCHAR variables is that you can explicitly reference the length member of the VARCHAR structure after a SELECT or FETCH. Oracle puts the length of the selected character string in the length member. You can then use this member to do things such as adding the null ('\0') terminator.

username.arr[username.len] = '\0'; 

or using the length in a strncpy or printf statement; for example:

printf("Username is %.*s\n", username.len, username.arr);

You specify the maximum length of a VARCHAR variable in its declaration. The length must lie in the range 1.65533. For example, the following declaration is invalid because no length is specified:

VARCHAR   null_string[];    /* invalid */

The length specification holds the current length of the value stored in the array member.

You can declare multiple VARCHARs on a single line; for example:

VARCHAR emp_name[ENAME_LEN], dept_loc[DEPT_NAME_LEN];

The length specifier for a VARCHAR can be a #defined macro, or any complex expression that can be resolved to an integer at precompile time.

You can also declare pointers to VARCHAR datatypes. See the section

typedef VARCHAR buf[64]; 

VARCHAR Variable Referencing

In SQL statements, you reference VARCHAR variables using the struct name prefixed with a colon, as the following example shows:

... 
int       part_number; 
VARCHAR   part_desc[40]; 
... 
main() 
{ 
    ... 
    EXEC SQL SELECT pdesc INTO :part_desc 
        FROM parts 
        WHERE pnum = :part_number; 
    ... 

After the query is executed, part_desc.len holds the actual length of the character string retrieved from the database and stored in part_desc.arr.

In C statements, you reference VARCHAR variables using the component names, as the next example shows:

printf("\n\nEnter part description: "); 
gets(part_desc.arr); 
/* You must set the length of the string
   before using the VARCHAR in an INSERT or UPDATE */
part_desc.len = strlen(part_desc.arr); 

Return NULLs to a VARCHAR Variable

Oracle automatically sets the length component of a VARCHAR output host variable. If you SELECT or FETCH a NULL into a VARCHAR, the server does not change the length or array members.

Insert NULLs Using VARCHAR Variables

If you set the length of a VARCHAR variable to zero before performing an UPDATE or INSERT statement, the column value is set to NULL. If the column has a NOT NULL constraint, Oracle returns an error.

Pass VARCHAR Variables to a Function

VARCHARs are structures, and most C compilers permit passing of structures to a function by value, and returning structures by copy out from functions. However, in Pro*C/C++ you must pass VARCHARs to functions by reference. The following example shows the correct way to pass a VARCHAR variable to a function:

VARCHAR emp_name[20]; 
... 
emp_name.len = 20; 
SELECT ename INTO :emp_name FROM emp 
WHERE empno = 7499; 
... 
print_employee_name(&emp_name); /* pass by pointer */ 
... 
 
print_employee_name(name) 
VARCHAR *name; 
{ 
    ... 
    printf("name is %.*s\n", name->len, name->arr); 
    ... 
}

Find the Length of the VARCHAR Array Component

When the precompiler processes a VARCHAR declaration, the actual length of the array element in the generated structure can be longer than that declared. For example, on a Sun Solaris system, the Pro*C/C++ declaration

VARCHAR my_varchar[12];
 

is expanded by the precompiler to

struct my_varchar
{
    unsigned short len;
    unsigned char  arr[12];
};

However, the precompiler or the C compiler on this system pads the length of the array component to 14 bytes. This alignment requirement pads the total length of the structure to 16 bytes: 14 for the padded array and 2 bytes for the length.

The SQLVarcharGetLength() (replaces the non-threaded sqlvcp()) function--part of the SQLLIB runtime library--returns the actual (possibly padded) length of the array member.

You pass the SQLVarcharGetLength() function the length of the data for a VARCHAR host variable or a VARCHAR pointer host variable, and SQLVarcharGetLength() returns the total length of the array component of the VARCHAR. The total length includes any padding that might be added by your C compiler.

The syntax of SQLVarcharGetLength() is

SQLVarcharGetLength (dvoid *context, unsigned long *datlen, unsigned long 
*totlen); 

For single-threaded applications, use sqlvcp(). Put the length of the VARCHAR in the datlen parameter before calling sqlvcp(). When the function returns, the totlen parameter contains the total length of the array element. Both parameters are pointers to unsigned long integers, so must be passed by reference.

Example Program: Using sqlvcp()

The following example program shows how you can use the function in a Pro*C/C++ application. The example also uses the sqlgls() function. The example declares a VARCHAR pointer, then uses the sqlvcp() function to determine the size required for the VARCHAR buffer. The program FETCHes employee names from the EMP table and prints them. Finally, the example uses the sqlgls() function to print out the SQL statement and its function code and length attributes. This program is available on-line as sqlvcp.pc in your demo directory.

/*
 *  The sqlvcp.pc program demonstrates how you can use the
 *  sqlvcp() function to determine the actual size of a
 *  VARCHAR struct. The size is then used as an offset to
 *  increment a pointer that steps through an array of
 *  VARCHARs.
 *
 *  This program also demonstrates the use of the sqlgls()
 *  function, to get the text of the last SQL statement executed.
 *  sqlgls() is described in the "Error Handling" chapter of
 *  The Programmer's Guide to the Oracle Pro*C/C++ Precompiler.
 */

#include <stdio.h> 
#include <sqlca.h>
#include <sqlcpr.h>

/*  Fake a VARCHAR pointer type. */ 

struct my_vc_ptr 
{ 
    unsigned short len; 
    unsigned char arr[32767]; 
}; 

/* Define a type for the VARCHAR pointer */
typedef struct my_vc_ptr my_vc_ptr; 
my_vc_ptr *vc_ptr; 


EXEC SQL BEGIN DECLARE SECTION; 
VARCHAR *names;  
int      limit;    /* for use in FETCH FOR clause  */ 
char    *username = "scott/tiger"; 
EXEC SQL END DECLARE SECTION; 
void sql_error(); 
extern void sqlvcp(), sqlgls(); 

main() 
{ 
    unsigned int vcplen, function_code, padlen, buflen; 
    int i; 
    char stmt_buf[120]; 

    EXEC SQL WHENEVER SQLERROR DO sql_error(); 

    EXEC SQL CONNECT :username; 
    printf("\nConnected.\n"); 
     
/*  Find number of rows in table. */ 
    EXEC SQL SELECT COUNT(*) INTO :limit FROM emp; 
     
     
/*  Declare a cursor for the FETCH statement. */ 
    EXEC SQL DECLARE emp_name_cursor CURSOR FOR 
    SELECT ename FROM emp; 
    EXEC SQL FOR :limit OPEN emp_name_cursor; 
     
/*  Set the desired DATA length for the VARCHAR. */ 
    vcplen = 10; 
     
/*  Use SQLVCP to help find the length to malloc. */ 
    sqlvcp(&vcplen, &padlen); 
    printf("Actual array length of VARCHAR is %ld\n", padlen); 
     
/*  Allocate the names buffer for names. 
    Set the limit variable for the FOR clause. */ 
    names = (VARCHAR *) malloc((sizeof (short) + 
    (int) padlen) * limit); 
    if (names == 0) 
    { 
        printf("Memory allocation error.\n"); 
        exit(1); 
    }
/*  Set the maximum lengths before the FETCH. 
 *  Note the "trick" to get an effective VARCHAR *.
 */ 
    for (vc_ptr = (my_vc_ptr *) names, i = 0; i < limit; i++) 
    { 
        vc_ptr->len = (short) padlen; 
        vc_ptr = (my_vc_ptr *)((char *) vc_ptr + 
        padlen + sizeof (short)); 
    } 
/*  Execute the FETCH. */ 
    EXEC SQL FOR :limit FETCH emp_name_cursor INTO :names; 
     
/*  Print the results. */ 
    printf("Employee names--\n"); 
    
    for (vc_ptr = (my_vc_ptr *) names, i = 0; i < limit; i++) 
    { 
        printf
         ("%.*s\t(%d)\n", vc_ptr->len, vc_ptr->arr, vc_ptr->len); 
        vc_ptr = (my_vc_ptr *)((char *) vc_ptr + 
                  padlen + sizeof (short)); 
    } 
     
/*  Get statistics about the most recent 
 *  SQL statement using SQLGLS. Note that 
 *  the most recent statement in this example 
 *  is not a FETCH, but rather "SELECT ENAME FROM EMP" 
 *  (the cursor).
 */ 
    buflen = (long) sizeof (stmt_buf); 
    
/*  The returned value should be 1, indicating no error. */ 
    sqlgls(stmt_buf, &buflen, &function_code);
    if (buflen != 0)
    { 
        /* Print out the SQL statement. */ 
        printf("The SQL statement was--\n%.*s\n", buflen, stmt_buf); 
     
        /* Print the returned length. */ 
        printf("The statement length is %ld\n", buflen); 
     
        /* Print the attributes. */ 
        printf("The function code is %ld\n", function_code); 
    
        EXEC SQL COMMIT RELEASE; 
        exit(0); 
    }
    else 
    { 
        printf("The SQLGLS function returned an error.\n"); 
        EXEC SQL ROLLBACK RELEASE; 
        exit(1); 
    } 
} 

void
sql_error() 
{ 
    char err_msg[512]; 
    int buf_len, msg_len;

     
    EXEC SQL WHENEVER SQLERROR CONTINUE; 
 
    buf_len = sizeof (err_msg); 
    sqlglm(err_msg, &buf_len, &msg_len); 
    printf("%.*s\n", msg_len, err_msg); 
 
    EXEC SQL ROLLBACK RELEASE; 
    exit(1); 
} 

Cursor Variables

You can use cursor variables in your Pro*C/C++ program for queries. A cursor variable is a handle for a cursor that must be defined and opened on the Oracle (release 7.2 or later) server, using PL/SQL. See the PL/SQL User's Guide and Reference for complete information about cursor variables.

The advantages of cursor variables are:

• Ease of maintenance

  Queries are centralized, in the stored procedure that opens the cursor variable. If you need to change the cursor, you only need to make the change in one place: the stored procedure. There is no need to change each application.

• Convenient security

  The user of the application is the username used when the Pro*C/C++ application connects to the server. The user must have execute permission on the stored procedure that opens the cursor but not read permission on the tables used in the query. This capability can be used to limit access to the columns in the table, and access to other stored procedures.

Declare a Cursor Variable

You declare a cursor variable in your Pro*C/C++ program using the Pro*C/C++ pseudotype SQL_CURSOR. For example:

EXEC SQL BEGIN DECLARE SECTION;
    sql_cursor     emp_cursor;             /* a cursor variable */
    SQL_CURSOR     dept_cursor;      /* another cursor variable */
    sql_cursor     *ecp;      /* a pointer to a cursor variable */
    ...
EXEC SQL END DECLARE SECTION;
ecp = &emp_cursor;             /* assign a value to the pointer */

You can declare a cursor variable using the type specification SQL_CURSOR, in all upper case, or sql_cursor, in all lower case; you cannot use mixed case.

A cursor variable is just like any other host variable in the Pro*C/C++ program. It has scope, following the scope rules of C. You can pass it as a parameter to other functions, even functions external to the source file in which you declared it. You can also define functions that return cursor variables, or pointers to cursor variables.

Allocate a Cursor Variable

Before you can use a cursor variable, either to open it or to FETCH it, you must allocate the cursor. You do this using the new precompiler command ALLOCATE. For example, to allocate the SQL_CURSOR emp_cursor that was declared in the example earlier, you write the statement:

EXEC SQL ALLOCATE :emp_cursor;

Allocating a cursor does not require a call to the server, either at precompile time or at runtime. If the ALLOCATE statement contains an error (for example, an undeclared host variable), Pro*C/C++ issues a precompile-time error. Allocating a cursor variable does cause heap memory to be used. For this reason, you can free a cursor variable in a program loop. Memory allocated for cursor variables is not freed when the cursor is closed, but only when an explicit CLOSE is executed, or the connection is closed:

EXEC SQL CLOSE :emp_cursor;

Open a Cursor Variable

You must open a cursor variable on the Oracle database server. You cannot use the embedded SQL OPEN command to open a cursor variable. You can open a cursor variable either by calling a PL/SQL stored procedure that opens the cursor (and defines it in the same statement). Or, you can open and define a cursor variable using an anonymous PL/SQL block in your Pro*C/C++ program.

For example, consider the following PL/SQL package, stored in the database:

CREATE PACKAGE demo_cur_pkg AS
    TYPE EmpName IS RECORD (name VARCHAR2(10));
    TYPE cur_type IS REF CURSOR RETURN EmpName;
    PROCEDURE open_emp_cur (
               curs     IN OUT cur_type,
               dept_num IN     NUMBER);
END;

CREATE PACKAGE BODY demo_cur_pkg AS
    CREATE PROCEDURE open_emp_cur (
               curs     IN OUT cur_type,
               dept_num IN     NUMBER) IS
    BEGIN
        OPEN curs FOR
            SELECT ename FROM emp
                WHERE deptno = dept_num
                ORDER BY ename ASC;
    END;
END;

After this package has been stored, you can open the cursor curs by calling the open_emp_cur stored procedure from your Pro*C/C++ program, and FETCH from the cursor in the program. For example:

...
sql_cursor    emp_cursor;
char          emp_name[11];
...
EXEC SQL ALLOCATE :emp_cursor;  /* allocate the cursor variable */
...
/* Open the cursor on the server side. */
EXEC SQL EXECUTE
    begin
        demo_cur_pkg.open_emp_cur(:emp_cursor, :dept_num);
    end;
;
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
    EXEC SQL FETCH :emp_cursor INTO :emp_name;
    printf("%s\n", emp_name);
}
...

To open a cursor using a PL/SQL anonymous block in your Pro*C/C++ program, you define the cursor in the anonymous block. For example:

sql_cursor emp_cursor;
int dept_num = 10;
...
EXEC SQL EXECUTE
    BEGIN
        OPEN :emp_cursor FOR SELECT ename FROM emp
             WHERE deptno = :dept_num;
    END;
END-EXEC;
...

The earlier examples show how to use PL/SQL to open a cursor variable. You can also open a cursor variable using embedded SQL with the CURSOR clause:

...
sql_cursor emp_cursor;
...
EXEC ORACLE OPTION(select_error=no);
EXEC SQL
    SELECT CURSOR(SELECT ename FROM emp WHERE deptno = :dept_num)
    INTO :emp_cursor FROM DUAL;
EXEC ORACLE OPTION(select_error=yes);

In the statement earlier, the emp_cursor cursor variable is bound to the first column of the outermost select. The first column is itself a query, but it is represented in the form compatible with a sql_cursor host variable since the CURSOR(...) conversion clause is used.

Before using queries which involve the CURSOR clause, you must set the SELECT_ERROR option to NO. This will prevent the cancellation of the parent cursor and allow the program to run without errors.

Opening in a Standalone Stored Procedure

In the example earlier, a reference cursor was defined inside a package, and the cursor was opened in a procedure in that package. But it is not always necessary to define a reference cursor inside the package that contains the procedures that open the cursor.

If you need to open a cursor inside a standalone stored procedure, you can define the cursor in a separate package, and then reference that package in the standalone stored procedure that opens the cursor. Here is an example:

PACKAGE dummy IS
    TYPE EmpName IS RECORD (name VARCHAR2(10));
    TYPE emp_cursor_type IS REF CURSOR RETURN EmpName;
END;
-- and then define a standalone procedure:
PROCEDURE open_emp_curs (
      emp_cursor IN OUT dummy.emp_cursor_type;
      dept_num   IN     NUMBER) IS
    BEGIN
        OPEN emp_cursor FOR
            SELECT ename FROM emp WHERE deptno = dept_num;
    END;
END;

Return Types

When you define a reference cursor in a PL/SQL stored procedure, you must declare the type that the cursor returns. See the PL/SQL User's Guide and Reference for complete information on the reference cursor type and its return types.

Closing and Freeing a Cursor Variable

Use the CLOSE command to close a cursor variable. For example, to close the emp_cursor cursor variable that was OPENed in the examples earlier, use the embedded SQL statement:

EXEC SQL CLOSE :emp_cursor;

The cursor variable is a host variable, and so you must precede it with a colon.

You can reuse ALLOCATEd cursor variables. You can open, FETCH, and CLOSE as many times as needed for your application. However, if you disconnect from the server, then reconnect, you must re-ALLOCATE cursor variables.

Cursors are deallocated by the FREE embedded SQL statement. For example:

EXEC SQL FREE :emp_cursor;

If the cursor is still open, it is closed and the memory allocated for it is released.

Cursor Variables with the OCI (Release 7 Only)

You can share a Pro*C/C++ cursor variable with an OCI function. To do so, you must use the SQLLIB conversion functions, SQLCDAFromResultSetCursor() (formerly known as sqlcdat()) and SQLCDAToResultSetCursor (formerly known as sqlcurt()). These functions convert between OCI cursor data areas and Pro*C/C++ cursor variables.

The SQLCDAFromResultSetCursor() function translates an allocated cursor variable to an OCI cursor data area. The syntax is:

void SQLCDAFromResultSetCursor(dvoid *context, Cda_Def *cda, void *cur, 
   sword *retval);

where the parameters are:

The SQLCDAToResultSetCursor() function translates an OCI cursor data area to a Pro*C/C++ cursor variable. The syntax is:

void SQLCDAToResultSetCursor(dvoid *context, void *cur, Cda_Def *cda, 
   int *retval);

where the parameters are:

ANSI and K&R prototypes for these functions are provided in the sql2oci.h header file. Memory for both cda and cur must be allocated prior to calling these functions.

Restrictions

The following restrictions apply to the use of cursor variables:

• If you use the same cursor variable in Pro*C/C++ and OCI V7, then you must use either SQLLDAGetCurrent() or SQLLDAGetName() immediately after connecting.
• You cannot translate a cursor variable to an OCI release 8 equivalent.
• You cannot use cursor variables with dynamic SQL.
• You can only use cursor variables with the ALLOCATE, FETCH, FREE, and CLOSE commands
• The DECLARE CURSOR command does not apply to cursor variables.
• You cannot FETCH from a CLOSEd cursor variable.
• You cannot FETCH from a non-ALLOCATEd cursor variable.
• If you precompile with MODE=ANSI, it is an error to close a cursor variable that is already closed.
• You cannot use the AT clause with the ALLOCATE command, nor with the FETCH and CLOSE commands if they reference a cursor variable.
• Cursor variables cannot be stored in columns in the database.
• A cursor variable itself cannot be declared in a package specification. Only the type of the cursor variable can be declared in the package specification.
• A cursor variable cannot be a component of a PL/SQL record.

Example: cv_demo.sql and sample11.pc

The following example programs--a PL/SQL script and a Pro*C/C++ program--demonstrate how you can use cursor variables. These sources are available on-line in your demo directory. Also see another version of the same application, cv_demo.pc, in the demo directory.

cv_demo.sql

-- PL/SQL source for a package that declares and
-- opens a ref cursor
CONNECT SCOTT/TIGER;
CREATE OR REPLACE PACKAGE emp_demo_pkg as
   TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
     PROCEDURE open_cur(curs IN OUT emp_cur_type, dno IN NUMBER);
END emp_demo_pkg;
 
 
CREATE OR REPLACE PACKAGE BODY emp_demo_pkg AS
    PROCEDURE open_cur(curs IN OUT emp_cur_type, dno IN NUMBER) IS
    BEGIN 
        OPEN curs FOR SELECT *
            FROM emp WHERE deptno = dno
            ORDER BY ename ASC;
    END;
END emp_demo_pkg;

sample11.pc

/*
 *  Fetch from the EMP table, using a cursor variable.
 *  The cursor is opened in the stored PL/SQL procedure
 *  open_cur, in the EMP_DEMO_PKG package.
 *
 *  This package is available on-line in the file
 *  sample11.sql, in the demo directory.
 *
 */
 
#include <stdio.h>
#include <sqlca.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>
 
/* Error handling function. */
void sql_error(msg)
    char *msg;
{
    size_t clen, fc;
    char cbuf[128];
 
    clen = sizeof (cbuf);
    sqlgls((char *)cbuf, (size_t *)&clen, (size_t *)&fc);
 
    printf("\n%s\n", msg);
    printf("Statement is--\n%s\n", cbuf);
    printf("Function code is %ld\n\n", fc);
 
    sqlglm((char *)cbuf, (size_t *) &clen, (size_t *) &clen);
    printf ("\n%.*s\n", clen, cbuf);
  
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL ROLLBACK WORK RELEASE;
    exit(EXIT_FAILURE);
}

void main()
{
    char temp[32];
 
    EXEC SQL BEGIN DECLARE SECTION;
        char *uid = "scott/tiger";
        SQL_CURSOR emp_cursor;
        int dept_num;
        struct
        {
            int   emp_num;
            char  emp_name[11];
            char  job[10];
            int   manager;
            char  hire_date[10];
            float salary;
            float commission;
            int   dept_num;
        } emp_info;
    
        struct
        {
            short emp_num_ind;
            short emp_name_ind;
            short job_ind;
            short manager_ind;
            short hire_date_ind;
            short salary_ind;
            short commission_ind;
            short dept_num_ind;
        } emp_info_ind;
    EXEC SQL END DECLARE SECTION;
    
    EXEC SQL WHENEVER SQLERROR do sql_error("Oracle error");
    
/* Connect to Oracle. */
    EXEC SQL CONNECT :uid;
 
/* Allocate the cursor variable. */
    EXEC SQL ALLOCATE :emp_cursor;
 
/* Exit the inner for (;;) loop when NO DATA FOUND. */
    EXEC SQL WHENEVER NOT FOUND DO break;
 
    for (;;)
    {
        printf("\nEnter department number  (0 to exit): ");
        gets(temp);
        dept_num =