Release 8.0
A58234-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Call Interface Programmer's Guide, Volumes 1 & 2 Release 8.0 A58234-01 |
|
Chapter 2, "OCI Programming Basics", introduced the concepts of binding and defining in OCI applications. This chapter revisits the basic concepts, and provides more detailed information about the different types of binds and defines you may use in OCI applications. The chapter includes short code examples to demonstrate the use of these different binds and defines.
Additionally, this chapter discusses the use of arrays of structures, as well as other issues involved in binding, defining, and character conversions.
Note: For information about binding and defining new Oracle8 datatypes for object applications, refer to Chapter 10.
This chapter includes the following sections:
Most DML statements, and some queries (such as those with a WHERE clause), require a program to pass data to Oracle as part of a SQL or PL/SQL statement. Such data can be constant or literal data, known when your program is compiled. For example, the following SQL statement, which adds an employee to a database contains several literals, such as `BESTRY' and 2365:
INSERT INTO emp VALUES (2365, `BESTRY', `PROGRAMMER', 2000, 20)
Hard coding a statement like this into an application would severely limit its usefulness. You would need to change the statement and recompile the program each time you add a new employee to the database. To make the program more flexible, you can write the program so that a user can supply input data at run time.
When you prepare a SQL statement or PL/SQL block that contains input data to be supplied at run time, placeholders in the SQL statement or PL/SQL block mark where data must be supplied. For example, the following SQL statement contains five placeholders, indicated by the leading colons (e.g., :ename), that show where input data must be supplied by the program.
INSERT INTO emp VALUES (:empno, :ename, :job, :sal, :deptno)
You can use placeholders for input variables in any DELETE, INSERT, SELECT, or UPDATE statement, or PL/SQL block, in any position in the statement where you can use an expression or a literal value. In PL/SQL, placeholders can also be used for output variables.
For each placeholder in the SQL statement or PL/SQL block, you must call an OCI routine that binds the address of a variable in your program to the placeholder. When the statement executes, Oracle gets the data that your program placed in the input, or bind, variables and passes it to the server with the SQL statement. Data does not have to be in a bind variable when you perform the bind step. At the bind step, you are only specifying the address, datatype, and length of the variable.
Note: If program variables do not contain data at bind time, make sure they contain valid data when you execute the SQL statement or PL/SQL block using OCIStmtExecute().
For example, given the INSERT statement
INSERT INTO emp VALUES (:empno, :ename, :job, :sal, :deptno)
and the following variable declarations
text *ename, *job sword empno, sal, deptno
the bind step makes an association between the placeholder name and the address of the program variables. The bind also indicates the datatype and length of the program variables, as illustrated in Figure 5-1. The code that implements this example is found in the section "Steps Used in Binding" on page 5-6.
If you change only the value of a bind variable, it is not necessary to rebind in order to execute the statement again. The bind is a bind by reference, so as long as the address of the bind variable and bind handle remain valid, you can reexecute a statement that references the variable without rebinding.
Note: At the interface level, all bind variables are considered at least IN and thus must be properly initialized (to zero if they are pure OUT bind variables).
For release 8.0, new datatypes have been implemented for named data types, REFs and LOBs, and they may be bound as placeholders in a SQL statement.
Note: For opaque data types (e.g., descriptors and locators) whose sizes are not known to the user, the address of the descriptor or locator pointer must be passed. Set the size parameter set to the size of the appropriate data structure (e.g., sizeof(structure))
The SQL statement in the previous section is an example of a named bind. Each placeholder in the statement has a name associated with it (e.g., `ename' or `sal'). When this statement is prepared and the placeholders are associated with values in the application, the association is made by the name of the placeholder using the OCIBindByName() call with the name of the placeholder passed in the placeholder parameter.
A second type of bind is known as a positional bind. In a positional bind, the placeholders are referred to by their position in the statement rather than their names. For binding purposes, an association is made between an input value and the position of the placeholder, using the OCIBindByPos() call.
The example from the previous section could also be used for a positional bind:
INSERT INTO emp VALUES (:empno, :ename, :job, :sal, :deptno)
The five placeholders would then each be bound by calling OCIBindByPos() and passing the position number of the placeholder in the position parameter. For example, the :empno placeholder would be bound by calling OCIBindByPos() with a position of 1, :ename with a position of 2, and so on.
In the case of a duplicate bind, only a single bind call may be necessary. Consider the following SQL statement, which queries the database for those employees whose commission and salary are both greater than a given amount:
SELECT empno FROM emp WHERE sal > :some_value AND comm > :some_value
An OCI application could complete the binds for this statement with a single call to OCIBindByName() to bind the :some_value placeholder by name. In this case, the second placeholder inherits the bind information from the first placeholder.
You can pass data to Oracle in various ways. You can execute a SQL statement repeatedly using the OCIStmtExecute() routine and supply different input values on each iteration. Alternatively, you can use the Oracle array interface and input many values with a single statement and a single call to OCIStmtExecute(). In this case you bind an array to an input placeholder, and the entire array can be passed at the same time, under the control of the iters parameter.
The array interface significantly reduces round-trips to Oracle when you need to update or insert a large volume of data. This reduction can lead to considerable performance gains in a busy client/server environment. For example, consider an application that needs to insert 10 rows into the database. Calling OCIStmtExecute() ten times with single values results in ten network round-trips to insert all the data. The same result is possible with a single call to OCIStmtExecute() using an input array, which involves only one network round-trip.
Note: When using the OCI array interface to perform inserts, row triggers in the database are fired as each row of the insert gets inserted.
You process a PL/SQL block by placing the block in a string variable, binding any variables, and executing the statement containing the block, just as you would with a single SQL statement.
When you bind placeholders in a PL/SQL block to program variables, you must use OCIBindByName() or OCIBindByPos() to perform the basic bind binds. You can use OCIBindByName() or OCIBindByPos() to bind host variables that are either scalars or arrays.
The following short PL/SQL block contains two placeholders, which represent IN parameters to a procedure that updates an employee's salary, given the employee number and the new salary amount:
char plsql_statement[] = "BEGIN\ RAISE_SALARY(:emp_number, :new_sal);\ END;" ;
These placeholders can be bound to input variables in the same way as placeholders in a SQL statement.
When processing PL/SQL statements, output variables are also associated with program variables using bind calls.
For example, in a PL/SQL block such as
BEGIN SELECT ename,sal,comm INTO :emp_name, :salary, :commission FROM emp WHERE ename = :emp_number; END;
you would use OCIBindByName() to bind variables in place of the :emp_name, :salary, and :commission output placeholders, and in place of the input placeholder :emp_number.
7.x Upgrade Note: In the Oracle7 OCI, it was sufficient for applications to initialize only IN-bind buffers. In Oracle8, all buffers, even pure OUT buffers, must be initialized by setting the buffer length to zero in the bind call, or by setting the corresponding indicator to -1.
See Also: For more information about binding PL/SQL placeholders see "Additional Information for Named Data Type and REF Binds" on page 10-3.
Binding placeholders is done in one or more steps. For a simple scalar or array bind, it is only necessary to specify an association between the placeholder and the data. This is done by using OCI bind by name (OCIBindByName()) or OCI bind by position (OCIBindByPos()) call.
Note: See the section "Named Binds and Positional Binds" on page 5-4 for information about the difference between these types of binds.
Once the bind is complete, the OCI library knows where to find the input data (or where to put PL/SQL output data) when the SQL statement is executed. As mentioned in the section "Binding" on page 5-2, program input data does not need to be in the program variable when it is bound to the placeholder, but the data must be there when the statement is executed.
The following code example shows handle allocation and binding for each of five placeholders in a SQL statement.
Note: The checkerr() function evaluates the return code from an OCI application. The code for the function is listed in the section "Error Handling" on page 2-25.
... /* The SQL statement, associated with stmthp (the statement handle) by calling OCIStmtPrepare() */ text *insert = (text *) "INSERT INTO emp(empno, ename, job, sal, deptno)\ VALUES (:empno, :ename, :job, :sal, :deptno)"; ... /* Bind the placeholders in the SQL statement, one per bind handle. */ checkerr(errhp, OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":ENAME", strlen(":ENAME"), (ub1 *) ename, enamelen+1, STRING_TYPE, (dvoid *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) checkerr(errhp, OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":JOB", strlen(":JOB"), (ub1 *) job, joblen+1, STRING_TYPE, (dvoid *) &job_ind, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) checkerr(errhp, OCIBindByName(stmthp, &bnd3p, errhp, (text *) ":SAL", strlen(":SAL"), (ub1 *) &sal, (sword) sizeof(sal), INT_TYPE, (dvoid *) &sal_ind, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) checkerr(errhp, OCIBindByName(stmthp, &bnd4p, errhp, (text *) ":DEPTNO", strlen(":DEPTNO"), (ub1 *) &deptno,(sword) sizeof(deptno), INT_TYPE, (dvoid *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) checkerr(errhp, OCIBindByName(stmthp, &bnd5p, errhp, (text *) ":EMPNO", strlen(":EMPNO"), (ub1 *) &empno, (sword) sizeof(empno), INT_TYPE, (dvoid *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0,OCI_DEFAULT))
Perhaps the most common use for PL/SQL blocks in an OCI program is to call stored procedures or stored functions. For example, assume that there is a procedure called RAISE_SALARY stored in the database, and you want to call this procedure from an OCI program. You do this by embedding a call to that procedure in an anonymous PL/SQL block, then processing the PL/SQL block in the OCI program.
The following program fragment shows how to embed a stored procedure call in an OCI application. For the sake of brevity, only the relevant portions of the program are reproduced here.
The program passes an employee number and a salary increase as inputs to a stored procedure called raise_salary, which takes these parameters:
raise_salary (employee_num IN, sal_increase IN, new_salary OUT);
This procedure raises a given employee's salary by a given amount. The increased salary which results is returned in the stored procedure's OUT variable new_salary, and the program displays this value.
/* Define PL/SQL statement to be used in program. */ text *give_raise = (text *) "BEGIN\ RAISE_SALARY(:emp_number,:sal_increase, :new_salary);\ END;"; OCIBind *bnd1p = NULL; /* the first bind handle */ OCIBind *bnd2p = NULL; /* the second bind handle */ OCIBind *bnd3p = NULL; /* the third bind handle */ static void checkerr(); sb4 status; main() { sword empno, raise, new_sal; dvoid *tmp; OCISession *usrhp = (OCISession *)NULL; ... /* attach to database server, and perform necessary initializations and authorizations */ ... /* allocate a statement handle */ checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp, OCI_HTYPE_STMT, 100, (dvoid **) &tmp)); /* prepare the statement request, passing the PL/SQL text block as the statement to be prepared */ checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) give_raise, (ub4) strlen(give_raise), OCI_NTV_SYNTAX, OCI_DEFAULT)); /* bind each of the placeholders to a program variable */ checkerr( errhp, OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":emp_number", -1, (ub1 *) &empno, (sword) sizeof(empno), SQLT_INT, (dvoid *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)); checkerr( errhp, OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":sal_increase", -1, (ub1 *) &raise, (sword) sizeof(raise), SQLT_INT, (dvoid *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)); /* remember that PL/SQL OUT variable are bound, not defined */ checkerr( OCIBindByName(stmthp, &bnd3p, errhp, (text *) ":new_salary", -1, (ub1 *) &new_sal, (sword) sizeof(new_sal), SQLT_INT, (dvoid *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)); /* prompt the user for input values */ printf("Enter the employee number: "); scanf("%d", &empno); /* flush the input buffer */ myfflush(); printf("Enter employee's raise: "); scanf("%d", &raise); /* flush the input buffer */ myfflush(); /* execute PL/SQL block*/ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT)); /* display the new salary, following the raise */ printf("The new salary is %d\n", new_sal); }
The following is one possible sample output from this program. Before execution, the salary of employee 7954 is 2000.
Enter the employee number: 7954 Enter employee's raise: 1000
The new salary is 3000
The previous section and example demonstrated how to perform a simple scalar bind. In that case, only a single bind call is necessary. In some cases, additional bind calls are necessary to define specific attributes for specific bind datatypes or execution modes. These more sophisticated bind operations are discussed in the following section.
Oracle8 also provides predefined C datatypes that map ADT attributes. Information about binding these datatypes (e.g., OCIDate, OCINumber) can be found in Chapter 10.
The section "Binding" on page 4-5 discussed how a basic bind operation is performed to create an association between a placeholder in a SQL statement and a program variable using OCIBindByName() or OCIBindByPos().
This section covers more advanced bind operations, including multi-step binds, and binds of named data types and REFs.
In certain cases, additional bind calls are necessary to define specific attributes for certain bind data types or certain execution modes.
The following sections describe these special cases, and the information about binding is summarized in Table 5-1 on page 5-12.
Static array bind attributes are set using the OCI array of structures bind call OCIBindArrayOfStruct(). This call is made following a call to OCIBindByName() or OCIBindByPos().
Note: A static array bind does not refer to binding a column of type ARRAY of scalars or named data types, but a bind to a PL/SQL table or for multiple row operations in SQL (INSERTs/UPDATEs).
The OCIBindArrayOfStruct() call is also used to define the skip parameters needed if the application utilizes arrays of structures functionality.
See Also: For more information on using arrays of structures, see the section "Arrays of Structures" on page 5-17.
For information on binding named data types (objects), refer to"Named Data Type Binds" on page 10-2.
For information on binding REFs, refer to "Binding REFs" on page 10-3.
When working with LOBs, the LOB locators, rather than the actual LOB value, are bound. The LOB value is written or read by passing a LOB locator to the PL/SQL DBMS_LOB package or OCI LOB functions.
Either a single locator or an array of locators can be bound in a single bind call. In each case, the application must pass the address of a LOB locator and not the locator itself.
For example, if an application has prepared a SQL statement like
INSERT INTO some_table VALUES (:one_lob)
where :one_lob is a bind variable corresponding to a LOB column, and has made the following declaration:
OCILobLocator * one_lob;
then the following sequence of steps would be used to bind the placeholder, and execute the statement
/* initialize single locator */ one_lob = OCIDescriptorAlloc(...OCI_DTYPE_LOB...); ... /* pass the address of the locator */ OCIBindByName(...,(dvoid *) &one_lob,...); OCIStmtExecute(...,1,...) /* 1 is the iters parameter */Note: In these examples, most parameters are omitted for simplicity.
You could also do an array insert using the same SQL INSERT statement. In this case, the application would include the following code:
OCILobLocator * lob_array[10]; ... for (i=0; i<10, i++) lob_array[i] = OCIDescriptorAlloc(...OCI_DTYPE_LOB...); /* initialize array of locators */ ... OCIBindByName(...,(dvoid *) lob_array,...); OCIStmtExecute(...,10,...); /* 10 is the iters parameter */
Note that you must allocate descriptors with the OCIDescriptorAlloc() routine before they can be used. In the case of an array of locators, you must initialize each array element using OCIDescriptorAlloc(). Use OCI_DTYPE_LOB as the type parameter when allocating BLOBs, CLOBs, and NCLOBs. Use OCI_DTYPE_FILE when allocating BFILEs.
See Also: For more information about OCI LOB functions, refer to the section "LOB and FILE Operations" on page 7-24.
If the mode parameter in a call to OCIBindByName() or OCIBindByPos() is set to OCI_DATA_AT_EXEC, an additional call to OCIBindDynamic() is necessary if the application will use the callback method for providing data at runtime. The call to OCIBindDynamic() sets up the callback routines, if necessary, for indicating the data or piece that is being provided.
If the OCI_DATA_AT_EXEC mode is chosen, but the standard OCI piecewise polling method will be used instead of callbacks, the call to OCIBindDynamic() is not necessary.
When binding RETURN clause variables, an application must use OCI_DATA_AT_EXEC mode, and it must provide callbacks.
See Also: For more information about piecewise operations, please refer to the section "Run Time Data Allocation and Piecewise Operations" on page 7-16.
Ref Cursors are bound to a statement handle with a bind datatype of SQLT_RSET. See "PL/SQL REF CURSORs and Nested Tables" on page 5-27
The following table summarizes the bind calls necessary for different types of binds. For each type, the table lists the bind datatype (passed in the dty parameter of OCIBindByName() or OCIBindByPos()), and notes about the bind.
See Also: For more information about datatypes and datatype codes, see Chapter 3, "Datatypes".
Query statements return data from the database to your application. When processing a query, you must define an output variable or an array of output variables for each item in the select-list from which you want to retrieve data. The define step creates an association that determines where returned results are stored, and in what format.
For example, if your OCI statement processes the following statement:
SELECT name, ssn FROM employees
WHERE empno = :empnum
you would normally need to define two output variables, one to receive the value returned from the name column, and one to receive the value returned from the ssn column.
For information about implementing define operations, please refer to Chapter 5, "Binding and Defining".
Note: If you were only interested in retrieving values from the name column, you would not need to define an output variable for ssn.
If the SELECT statement being processed might return more than a single value for a query, the output variables you define may be arrays instead of scalar values.
Note: Depending on the application, the define step can take place before or after the execute. If the datatypes of select-list items are known when the application is coded, the define can take place before the statement is executed. If your application is processing dynamic SQL statements-statements entered by the user at run time- or statements that do not have a clearly defined select-list, such as
SELECT * FROM employees
the application must execute the statement and retrieve describe information before defining output variables. See the section "Describing Select-List Items" on page 4-8 for more information.
The OCI processes the define call locally, on the client side. In addition to indicating the location of buffers where results should be stored, the define step also determines what type of data conversions, if any, will take place when data is returned to the application.
The dty parameter of the OCIDefineByPos() call specifies the datatype of the output variable. The OCI is capable of a wide range of data conversions when data is fetched into the output variable. For example, internal data in Oracle DATE format can be automatically converted to a string datatype on output.
See Also: For more information about datatypes and conversions, refer to Chapter 3, "Datatypes".
Defining output variables is done in one or more steps. A basic define is accomplished with the OCI define by position call, OCIDefineByPos(). This step creates an association between a select-list item and an output variable. Additional define calls may be necessary for certain datatypes or fetch modes.
Once the define step is complete, the OCI library knows where to put retrieved data after fetching it from the database.
Note: You can make your define calls again to redefine the output variables without having to reprepare or reexecute the SQL statement.
The following example code shows a scalar output variable being defined following an execute and a describe.
/* The following statement was prepared, and associated with statement handle stmthp1. SELECT dname FROM dept WHERE deptno = :dept_input The input placeholder was bound earlier, and the data comes from the user input below */ printf("Enter employee dept: "); scanf("%d", &deptno); myfflush(); /* Execute the statement. If OCIStmtExecute() returns OCI_NO_DATA, meaning that no data matches the query, then the department number is invalid. */ if ((status = OCIStmtExecute(svchp, stmthp1, errhp, 1, 0, 0, 0, OCI_DEFAULT)) && (status != OCI_NO_DATA)) { checkerr(errhp, status); do_exit(EXIT_FAILURE); } if (status == OCI_NO_DATA) { printf("The dept you entered doesn't exist.\n"); return 0; } /* The next two statements describe the select-list item, dept, and return its length */ checkerr(errhp, OCIParamGet(stmthp1, errhp, &parmdp, (ub4) 1)); checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &deptlen, (ub4 *) 0, (ub4) OCI_ATTR_DATA_SIZE, (OCIError *) errhp )); /* Use the retrieved length of dept to allocate an output buffer, and then define the output variable. If the define call returns an error, exit the application */ dept = (text *) malloc((int) deptlen + 1); if (status = OCIDefineByPos(stmthp1, &defnp, errhp, 1, (ub1 *) dept, deptlen+1, SQLT_STRING, (dvoid *) 0, (ub2 *) 0, OCI_DEFAULT)) { checkerr(errhp, status); do_exit(EXIT_FAILURE); }
For an explanation of the describe step, see the section "Describing Select-List Items" on page 4-8.
In some cases the define step requires more than just a call to OCIDefineByPos(). There are additional calls that define the attributes of an array fetch (OCIDefineArrayOfStruct()) or a named data type fetch (OCIDefineObject()). For example, to fetch multiple rows with a column of named data types, all three calls must be invoked for the column; but to fetch multiple rows of scalar columns, OCIDefineArrayOfStruct() and OCIDefineByPos() are sufficient.
These more sophisticated define operations are covered in the section "Advanced Define Operations" on page 5-16.
Oracle8 also provides pre-defined C datatypes that map object type attributes. Information about defining these datatypes (e.g., OCIDate, OCINumber) can be found in Chapter 10.
The section "Defining" on page 4-11 discussed how a basic bind operation is performed to create an association between a SQL select-list item and an output buffer in an application.
This section covers more advanced defined operations, including multi-step defines, and defines of named data types and REFs.
In some cases the define step requires more than just a call to OCIDefineByPos(). There are additional calls that define the attributes of an array fetch (OCIDefineArrayOfStruct()) or a named data type fetch (OCIDefineObject()). For example, to fetch multiple rows with a column of named data types, all the three calls must be invoked for the column; but to fetch multiple rows of scalar columns only OCIDefineArrayOfStruct() and OCIDefineByPos() are sufficient.
The following sections discuss specific information pertaining to different types of defines.
For information on defining named data type (object) output variables, refer to "Defining Named Data Type Output Variables" on page 10-4.
For information on defining REF output variables, refer to "Defining REF Output Variables" on page 10-4.
For LOBs, the buffer pointer must be a locator of type OCILobLocator, allocated by the OCIDescriptorAlloc() call. LOB locators, and not LOB values, are always returned for a LOB column. LOB values can then be fetched using OCI LOB calls on the fetched locator.
You do not use the define calls to define output variables for select-list items in a SQL SELECT statement in a PL/SQL block. You must use OCI bind calls instead.
See Also: See the section "Additional Information for Named Data Type and REF Defines, and PL/SQL OUT Binds" on page 10-5 for more information about defining PL/SQL output variables.
When performing a piecewise fetch, an initial call to OCIDefineByPos() is required. An additional call to OCIDefineDynamic() is necessary if the application will use callbacks rather than the standard polling mechanism for fetching data.
See Also: See the section "Run Time Data Allocation and Piecewise Operations" on page 7-16 for more information.
When using arrays of structures, an initial call to OCIDefineByPos() is required. An additional call to OCIDefineArrayOfStruct() is necessary to set up additional parameters, including the skip parameter necessary for arrays of structures operations.
See Also: For more information, refer to the section "Arrays of Structures" on page 5-17.
The "arrays of structures" functionality of the Oracle8 OCI can simplify the processing of multi-row, multi-column operations. The OCI programmer can create a structure of related scalar data items and then fetch values from the database into an array of these structures or insert values into the database from an array of these structures.
For example, an application may need to fetch multiple rows of data from three columns named NAME, AGE, and SALARY. The OCI application could include the definition of a structure containing separate fields to hold the NAME, AGE and SALARY data from one row in the database table. The application would then fetch data into an array of these structures.
In order to perform a multi-row, multi-column operation using an array of structures, the developer associates each column involved in the operation with a field in a structure. This association, which is part of the OCIDefineArrayOfStruct() and OCIBindArrayOfStruct() calls, specifies where fetched data will be stored, or where inserted or updated data will be found.
Figure 5-2 is a graphical representation of this process. In the figure, an application fetches various fields from a database row into a single structure in an array of structures. Each column being fetched corresponds to one of the fields in the structure.
Fetching Data Into an Array of StructuresWhen you split column data across an array of structures, it is no longer contiguous. The single array of structures stores data as though it were composed of several interleaved arrays of scalars. Because of this fact, developers must specify a "skip parameter" for each field they are binding or defining. This skip parameter specifies the number of bytes that need to be skipped in the array of structures before the same field is encountered again. In general this will be equivalent to the byte size of one structure.
The figure below demonstrates how a skip parameter is determined. In this case the skip parameter is the sum of the sizes of the fields field1, field2 and field3, which is 8 bytes. This equals the size of one structure.
On some systems it may be necessary to set the skip parameter to be sizeof(one array element) rather than sizeof(struct). This is because some compilers may insert padding into a structure. For example, consider an array of C structures consisting of two fields, a ub4 and a ub1.
struct demo { ub4 field1; ub1 field2; }; struct demo demo_array[MAXSIZE];
Some compilers insert three bytes of padding after the ub1 so that the ub4 which begins the next structure in the array is properly aligned. In this case, the following statement may return an incorrect value:
skip_parameter = sizeof(struct demo);
On some systems this will produce a proper skip parameter of eight. On other systems, skip_parameter will be set to five bytes by this statement. In this case, use the following statement to get the correct value for the skip parameter:
skip_parameter = sizeof(demo_array[0]);
The ability to work with arrays of structures is an extension of the functionality for binding and defining arrays of program variables. Programmers can also work with standard arrays (as opposed to arrays of structures). When specifying a standard array operation, the related skip will be equal to the size of the datatype of the array under consideration. For example, for an array declared as
text emp_names[4][20]
the skip parameter for the bind or define operation will be 20. Each data element in the array is then recognized as a separate unit, rather than being part of a structure.
Two OCI calls must be used when performing operations involving arrays of structures: OCIBindArrayOfStruct() (for binding fields in arrays of structures for input variables) and OCIDefineArrayOfStruct() (for defining arrays of structures for output variables).
Note: When binding or defining for arrays of structures, multiple calls are required. A call to OCIBindByName() or OCIBindByPos() must proceed a call to OCIBindArrayOfStruct(), and a call to OCIDefineByPos() must proceed a call to OCIDefineArrayOfStruct().
See Also: See the descriptions of OCIBindArrayOfStruct() and OCIDefineArrayOfStruct() in Chapter 13 for syntax and parameter descriptions.
The implementation of arrays of structures also supports the use of indicator variables and return codes. OCI application developers can declare parallel arrays of column-level indicator variables and return codes, corresponding to the arrays of information being fetched, inserted, or updated. These arrays can have their own skip parameters, which are specified during a call to OCIBindArrayOfStruct() or OCIDefineArrayOfStruct().
You can set up arrays of structures of program values and indicator variables in many ways. For example, consider an application that fetches data from three database columns into an array of structures containing three fields. You can set up a corresponding array of indicator variable structures of three fields, each of which is a column-level indicator variable for one of the columns being fetched from the database.
Note: A one-to-one relationship between the fields in an indicator struct and the number of select-list items is not necessary.
See Also: See "Indicator Variables" on page 2-29 for more information about indicator variables.
The OCI supports the use of the RETURNING clause with SQL INSERT, UPDATE, and DELETE statements. This section outlines the rules an OCI application must follow to correctly implement DML statements with the RETURNING clause.
Note: For more information about the use of the RETURNING clause with INSERT, UPDATE, or DELETE statements, please refer to the descriptions of those commands in the Oracle8 SQL Reference.
For an complete code example, refer to "Example 3, DML with RETURNING Clause" on page D-25.
Using the RETURNING clause with a DML statement allows you to essentially combine two SQL statements into one, possibly saving you a server round-trip. This is accomplished by adding an extra clause to the traditional UPDATE, INSERT, and DELETE statements. The extra clause effectively adds a query to the DML statement.
In the OCI, the values are returned to the application through the use of OUT bind variables. The rules for binding these variables are described in the next section. In the following examples, the bind variables are indicated by the preceding colon (e.g., :out1). These examples assume the existence of a table called table1, which contains three columns: col1, col2, and col3.
For example, the following statement inserts new values into the database and then retrieves the column values of the affected row from the database, allowing your application to work with inserted rows.
INSERT INTO table1 VALUES (:1, :2, :3,) RETURNING col1, col2, col3 INTO :out1, :out2, :out3
The next example uses the UPDATE statement. This statement updates the values of all columns whose col1 value falls within a certain range, and then returns the affected rows to the application, allowing the application to see which rows were modified.
UPDATE table1 SET col1 = col1 + :1, col2 = :2, col3 = :3 WHERE col1 >= :low AND col1 <= :high RETURNING col1, col2, col3 INTO :out1, :out2, :out3
The following DELETE statement deletes the rows whose col1 value falls within a certain range, and then returns the data from those rows so that the application can check them.
DELETE FROM table1 WHERE col1 >= :low AND col2 <= :high RETURNING col1, col2, col3 INTO :out1, :out2, :out3
Note that in both the UPDATE and DELETE examples there is the possibility that the statement will affect multiple rows in the table. Additionally, a DML statement could be executed multiple times in a single OCIExecute() statement. Because of this possibility for multiple returning values, an OCI application may not know how much data will be returned at runtime. As a result, the variables corresponding to the RETURNING...INTO placeholders must be bound in OCI_DATA_AT_EXEC mode. It is an additional requirement that the application must define its own dynamic data handling callbacks (rather than using the OCI_DATA_AT_EXEC polling mechanism).
Note: Even if the application can be sure that it will only get a single value back in the RETURNING clause, it must still bind in OCI_DATA_AT_EXEC mode and use callbacks.
The returning clause can be particularly useful when working with LOBs. Normally, an application must insert an empty LOB locator into the database, and then SELECT it back out again to operate on it. Using the RETURNING clause, the application can combine these two steps into a single statement:
INSERT INTO some_table VALUES (:in_locator) RETURNING lob_column INTO :out_locator
As mentioned in the previous section, an OCI application implements the placeholders in the RETURNING clause as pure OUT bind variables. An application must adhere to the following rules when working with these bind variables:
Note: The OCI only supports the callback mechanism for RETURNING clause binds. The polling mechanism is not supported.
The out bind function provided to OCIBindDynamic() must be prepared to receive partial results of a statement in the event of an error. For example, if the application has issued a DML statement which should be executed 10 times, and an error occurs during the fifth iteration, the server will still return the data from iterations 1 through 4. The callback function would still be called to receive data for the first four iterations.
The RETURNING clause can also be used to return a REF to an object which is being inserted into or updated in the database. The following SQL statement shows how this could be used.
UPDATE EXTADDR E SET E.ZIP = '12345', E.STATE='AZ' WHERE E.STATE = 'CA' AND E.ZIP='95117' RETURNING REF(E), ZIP INTO :addref, :zip
This statement updates several attributes of an object in an object table and then returns a REF to the object (along with the scalar ZIP code) in the RETURNING clause.
Binding the REF output variable in an OCI application requires three steps:
The following pseudocode shows a function which performs the binds necessary for the above example.
sword bind_output(stmthp, bndhp, errhp) OCIStmt *stmthp; OCIBind *bndhp[]; OCIError *errhp; { ub4 i; /* get TDO for BindObject call */ if (OCITypeByName(envhp, errhp, svchp, (CONST text *) 0, (ub4) 0, (CONST text *) "ADDRESS_OBJECT", (ub4) strlen((CONST char *) "ADDRESS_OBJECT"), (CONST text *) 0, (ub4) 0, OCI_DURATION_SESSION, OCI_TYPEGET_HEADER, &addrtdo)) { return OCI_ERROR; } /* initial bind call for both variables */ if (OCIBindByName(stmthp, &bndhp[2], errhp, (text *) ":addref", (sb4) strlen((char *) ":addref"), (dvoid *) 0, (sb4) sizeof(OCIRef *), SQLT_REF, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC) || OCIBindByName(stmthp, &bndhp[3], errhp, (text *) ":zip", (sb4) strlen((char *) ":zip"), (dvoid *) 0, (sb4) MAXZIPLEN, SQLT_CHR, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)) { return OCI_ERROR; } /* object bind for REF variable */ if (OCIBindObject(bndhp[2], errhp, (OCIType *) addrtdo, (dvoid **) &addrref[0], (ub4 *) 0, (dvoid **) 0, (ub4 *) 0)) { return OCI_ERROR; } for (i = 0; i < MAXCOLS; i++) pos[i] = i; /* dynamic binds for both RETURNING variables */ if (OCIBindDynamic(bndhp[2], errhp, (dvoid *) &pos[0], cbf_no_data, (dvoid *) &pos[0], cbf_get_data) || OCIBindDynamic(bndhp[3], errhp, (dvoid *) &pos[1], cbf_no_data, (dvoid *) &pos[1], cbf_get_data)) { return OCI_ERROR; } return OCI_SUCCESS; }
When a callback function is called, the OCI_ATTR_ROWS_RETURNED attribute of the bind handle tells the application the number of rows being returned in that particular iteration. Thus, when the callback is called the first time in a particular iteration (i.e., index=0), the user can allocate space for all the rows which will be returned for that bind variable. When the callback is called subsequently (with index>0) within the same iteration, the user can merely increment the buffer pointer to the correct memory within the allocated space to retrieve the data.
This section discusses issues involving NCHAR data and character conversions between the client and the server.
Oracle8 provides support for NCHAR data in the database, and the Oracle8 OCI provides support for binding and defining NCHAR data. If a database column containing character data is defined to be an NCHAR column, then a bind or define involving that column must take into account special considerations for dealing with character set specifications.
These considerations are necessary in case the width of the client character set is different from that on the server, and also for proper character conversion between the client and server. During conversion of data between different character sets, the size of the data may grow or shrink as much as fourfold. Care must be taken to insure that buffers provided to hold the data are of sufficient size.
In some cases, it may also be easier for an application to deal with NCHAR data in terms of numbers of characters, rather than numbers of bytes (which is the usual case).
Each OCI bind and define handle has "form" (OCI_ATTR_CHRSETFORM) and "character set ID" (OCI_ATTR_CHRSETID) attributes associated with it. An application can set these attributes with the OCIAttrSet() call in order to specify the character set ID and form of the bind/define buffer.
The form attribute has two possible values:
The default value is SQLCS_IMPLICIT.
If the character set ID is not specified, then the default value of the database or NCHAR character set ID of the client is used, depending on the value of form. That is the value specified in the NLS_LANG and NLS_NCHAR environment variables.
If nothing is specified, then the default database character set ID of the client is assumed.
Note: No matter what values are assigned to the character set ID and form of the client-side bind buffer, the data is converted and inserted into the database according to the server's database/NCHAR character set ID and form.
See Also: For more information about NCHAR data, refer to the Oracle8 Reference.
Every bind handle has a OCI_ATTR_MAXDATA_SIZE attribute. This attribute specifies the number of bytes to be allocated on the server to accommodate the client-side bind data after any necessary character set conversions.
Note: Character set conversions performed when data is sent to the server may result in the data expanding or contracting, so its size on the client may not be the same as its size on the server.
An application will typically set OCI_ATTR_MAXDATA_SIZE to the maximum size of the column or the size of the PL/SQL variable, depending on how it is used. Oracle issues an error if OCI_ATTR_MAXDATA_SIZE is not a large enough value to accommodate the data after conversion, and the operation will fail.
Bind and define handles have a character count attribute associate with them. An application can use this attribute to work with data in terms of numbers of characters, rather than numbers of bytes. If this attribute is set to a non-zero value, it indicates that all calculations should be done in terms of characters instead of bytes, and any constraint sizes should be thought of in terms of characters rather than bytes.
This attribute can be set in addition to the OCI_ATTR_MAXDATA_SIZE attribute for bind handles. For example, if OCI_ATTR_MAXDATA_SIZE is set to 100, and OCI_ATTR_CHAR_COUNT is set to 0, this means that the maximum possible size of the data on the server after conversion is 100 bytes. However, if OCI_ATTR_MAXDATA_SIZE is set to 100, and OCI_ATTR_CHAR_COUNT is set to a non-zero value, then if the character set has 2 bytes/character, the maximum possible allocated size is 200 bytes (2 bytes/char * 100 chars).
Note: This attribute is valid only for fixed-width character set IDs. For variable-width character set IDs, these values are always treated as numbers of bytes, rather than numbers of characters.
For binds, the OCI_ATTR_CHAR_COUNT attribute sets the number of characters that an application wants to reserve on the server to store the data being bound. This overrides the OCI_ATTR_MAXDATA_SIZE attribute. For all datatypes that have a length prefix as part of their value (e.g., VARCHAR2), the length prefix is then considered to be the number of characters, rather than the number of bytes. In this case, indicator lengths and return codes are also in characters.
Note: Regardless of the value of the OCI_ATTR_CHAR_COUNT attribute, the buffer lengths specified in a bind or define call are always considered to be in terms of number of bytes. The actual length values sent and received by the user are also in characters in this case.
For defines, the OCI_ATTR_CHAR_COUNT attribute specifies the maximum number of characters of data the client application wants to receive. This constraint overrides the maxlength parameter specified in the OCIDefineByPos() call.
The OCI provides the ability to bind and define PL/SQL REF CURSORs and nested tables. An application can use a statement handle to bind and define these types of variables. As an example, consider this PL/SQL block:
static const text *plsql_block = (text *) "begin \ OPEN :cursor1 FOR SELECT empno, ename, job, mgr, sal, deptno \ FROM emp_rc WHERE job=:job ORDER BY empno; \ OPEN :cursor2 FOR SELECT * FROM dept_rc ORDER BY deptno; \ end;";
An application would allocate a statement handle for binding, by calling OCIHandleAlloc(), and then bind the :cursor1 placeholder to the statement handle, as in the following code, where :cursor1 is bound to stm2p. Note that the handle allocation code is not included here.
err = OCIStmtPrepare (stm1p, errhp, (text *) nst_tab, strlen(nst_tab), OCI_NTV_SYNTAX, OCI_DEFAULT); ... err = OCIBindByName (stm1p, (OCIBind **) bndp, errhp, (text *)":cursor1", (sb4)strlen((char *)":cursor1"), (dvoid *)&stm2p, (sb4) 0, SQLT_RSET, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, (ub4)OCI_DEFAULT);
In this code, stm1p is the statement handle for the PL/SQL block, while stm2p is the statement handle which is bound as a REF CURSOR for later data retrieval. A value of SQLT_RSET is passed for the dty parameter.
As another example, consider the following:
static const text *nst_tab = (text *) "SELECT ename, CURSOR(SELECT dname, loc FROM dept_rc) \ FROM emp_rc WHERE ename = 'LOCKE'";
In this case the second position is a nested table, which an OCI application can define as a statement handle as follows. Note that the handle allocation code is not included here.
err = OCIStmtPrepare (stm1p, errhp, (text *) nst_tab, strlen(nst_tab), OCI_NTV_SYNTAX, OCI_DEFAULT); ... err = OCIDefineByPos (stm1p, (OCIDefine **) dfn2p, errhp, (ub4)2, (dvoid *)&stm2p, (sb4)0, SQLT_RSET, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT);
After execution, when you fetch a row into stm2p it then becomes a valid statement handle.
Note: If you have retrieved multiple ref cursors, you must take care when fetching them into stm2p. If you fetch the first one, you can then perform fetches on it to retrieve its data. However, once you fetch the second ref cursor into stm2p, you no longer have access to the data from the first ref cursor.
