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.

Brief descriptions of the Oracle datatypes follow.

This section describes additional external datatypes.

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-5 shows the compatible Oracle internal datatypes.

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.

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; 

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:

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; 

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.

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.

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.

typedef VARCHAR buf[64]; 

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); 

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.

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.

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); 
    ... 
}

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.

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.

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;

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.

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.

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.

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.

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.

A SQLLIB function, SQLRowidGet(), provides the ability to retrieve a pointer to the universal ROWID of the last row inserted, updated, or selected. The function prototype and its arguments are:

void SQLRowidGet (dvoid *rctx, OCIRowid **urid) ;

rctx (IN)

is a pointer to a runtime context. For the default context or a non-threaded case, pass SQL_SINGLE_RCTX.

urid (OUT)

is a pointer to a universal ROWID pointer. When a normal execution finishes, this will point to a valid ROWID. In case of an error, NULL is returned.

An array is a collection of related data items, called elements, associated with a single variable name. When declared as a host variable, the array is called a host array. Likewise, an indicator variable declared as an array is called an indicator array. An indicator array can be associated with any host array.

Host arrays can increase performance by letting you manipulate an entire collection of data items with a single SQL statement. With few exceptions, you can use host arrays wherever scalar host variables are allowed. Also, you can associate an indicator array with any host array.

You can use host arrays as components of host structures. In the following example, a structure containing arrays is used to INSERT three new entries into the EMP table:

struct 
{ 
    char emp_name[3][10]; 
    int emp_number[3]; 
    int dept_number[3]; 
} emp_rec; 
... 
strcpy(emp_rec.emp_name[0], "ANQUETIL"); 
strcpy(emp_rec.emp_name[1], "MERCKX"); 
strcpy(emp_rec.emp_name[2], "HINAULT"); 
emp_rec.emp_number[0] = 1964; emp_rec.dept_number[0] = 5; 
emp_rec.emp_number[1] = 1974; emp_rec.dept_number[1] = 5; 
emp_rec.emp_number[2] = 1985; emp_rec.dept_number[2] = 5; 
 
EXEC SQL INSERT INTO emp (ename, empno, deptno) 
    VALUES (:emp_rec); 
...

You cannot bind a C struct to a PL/SQL record.

You cannot nest host structures. The following example is invalid:

struct 
{ 
    int emp_number; 
    struct 
    { 
        float salary; 
        float commission; 
    } sal_info;            /* INVALID */ 
    int dept_number; 
} emp_record; 
... 
EXEC SQL SELECT empno, sal, comm, deptno 
    INTO :emp_record 
    FROM emp; 

Also, you cannot use a C union as a host structure, nor can you nest a union in a structure that is to be used as a host structure.

When you need to use indicator variables, but your host variables are contained in a host structure, you set up a second structure that contains an indicator variable for each host variable in the host structure.

For example, suppose you declare a host structure student_record as follows:

struct 
{ 
    char s_name[32]; 
    int s_id; 
    char grad_date[9]; 
} student_record; 

If you want to use the host structure in a query such as

EXEC SQL SELECT student_name, student_idno, graduation_date 
    INTO :student_record 
    FROM college_enrollment 
    WHERE student_idno = 7200; 

and you need to know if the graduation date can be NULL, then you must declare a separate host indicator structure. You declare this as

struct 
{ 
    short s_name_ind;  /* indicator variables must be shorts */ 
    short s_id_ind; 
    short grad_date_ind; 
} student_record_ind; 

Reference the indicator structure in the SQL statement in the same way that you reference a host indicator variable:

EXEC SQL SELECT student_name, student_idno, graduation_date 
    INTO :student_record INDICATOR :student_record_ind 
    FROM college_enrollment 
    WHERE student_idno = 7200;
 

When the query completes, the NULL/NOT NULL status of each selected component is available in the host indicator structure.

You define pointers as host variables following the normal C practice, as the next example shows:

int   *int_ptr; 
char  *char_ptr;

In SQL statements, prefix pointers with a colon, as shown in the following example:

EXEC SQL SELECT intcol INTO :int_ptr FROM ... 

Except for pointers to character strings, the size of the referenced value is given by the size of the base type specified in the declaration. For pointers to character strings, the referenced value is assumed to be a NULL-terminated string. Its size is determined at run time by calling the strlen() function.

You can use pointers to reference the members of a struct. First, declare a pointer host variable, then set the pointer to the address of the desired member, as shown in the example later. The datatypes of the struct member and the pointer variable must be the same. Most compilers will warn you of a mismatch.

