Release 8.0
A58234-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Call Interface Programmer's Guide, Volumes 1 & 2 Release 8.0 A58234-01 |
|
This chapter discusses the concepts and steps involved in processing SQL statements with the Oracle Call Interface.
The following topics are covered in this chapter:
Chapter 2 discussed the basic steps involved in any OCI application. This chapter presents a more detailed look at the specific tasks involved in processing SQL statements in an OCI program.
One of the most common tasks of an OCI program is to accept and process SQL statements. This section outlines the specific steps involved in processing SQL.
Once you have allocated the necessary handles and attached to a server, the basic steps in processing a SQL statement are the following, as illustrated in Figure 4-1:
Following these steps, the application can free allocated handles and then detach from the server, or it may process additional statements.
7.x Upgrade Note: OCI programs no longer require an explicit parse step. If a statement must be parsed, that step takes place on execute. This means that 8.0 applications must issue an execute command for both DML and DDL statements.
For each of the steps in the diagram, the corresponding OCI function calls are listed. In some cases multiple calls may be required.
Each step above is described in detail in the following sections.
Note: Some variation in the order of steps is possible. For example, it is possible to do the define step before the execute if the datatypes and lengths of returned values are known at compile time. Also, as indicated by the asterisks (*), some steps may not be required by your application.
Additional steps beyond those listed above may be required if your application needs to do the following:
These topics are described in Chapter 7.
SQL and PL/SQL statements need to be prepared for execution by using the statement prepare call and bind calls (if necessary). In this phase, the application specifies a SQL or PL/SQL statement and binds associated placeholders in the statement to data for execution. The client-side library allocates storage to maintain the statement prepared for execution.
An application requests a SQL or PL/SQL statement to be prepared for execution using the OCIStmtPrepare() call and passing it a previously allocated statement handle. This is a completely local call, requiring no round-trip to the server. No association is made at this point between the statement and a particular server.
Following the request call, an application can call OCIAttrGet() on the statement handle, passing OCI_ATTR_STMT_TYPE to the attrtype parameter, to determine what type of SQL statement was prepared. The possible attribute values, and corresponding statement types are listed in Table 4-1.
See Also: For more information on the specifics of using PL/SQL in an OCI application, see the section "Using PL/SQL in an OCI Program" on page 2-32.
The OCIStmtPrepare() call is described in more detail in Chapter 13, "OCI Relational Functions".
A prepared application request can be executed on multiple servers at run time by reassociating the statement handle with the respective service context handles for the servers. All information cached about the current service context and statement handle association is lost when a new association is made.
For example, consider an application such as a network manager, which manages multiple servers. In many cases, it is likely that the same SELECT statement will need to be executed against multiple servers to retrieve information for display. The OCI allows the server manager application to prepare a SELECT statement once and execute it against multiple servers. It must fetch all of the required rows from each server prior to reassociating the prepared statement with the next server.
Note: If a prepared statement must be reexecuted frequently on the same server, it is efficient to prepare a new statement for another service context.
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.
Note: Placeholders cannot be used to represent other Oracle objects such as tables. For example, the following is not a valid use of the :emp placeholder:
INSERT INTO :emp VALUES (12345, 'OERTEL', 'WRITER', 50000, 30)
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.
For detailed information about implementing bind operations, please refer to Chapter 5, "Binding and Defining".
An OCI application executes prepared statements individually using OCIStmtExecute().
When an OCI application executes a query, it receives data from Oracle that matches the query specifications. Within the database, the data is stored in Oracle-defined formats. When the results are returned, an OCI application can request that data be converted to a particular host language format, and stored in a particular output variable or buffer.
For each item in the select-list of a query, the OCI application must define an output variable to receive the results of the query. The define step indicates the address of the buffer and the type of the data to be retrieved.
Note: If output variables are defined for a SELECT statement before a call to OCIStmtExecute(), the number of rows specified by the iters parameter are fetched directly into the defined output buffers and additional rows equivalent to the prefetch count are prefetched. If there are no additional rows, then the fetch is complete without calling OCIStmtFetch().
For non-queries, the iters parameter of the OCIStmtExecute() call controls how many times the statement is executed during array operations. For example, if an array of 10 items is bound to a placeholder for an INSERT statement, and iters is set to 10, all 10 items will be inserted in a single execute call.
See Also: See the section "Defining" on page 4-11 for more information about defining output variables.
The OCIStmtExecute() call provides the ability to ensure that multiple service contexts operate on the same consistent snapshot of the database's committed data. This is achieved by taking the contents of the snap_out parameter of one OCIStmtExecute() call and passing that value in the snap_in parameter of the next OCIStmtExecute() call.
Note: Uncommitted data in one service context is not visible to another context, even when using the same snapshot.
The datatype of both the snap_out and snap_in parameter is OCISnapshot, an OCI snapshot descriptor. This descriptor is allocated with the OCIDescAlloc() function.
See Also: For more information about descriptors, see the section "Descriptors and Locators" on page 2-12.
It is not necessary to specify a snapshot when calling OCIStmtExecute(). The following sample code shows a basic execution in which the snapshot parameters are passed as NULL.
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *)NULL, (OCISnapshot *) NULL, OCI_DEFAULT))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.
A user can specify one of three modes for the OCIStmtExecute() call:
If your OCI application is processing a query, you may need to obtain more information about the items in the select-list. This is particularly true for dynamic queries whose contents are not known until run time. In this case, the program may need to obtain information about the datatypes and column lengths of the select-list items. This information is necessary to define output variables that will receive query results.
For example, a user might enter a query such as
SELECT * FROM employees
where the program has no prior information about the columns in the employees table.
In release 8.0, there are two types of describes available: implicit and explicit. An implicit describe is one which does not require any special calls to retrieve describe information from the server (although special calls are necessary to access the information). An explicit describe is one which requires the application to call a particular function to bring the describe information from the server.
An application may describe a select-list (query) either implicitly or explicitly. Other schema elements must be described explicitly.
An implicit describe allows an application to obtain select-list information as an attribute of the statement handle after a statement has been executed without making a specific describe call. It is called "implicit", because no describe call is required. The describe information comes "free" with the execute.
Users may choose to describe a query explicitly prior to execution. To do this, specify OCI_DESCRIBE_ONLY as the mode of OCIStmtExecute(). Calling OCIStmtExecute() in this mode does not execute the statement, but it does return the select-list description. For performance reasons, however, it is recommended that applications take advantage of the implicit describe that comes "free" with a standard statement execution.
An explicit describe with the OCIDescribeAny() call obtains information about schema objects rather than select-lists.
In all cases, the specific information about columns and datatypes is retrieved by reading handle attributes.
See Also: For information about using OCIDescribeAny() to obtain meta-data pertaining to schema objects, refer to Chapter 6, "Describing Schema Metadata".
After a SQL statement is executed, information about the select-list is available as an attribute of the statement handle. No explicit describe call is needed.
To retrieve information about select-list items from the statement handle, the application must call OCIParamGet() once for each position in the select-list to allocate a parameter descriptor for that position. Select-list positions are 1-based, meaning that the first item in the select-list is considered to be position number 1.
To retrieve information about multiple select-list items, an application can call OCIParamGet() with the pos parameter set to 1 the first time, and then iterate the value of pos and repeat the OCIParamGet() call until OCI_NO_DATA is returned. An application could also specify any position n to get a column at random.
Once a parameter descriptor has been allocated for a position in the select-list, the application can retrieve specific information by calling OCIAttrGet() on the parameter descriptor. Information available from the parameter descriptor includes the datatype and maximum size of the parameter.
The following sample code shows a loop that retrieves the column names and data types corresponding to a query following query execution. The query was associated with the statement handle by a prior call to OCIStmtPrepare().
OCIParam *mypard; ub4 counter; ub2 dtype; text *col_name; ub4 col_name_len; sb4 parm_status; ... /* Request a parameter descriptor for position 1 in the select-list */ counter = 1; parm_status = OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp, &mypard, (ub4) counter); /* Loop only if a descriptor was successfully retrieved for current position, starting at 1 */ while (parm_status==OCI_SUCCESS) { /* Retrieve the data type attribute */ checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, (dvoid*) &dtype,(ub4 *) 0, (ub4) OCI_ATTR_DATA_TYPE, (OCIError *) errhp )); /* Retrieve the column name attribute */ checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, (dvoid**) &col_name,(ub4 *) &col_name_len, (ub4) OCI_ATTR_NAME, (OCIError *) errhp )); printf("column=%s datatype=%d\n\n", col_name, dtype); fflush(stdout); /* increment counter and get next descriptor, if there is one */ counter++; parm_status = OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp, &mypard, (ub4) counter); }Note: Error handling for the initial OCIParamGet() call is not included in this example. Ellipses (...) indicate portions of code that have been omitted for this example.
The checkerr() function is used for error handling. The complete listing can be found in the first sample application in Appendix D, "Code Examples".
The calls to OCIAttrGet() and OCIParamGet() are local calls that do not require a network round trip, because all of the select-list information is cached on the client side after the statement is executed.
See Also: See the descriptions of OCIParamGet() and OCIAttrGet() in Chapter 13, "OCI Relational Functions", for more information about these calls.
See the section "Parameter Attributes" on page 6-5 for a list of the specific attributes of the parameter descriptor which may be read by OCIAttrGet().
Users may choose to describe a query explicitly prior to execution. To do this, specify OCI_DESCRIBE_ONLY as the mode of OCIStmtExecute(). Calling OCIStmtExecute() in this mode does not execute the statement, but it does return the select-list description.
Note: To maximize performance, it is recommended that applications execute the statement in default mode and use the implicit describe which accompanies the execution.
The following short example demonstrates the use of this mechanism to perform an explicit describe of a select-list to return information about the columns in the select-list. This pseudo-code shows how to retrieve column information (for example, data type).
/* initialize svchp, stmhp, errhp, rowoff, iters, snap_in, snap_out */ /* set the execution mode to OCI_DESCRIBE_ONLY. Note that setting the mode to OCI_DEFAULT does an implicit describe of the statement in addition to executing the statement */ OCIParam *colhd; /* column handle */ checkerr(errhp, OCIStmtExecute(svchp, stmhp, errhp, iters, rowoff, snap_in, snap_out, OCI_DESCRIBE_ONLY); /* Get the number of columns in the query */ checkerr(errhp, OCIAttrGet(stmhp, OCI_HTYPE_STMT, &numcols, 0, OCI_ATTR_PARAM_COUNT, errh)); /* go through the column list and retrieve the data type of each column. We start from pos = 1 */ for (i = 1; i <= numcols; i++) { /* get parameter for column i */ checkerr(errhp, OCIParamGet(stmhp, OCI_HTYPE_STMT, errh, &colhd, i)); /* get data-type of column i */ checkerr(errhp, OCIAttrGet(colhd, OCI_DTYPE_PARAM, &type[i-1], 0, OCI_ATTR_DATA_TYPE, errh)); }
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 which 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".
If an OCI application has processed a query, it is typically necessary to fetch the results with OCIStmtFetch() after the statement has been executed.
Fetched data is retrieved into output variables that have been specified by define operations.
Note: If output variables are defined for a SELECT statement before a call to OCIStmtExecute(), the number of rows specified by the iters parameter is fetched directly into the defined output buffers.
See Also: These statements fetch data associated with the sample code in the section "Steps Used in Defining" on page 5-14. Refer to that example for more information.
For information about defining output variables, see the section "Defining" on page 5-13.
If LOB columns or attributes are part of a select-list, LOB locators are returned as results of the query. The actual LOB value is not returned by the fetch. The application can perform further operations on these locators.
See Also: See the section "LOB and FILE Operations" on page 7-24 for more information about working with LOB locators in the OCI.
In order to minimize server round trips and maximize the performance of applications, the OCI can prefetch result set rows when executing a query. The OCI programmer can customize this prefetching by setting the OCI_ATTR_PREFETCH_ROWS or OCI_ATTR_PREFETCH_MEMORY attribute of the statement handle using the OCIAttrSet() function.
OCI_ATTR_PREFETCH_ROWS sets the number of rows to be prefetched.
OCI_ATTR_PREFETCH_MEMORY sets the memory allocated for rows to be prefetched. The application then fetches as many rows as will fit into that much memory.
When both of these attributes are set, the OCI prefetches rows up to the OCI_ATTR_PREFETCH_ROWS limit unless the OCI_ATTR_PREFETCH_MEMORY limit is reached, in which case the OCI returns as many rows as will fit in a buffer of size OCI_ATTR_PREFETCH_MEMORY.
By default, prefetching is turned on, and the OCI fetches an extra row all the time. To turn prefetching off, set both the OCI_ATTR_PREFETCH_ROWS and OCI_ATTR_PREFETCH_MEMORY attributes to zero.
Note: Prefetching is not in effect if LONG columns are part of the query. Queries containing LOB columns can be prefetched, because the LOB locator, rather than the data, is returned by the query.
See Also: For more information about these handle attributes, see the section "Statement Handle Attributes" on page B-15.
