4 Datatypes and Host Variables

Internal Datatypes

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

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.

Brief descriptions of the Oracle datatypes follow.

BBBBBBBB.RRRR.FFFF 

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

Additional External Datatypes

This section describes additional external datatypes.

TIMESTAMP(fractional_seconds_precision) 

TIMESTAMP(fractional_seconds_precision) WITH TIME ZONE

TIMESTAMP(fractional_seconds_precision) WITH LOCAL TIME ZONE

INTERVAL YEAR(year_precision) TO MONTH

INTERVAL DAY (day_precision) TO SECOND(fractional_seconds_precision)

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-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.

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

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; 

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

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 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.

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

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.

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;

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.

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

/*
 *  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 = atoi(temp);
        if (dept_num <= 0)
            break;
 
        EXEC SQL EXECUTE
            begin
                emp_demo_pkg.open_cur(:emp_cursor, :dept_num);
            end;
        END-EXEC;
 
        printf("\nFor department %d--\n", dept_num);
        printf("ENAME           SAL     COMM\n");
        printf("-----           ---     ----\n");
 
/* Fetch each row in the EMP table into the data struct.
   Note the use of a parallel indicator struct. */
        for (;;)
        {
             EXEC SQL FETCH :emp_cursor 
                 INTO :emp_info INDICATOR :emp_info_ind;
 
             printf("%s ", emp_info.emp_name);
             printf("%8.2f ", emp_info.salary);
             if (emp_info_ind.commission_ind != 0)
                 printf("    NULL\n");
             else
                 printf("%8.2f\n", emp_info.commission);
        }
 
    }
 
/* Close the cursor. */
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL CLOSE :emp_cursor;

/* Disconnect from Oracle. */
    EXEC SQL ROLLBACK WORK RELEASE;
    exit(EXIT_SUCCESS);

}

SQLRowidGet()

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.

Host Structures and Arrays

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.

For a complete discussion of host arrays, see also Chapter 8, "Host Arrays".

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

PL/SQL Records

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

Nested Structures and Unions

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.

Host Indicator Structures

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.

Example Program: Cursor and a Host Structure

The demonstration program in this section shows a query that uses an explicit cursor, selecting data into a host structure. This program is available in the file sample2.pc in your demo directory.

/*
 *  sample2.pc
 *
 *  This program connects to ORACLE, declares and opens a cursor, 
 *  fetches the names, salaries, and commissions of all
 *  salespeople, displays the results, then closes the cursor. 
 */ 

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

#define UNAME_LEN      20 
#define PWD_LEN        40 
 
/*
 * Use the precompiler typedef'ing capability to create
 * null-terminated strings for the authentication host
 * variables. (This isn't really necessary--plain char *'s
 * does work as well. This is just for illustration.)
 */
typedef char asciiz[PWD_LEN]; 

EXEC SQL TYPE asciiz IS STRING(PWD_LEN) REFERENCE; 
asciiz     username; 
asciiz     password; 

struct emp_info 
{ 
    asciiz     emp_name; 
    float      salary; 
    float      commission; 
}; 


/* Declare function to handle unrecoverable errors. */ 
void sql_error(); 


main() 
{ 
    struct emp_info *emp_rec_ptr; 

/* Allocate memory for emp_info struct. */ 
    if ((emp_rec_ptr = 
        (struct emp_info *) malloc(sizeof(struct emp_info))) == 0)
    { 
        fprintf(stderr, "Memory allocation error.\n"); 
        exit(1); 
    } 
 
/* Connect to ORACLE. */ 
    strcpy(username, "SCOTT"); 
    strcpy(password, "TIGER"); 
 
    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");
 
    EXEC SQL CONNECT :username IDENTIFIED BY :password; 
    printf("\nConnected to ORACLE as user: %s\n", username); 
 
/* Declare the cursor. All static SQL explicit cursors
 * contain SELECT commands. 'salespeople' is a SQL identifier,
 * not a (C) host variable.
 */
    EXEC SQL DECLARE salespeople CURSOR FOR 
        SELECT ENAME, SAL, COMM 
            FROM EMP 
            WHERE JOB LIKE 'SALES%'; 
 
/* Open the cursor. */
    EXEC SQL OPEN salespeople; 
 
/* Get ready to print results. */
    printf("\n\nThe company's salespeople are--\n\n");
    printf("Salesperson   Salary   Commission\n"); 
    printf("-----------   ------   ----------\n"); 
 
/* Loop, fetching all salesperson's statistics.
 * Cause the program to break the loop when no more
 * data can be retrieved on the cursor.
 */
    EXEC SQL WHENEVER NOT FOUND DO break; 

    for (;;) 
    { 
        EXEC SQL FETCH salespeople INTO :emp_rec_ptr; 
        printf("%-11s%9.2f%13.2f\n", emp_rec_ptr->emp_name, 
                emp_rec_ptr->salary, emp_rec_ptr->commission); 
    } 
 
/* Close the cursor. */
    EXEC SQL CLOSE salespeople; 
 
    printf("\nArrivederci.\n\n");

    EXEC SQL COMMIT WORK RELEASE; 
    exit(0); 
} 



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

    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\n%s\n", msg);

/* Call sqlglm() to get the complete text of the
 * error message.
 */
    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);
} 

Pointer Variable Declaration

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

int   *int_ptr; 
char  *char_ptr;

Pointer Variable Referencing

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. For details, see also "Globalization Support".

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. */ 
... 

Structure Pointers

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.

CHARACTER SET [IS] NCHAR_CS

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;

Environment Variable NLS_NCHAR

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. See NLS_NCHAR in the Oracle Database Reference.

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.

CONVBUFSZ Clause in VAR

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 } ) ]

See Also: "VAR (Oracle Embedded SQL Directive)" for a complete discussion of all keywords, examples, and variables.

Character Strings in Embedded SQL

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.

Strings Restrictions

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++.

Indicator Variables

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