struct 
{ 
    int  i; 
    char c; 
} structvar; 
int   *i_ptr; 
char  *c_ptr; 
... 
main() 
{ 
    i_ptr = &structvar.i; 
    c_ptr = &structvar.c; 
/* Use i_ptr and c_ptr in SQL statements. */ 
... 

You can use a pointer to a structure as a host variable. The following example

• Declares a structure

• Declares a pointer to the structure

• Allocates memory for the structure

• Uses the struct pointer as a host variable in a query

• Dereferences the struct components to print the results

struct EMP_REC 
{ 
    int emp_number; 
    float salary; 
}; 
char *name = "HINAULT"; 
... 
struct EMP_REC *sal_rec; 
sal_rec = (struct EMP_REC *) malloc(sizeof (struct EMP_REC)); 
... 
EXEC SQL SELECT empno, sal INTO :sal_rec 
    FROM emp 
    WHERE ename = :name; 

printf("Employee number and salary for %s: ", name); 
printf("%d, %g\n", sal_rec->emp_number, sal_rec->salary);

In the SQL statement, pointers to host structures are referred to in exactly the same way as a host structure. The "address of" notation (&) is not required; in fact, it is an error to use it.

To specify which host variables hold National Character Set data, insert the clause "CHARACTER SET [IS] NCHAR_CS" in character variable declarations. Then you are able to store National Character Set data in those variables. You can omit the token IS. NCHAR_CS is the name of the National Character Set.

For example:

char character set is nchar_cs *str = "<Japanese_string>";

In this example, <Japanese_string> consists of Unicode characters that are in the National Character Set AL16UTF16, as defined by the variable NLS_NCHAR.

You can accomplish the same thing by entering NLS_CHAR=str on the command line, and coding in your application:

char *str = "<Japanese_string>"

Pro*C/C++ treats variables declared this way as of the character set specified by the environment variable NLS_NCHAR. The variable size of an NCHAR variable is specified as a byte count, the same way that ordinary C variables are.

To select data into str, use the following simple query:

EXEC SQL
   SELECT ENAME INTO :str FROM EMP WHERE DEPT = n'<Japanese_string1>';

Or, you can use str in the following SELECT:

EXEC SQL 
   SELECT DEPT INTO :dept FROM DEPT_TAB WHERE ENAME = :str;

Pro*C/C++ supports National Character Sets with database support when NLS_LOCAL=NO. When NLS_LOCAL=NO, and the new environmental variable NLS_NCHAR is set to a valid National Character Set, the database server supports NCHAR.

NLS_NCHAR specifies the character set used for National Character Set data (NCHAR, NVARCHAR2, NCLOB). If it is not specified, the character set defined or indirectly defined by NLS_LANG will be used.

NLS_NCHAR must have a valid National Character Set specification (not a language name, that is set by NLS_LANG) at both precompile-time and runtime. SQLLIB performs a runtime check when the first SQL statement is executed. If the precompile-time and runtime character sets are different, SQLLIB will return an error code.

You can override the default assignments by equivalencing host variables to Oracle external datatypes, using the EXEC SQL VAR statement, This is called host variable equivalencing.

The EXEC SQL VAR statement can have an optional clause: CONVBUFSZ (<size>). You specify the size, <size>, in bytes, of the buffer in the Oracle runtime library used to perform conversion of the specified host variable between character sets.

The new syntax is:

EXEC SQL VAR host_variable IS datatype [CONVBUFSZ [IS] (size)] ;

or

EXEC SQL VAR host_variable [CONVBUFSZ [IS] (size)];

where datatype is:

type_name [ ( { length | precision, scale } ) ]

A multibyte character string in an embedded SQL statement consists of a character literal that identifies the string as multibyte, immediately followed by the string. The string is enclosed in the usual single quotes.

For example, an embedded SQL statement such as

EXEC SQL SELECT empno INTO :emp_num FROM emp
    WHERE ename = N'<Japanese_string>';

contains a multibyte character string (<Japanese_string> could actually be Kanji), since the N character literal preceding the string identifies it as a multibyte string. Since Oracle is case-insensitive, you can use "n" or "N" in the example.

You cannot use datatype equivalencing (the TYPE or VAR commands) with multibyte character strings.

Dynamic SQL method 4 is not available for multibyte character string host variables in Pro*C/C++.

You can use indicator variables with host character variables that are multibyte characters (as specified using the NLS_CHAR option).