| Programmer's Guide to the Oracle7 Server Call Interface |  Library |  Product |  Contents |  Index |
The OCI Functions for C
This chapter describes each function in the OCI library for the OCI C programmer. The description of many of the functions includes an example that shows how an OCI program uses the function. Examples are not provided for the simpler functions. The description of each function has the following parts:
Purpose
What the function does.
Syntax
The function call with its parameter list.
Comments
A detailed description of the function, including examples.
Parameters
A detailed description of each parameter.
See Also
A list of other functions that affect or are used with this function. Not included if not applicable.
Be sure to read "Calling OCI Routines" ![[*]](http://dbserv.npi.msu.su/DOC/api/doc/OCI73/jump.gif)
. It contains important information about data structures, datatypes, parameter passing conventions, and other important information about the OCI functions.
Calling OCI Routines
This section describes data structures and coding rules that are specific to applications written in the C language. Refer to this section for information about data structures, datatypes, and parameter passing conventions in OCI C programs.
Datatypes
The datatypes used in the C examples in this guide are defined in the file oratypes.h. This file is port specific. The types defined, such as sb2 and ub4, can take different C types on different systems. An example oratypes.h file for UNIX C compilers is listed in Appendix A.Different OCI platforms may have different datatype definitions. The online location of the oratypes.h file can also be system specific. On Unix systems, it can be found at $ORACLE_HOME/rdbms/demo/ oratypes.h. See your Oracle system-specific documentation for the location of oratypes.h on your system.
Data Structures
To use the OCI functions, you must define data structures for one or more LDAs and CDAs. The internal structure of these data areas is discussed in the section "OCI Data Structures". The LDA structure is the same size as the CDA structure, and the same structure declaration can be used for both structures.
The only field an OCI application normally accesses in the LDA is the return code field. In the example code in this section, the datatypes Lda_Def and Cda_Def, as defined in the header file ocidfn.h, are used to define the LDAs and CDAs. This file is listed in Appendix A and is also available online; see the Oracle system-specific documentation for the online location of this file.
Parameter Names
The prototype parameter names in the function descriptions are six or less characters in length and do not contain non-alphanumeric characters. This maintains common names across all languages supported by the OCI. In your OCI C program, you can of course use longer, more descriptive names for the parameters.
Parameter Types
The OCI functions take three types of parameters:
- integers (sword, eword, sb4, ub4)
- short integers (sb2, ub2)
- character variables (ub1, sb1)
- addresses of program variables (pointers)
- memory pointers (dvoid)
Integers
When passing integer literals to an OCI function, you should cast the literal to the type of the parameter. For example, the oparse() function has the following prototype, using ANSI C notation:
oparse(Cda_Def *cursor, text *sqlstm, sb4 sqllen,
sword defflg, ub4 lngflg);
If you call oparse() as
oparse(&cda, (text *) "select sysdate from dual", -1, 1, 2);
it will usually work on most 32-bit systems, although the C compiler might issue warning messages to the effect that the type conversions are non-portable. So, you should call oparse() as
oparse(&cda, (text *) "select sysdate from dual", (sb4) -1,
(sword) 0, (ub4) 2);
Always be careful to distinguish signed and unsigned short integers (sb2 and ub2) from integers (sword), and signed and unsigned long integers (sb4 and ub4).
Addresses
Be careful to pass all pointer parameters as valid addresses. When passing the null pointer (0), Oracle recommends that you cast it to the appropriate type. When you pass a pointer as a parameter, your OCI program must allocate the storage for the object to which it points. OCI routines never allocate storage for program objects. String literals can be passed to an OCI function where the parameter type is text * as the oparse() example in the previous section demonstrates.
Parameter Classification
There are three kinds of parameters:
Required Parameters
Required parameters are used by Oracle, and the OCI program must supply valid values for them.
Optional Parameters
The use of optional parameters depends on the requirements of your program. The Syntax section for each routine in this chapter indicates optional parameters using square brackets ([ ]).In most cases, an unused optional parameter is passed as -1 if it is an integer. It is passed as the null pointer (0) if it is an address parameter. For example, your program might not need to supply an indicator variable on a bind call, in which case all input values in the program could be non-null. The indp parameter in the bind functions obindps(), obndra(), obndrv(), and obndrn() is optional. This parameter is a pointer, so it is passed as a null pointer ((sb2 *) 0) when it is not used.
Note: A value of -1 should not be passed for unused optional parameters in the new obindps() and odefinps() calls. Unused parameters in these calls must be passed a zero or NULL. See the descriptions of individual calls for more details about specific parameters.
Unused Parameters
Unused parameters are not used by Oracle, at least for the language being described. For example, for cross-language compatibility, some OCI functions have the parameters fmt, fmtl, and fmtt. These are the format string specifier for the packed decimal external datatype, and the string length and type parameters when this type is being bound. COBOL uses the packed decimal type, so these parameters are unused in C.However, you always pass unused parameters. In C, pass these in the same way as omitted optional parameters. In most cases, this means passing -1 if it is an integer parameter, or 0 if it is a pointer. See the syntax and examples for the odefin() function() for examples of how to pass omitted optional and unused parameters.
Note: As with optional parameters, a value of -1 should not be passed for unused parameters in the new obindps() and odefinps() calls. Unused parameters in these calls must be passed a zero or NULL. See the descriptions of individual calls for more details about specific parameters.
The Syntax section (in the description of each function) uses angle brackets (< >) to indicate unused parameters.
Parameter Descriptions
Parameters for the OCI functions are described in terms of their type and their mode. When a parameter is a CDA or an LDA, the type is a Cda_Def * or a Lda_Def *. That is, a pointer to a Cda_Def or Lda_Def structure as defined in ocidfn.h (see page A - 8).Note: The OCI program must allocate these structures, not just the pointers.
Parameter Modes
When a parameter is a generalized pointer (that is, it can be a pointer to any variable or array of variables, depending on the requirements of your program), its type is listed as a ub1 pointer. The mode of a parameter has three possible values:
IN
A parameter that passes data to Oracle.
OUT
A parameter that receives data from Oracle on this or a subsequent call.
IN/OUT
A parameter that passes data on the call and receives data on the return from this or a subsequent call.
Function Return Values
When called from a C program, OCI functions return an integer value. The return value is 0 if the function completed without error. If a non-zero value is returned, an error occurred. In that event, you should check the return code field in the CDA to get the error number. The example programs in Appendix A demonstrate this.The shorter code fragments in this chapter do not always check for errors.
Note: oerhms(), sqlld2(), and sqllda() are exceptions to this rule. oerhms() returns the length of the message. sqlld2() and sqllda() are void functions that return error indications in the LDA parameter.
Variable Location
When you bind and define program variables using obindps(), obndra(), obndrv(), obndrn(), odefinps() and odefin(), they are known to Oracle by their addresses. The address when bound must remain valid when the statement is executed.If you pass LDA and CDA structures to other non-OCI functions in your program, always pass them as pointers, not by value. Oracle updates the fields in these structures after OCI calls. You also lose important information if your program uses copies of these structures, which will not be updated by OCI calls.
Caution: A change in the location of local variables may also cause errors in an OCI program. When the address of a local variable used in a subsequent call is passed to Oracle as a parameter in a bind or define call, you must be certain that the addressed variable is actually at the specified location when it is used in the subsequent execute or fetch call.
For more information about variable locations, see the section "Optimizing Compilers" .
obindps
Purposeobindps() associates the address of a program variable with a placeholder in a SQL or PL/SQL statement. Unlike older OCI bind calls, obindps() can be used to bind placeholders to be used in piecewise operations, or operations involving arrays of structures.
Syntaxobindps(Cda_Def *cursor, ub1 opcode, text *sqlvar, [sb4 sqlvl], ub1 *pvctx, sb4 progvl, sword ftype, <sword scale>, [sb2 *indp], [ub2 *alenp], [ub2 *rcodep], sb4 pv_skip, sb4 ind_skip, sb4 alen_skip, sb4 rc_skip, [ub4 maxsiz], [ub4 *cursiz], <text *fmt>, <sb4 fmtl>, <sword fmtt>);
Comments
obindps() is used to associate the address of a program variable with a placeholder in a SQL or PL/SQL statement. Additionally, it can indicate that an application will be providing inserted or updated data incrementally at runtime. This piecewise insert is designated in the opcode parameter. obindps() is also used when an application will be inserting data stored in an array of structures.
Note: This function is only compatible with Oracle Server release 7.3 or later. If a release 7.3 application attempts to use this function against a release 7.2 or earlier server, an error message is likely to be generated. At that point you must restart execution.
With the introduction of obindps() there are now four fully-supported calls for binding input parameters, the other three being the older obndra(), obndrn() and obndrv(). Application developers should consider the following points when determining which bind call to use:
- obindps() is supported only when a program is linked in deferred mode. If it is necessary to link in non-deferred mode, another bind routine must be used. In this case, the ability to handle piecewise operations and arrays of structures is not supported.
- obindps() is more complex than the older bind calls. Users who are not performing piecewise operations and are not using arrays of structures may choose to use one of the older routines.
- obindps() does not support the ability to do a positional bind. If this functionality is needed, the bind should be performed using obndrn().
See the sections "Piecewise Insert, Update and Fetch," and "Arrays of Structures" for more information about piecewise operations, arrays of structures, skip parameters and the obindps() call.
The following sample code demonstrates the use of obindps() in an OCI program which performs an insert from an array of structures. This code is provided for demonstration purposes only, and does not constitute a complete program. Most of the work in the program is done within the insert_records() function.
For sample code demonstrating an array fetch, see the description of the odefinps() routine later in this chapter. For sample code demonstrating the use of obindps() for a piecewise insert, see the description of the ogetpi() routine later in this chapter.
... /* OCI #include statements */ #define DEFER_PARSE 1 /* oparse flags */ #define NATIVE 1 #define VERSION_7 2 #define ARRAY_SIZE 10
#define OCI_EXIT_FAILURE 1 /* exit flags */ #define OCI_EXIT_SUCCESS 0
void insert_records();
struct emp_record /* employee data record */
{ int empno;
char ename[11];
char job[11];
int mgr;
char hiredate[10];
float sal;
float comm;
int deptno;
};
typedef struct emp_record emp_record;
struct emp_record_indicators
{ short empno; /* indicator variable record */
short ename;
short job;
short mgr;
short hiredate;
short sal;
short comm;
short deptno;
};
typedef struct emp_record_indicators emp_record_indicators; Lda_Def lda; /* login area */ ub1 hda[256]; /* host area */ Cda_Def cda; /* cursor area */
main()
{
emp_record emp_records[ARRAY_SIZE];
emp_record_indicators emp_rec_inds[ARRAY_SIZE];
int i=0;
char yn[4];
... /* log on to the database */
for (i=0;i<ARRAY_SIZE;i++)
{
... /* prompt user for data necessary */
... /* to fill emp_records and */
... /* emp_records_inds arrays */ }
insert_records(i,&emp_records, &emp_records_inds);
... /* log off from the database */
}
/* Function insert_records(): This function inserts the array */
/* of records passed to it. */
void insert_records(n, emp_records, emp_rec_inds)
int n;
emp_record emp_records[];
emp_record_indicators emp_rec_inds[];
{
text *sqlstmt =(text *) "INSERT INTO EMP (empno,ename, deptno) /
VALUES (:empno, :ename, :deptno)";
if (oopen(&cda, &lda, (text *)0, -1, -1, (text *)0, -1))
exit(OCI_EXIT_FAILURE);
if (oparse(&cda, sqlstmt, (sb4)-1, 0, (ub4)VERSION_7))
exit(OCI_EXIT_FAILURE);
if (obindps(&cda, 1, (text *)":empno",
strlen(":empno"), (ub1 *)&emp_records[0].empno,
sizeof(emp_records[0].empno),
SQLT_INT, (sword)0, (sb2 *) &emp_rec_inds[0].empno,
(ub2 *)0, (ub2 *)0, (sb4) sizeof(emp_record),
(sb4) sizeof(emp_record_indicators), 0, 0,
0, (ub4 *)0, (text *)0, 0, 0))
exit(OCI_EXIT_FAILURE);
if (obindps(&cda, 1, (text *)":ename",
strlen(":ename"), (ub1 *)emp_records[0].ename,
sizeof(emp_records[0].ename),
SQLT_STR, (sword)0, (sb2 *) &emp_rec_inds[0].ename,
(ub2 *)0, (ub2 *)0, (sb4) sizeof(emp_record),
(sb4) sizeof(emp_record_indicators), 0, 0,
0, (ub4 *)0, (text *)0, 0, 0))
exit(OCI_EXIT_FAILURE);
if (obindps(&cda, 1, (text *)":deptno",
strlen(":deptno"), (ub1 *)&emp_records[0].deptno,
sizeof(emp_records[0].deptno),
SQLT_INT, (sword)0, (sb2 *) &emp_rec_inds[0].deptno,
(ub2 *)0, (ub2 *)0, (sb4) sizeof(emp_record),
(sb4) sizeof(emp_record_indicators),
0, 0, 0, (ub4 *)0, (text *)0, 0, 0))
exit(OCI_EXIT_FAILURE);
if (oexn(&cda,n,0))
exit(OCI_EXIT_FAILURE);
ocom(&lda); /* commit the insert */
if (oclose(&cda)) /* close cursor */
exit(OCI_EXIT_FAILURE);
}
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
| opcode | ub1 | IN |
| sqlvar | text * | IN |
| sqlvl | sb4 | IN |
| pvctx | ub1* | IN |
| progvl | sb4 | IN |
| ftype | sword | IN |
| scale | sword | IN |
| indp | sb2 * | IN/OUT |
| alenp | ub2 * | IN |
| rcodep | ub2 * | OUT |
| pv_skip | sb4 | IN |
| ind_skip | sb4 | IN |
| alen_skip | sb4 | IN |
| rc_skip | sb4 | IN |
| maxsiz | ub4 | IN |
| cursiz | ub4 * | IN/OUT |
| fmt | text * | IN |
| fmtl | sb4 | IN |
| fmtt | sword | IN |
Standard scalar and array binds are those binds which were previously possible using other OCI bind calls (obndra(), obndrv(), and obndrn()).
cursor A pointer to the CDA associated with the SQL statement or PL/SQL block being processed.
opcode Piecewise bind: pass as 0.
Arrays of structures or standard bind: pass as 1.
sqlvar Specifies the address of a character string holding the name of a placeholder (including the preceding colon, e.g., ":varname") in the SQL statement being processed.
sqlvl The length of the character string in sqlvar, including the preceding colon. For example, the placeholder ":employee" has a length of nine. If the string is null terminated, this parameter can be specified as -1.
pvctx Piecewise bind: A pointer to a context block entirely private to the application. This should be used by the application to store any information about the column being bound. One possible use would be to store a pointer to a file which will be referenced later. Each bind variable can then have its own separate file pointer. This pointer can be retrieved during a call to ogetpi().
Arrays of structures or standard bind: A pointer to a program variable or array of program variables from which input data will be retrieved when the SQL statement is executed. For arrays of structures this should point to the first scalar element in the array of structures being bound. This parameter is equivalent to the progv parameter from the older OCI bind calls.
progvl Piecewise bind: This should be passed in as the maximum possible size of the data element of type ftype.
Arrays of structures or standard bind: This should be passed as the length in bytes of the datatype of the program variable, array element or the field in a structure which is being bound.
ftype The external datatype code of the program variable being bound. Oracle converts the program variable from external to internal format before it is bound to the SQL statement. See the section "External Datatypes" for a list of datatype codes, and the listings of ocidem.h and ocidfn.h in Appendix A for lists of constant definitions corresponding to datatype codes.
For piecewise operations, the valid datatype codes are 1 (VARCHAR2), 5 (STRING), 8 (LONG) and 24 (LONG RAW).
scale Not normally used in C. See the description of OBNDRV for more information about this parameter.
indp Pointer to an indicator variable or array of indicator variables. For arrays of structures this may be an interleaved array of column-level indicator variables. See page 2 - 29 for more information about indicator variables.
alenp Piecewise bind: pass as (ub2 *)0.
Arrays of structures or standard bind: A pointer to a variable or array containing the length of data elements being bound. For arrays of structures, this may be an interleaved array of column-level length variables. The maximum usable size of the array is determined by the maxsiz parameter.
rcodep Pointer to a variable or array of variables where column-level error codes are returned after a SQL statement is executed. For arrays of structures, this may be an interleaved array of column-level return code variables.
Typical error codes would indicate that data in progv has been truncated (ORA-01406) or that a null occurred on a SELECT or PL/SQL FETCH (ORA-01405).
pv_skip Piecewise bind or standard scalar bind: pass as zero or NULL.
Arrays of structures or standard array bind: This is the skip parameter for an array of structures holding program variables being bound. In general, this value will be sizeof(structure). If a standard array bind is being performed, this value should equal the size of one element of the array being bound.
ind_skip Piecewise bind or standard scalar bind: pass as zero or NULL.
Arrays of structures or standard array bind: This is the skip parameter for an array of indicator variables associated with an array holding program data to be inserted. This parameter will either equal the size of one indicator parameter structure (for arrays of structures) or the size of one indicator variable (for standard array bind).
alen_skip Piecewise bind or standard scalar bind: pass as zero or NULL.
Arrays of structures or standard array bind: This is the skip parameter for an array of data lengths associated with an array holding program data to be inserted. This parameter will either equal the size of one length variable structure (for arrays of structures) or the size of one length variable (for standard array bind).
rc_skip Piecewise bind or standard scalar bind: pass as zero or NULL.
Arrays of structures or standard array bind: This is the skip parameter for an array used to store returned column-level error codes associated with the execution of a SQL statement. This parameter will either equal the size of one return code structure (for arrays of structures) or the size of one return code variable (for standard array bind).
maxsiz The maximum size of an array being bound to a PL/SQL table. Values range from 1 to 32512, but the maximum size of the array depends on the datatype. The maximum array size is 32512 divided by the internal size of the datatype.
This parameter is only relevant when binding to PL/SQL tables. Set this parameter to ((ub4)0) for SQL scalar or array binds.
cursiz A pointer to the actual number of elements in the array being bound to a PL/SQL table.
If progv is an IN parameter, set the cursiz parameter to the size of the array being bound. If progv is an OUT parameter, the number of valid elements being returned in the progv array is returned after PL/SQL block is executed.
This parameter is only relevant when binding to PL/SQL tables. Set this parameter to ((ub4 *) 0) for SQL scalar or array binds.
fmt Not normally used in C. See the description of OBNDRV for more information about this parameter.
fmtl Not normally used in C. See the description of OBNDRV for more information about this parameter.
fmtt Not normally used in C. See the description of OBNDRV for more information about this parameter.
See Also
obndra(), obndrn(), obndrv(), odefinps(), ogetpi(), osetpi().
obndra
Purposeobndra() associates the address of a program variable or array with a placeholder in a SQL statement or PL/SQL block.
Syntaxobndra(Cda_Def *cursor, text *sqlvar, [sword sqlvl], ub1 *progv, sword progvl, sword ftype, <sword scale>, [sb2 *indp], [ub2 *alen], [ub2 *arcode], [ub4 maxsiz], [ub4 *cursiz], <text *fmt>, <sword fmtl>, <sword fmtt>);
Comments
You can use obndra() to bind scalar variables or arrays in your program to placeholders in a SQL statement or a PL/SQL block. The alen parameter of the obndra() function allows you to change the size of the bound variable without actually rebinding the variable.
Note:
If cursor is a cursor variable that has been OPENed FOR in a PL/SQL block, then obndra() returns an error, unless a new SQL statement or PL/SQL block has been parsed on it.
When you bind arrays in your program to PL/SQL tables, you must use obndra(), because this function provides additional parameters that allow you to control the maximum size of the table and to retrieve the current table size after the block executes.
Note: Applications running against a release 7.3 or later server that need to perform piecewise operations or utilize arrays of structures must use the newer obindps() routine instead of obndra().
The obndra() function must be called after you call oparse() to parse the statement containing the PL/SQL block and before calling oexn() or oexec() to execute it.
Once you have bound a program variable, you can change the value in the variable (progv) and length of the variable (progvl) and re-execute the block without rebinding.
However, if you must change the type of the variable, you must reparse the statement or block and rebind the variable before re-executing.
The following short, but complete, example program shows how to use obndra() to bind arrays in a C program to tables in PL/SQL procedures.
#include <stdio.h> #include <oratypes.h> #include <ocidfn.h> #include <ocidem.h>
Cda_Def cda;
Lda_Def lda;
/* set up the table */
text *dt = (text *) "DROP TABLE part_nos";
text *ct = (text *) "CREATE TABLE part_nos (partno NUMBER, description/
VARCHAR2(20))";
text *cp = (text *) "/
CREATE OR REPLACE PACKAGE update_parts AS/n/
TYPE part_number IS TABLE OF part_nos.partno%TYPE/n/
INDEX BY BINARY_INTEGER;/n/
TYPE part_description IS TABLE OF part_nos.description%TYPE/n/
INDEX BY BINARY_INTEGER;/n/
PROCEDURE add_parts (n IN INTEGER,/n/
descrip IN part_description,/n/
partno IN part_number);/n/
END update_parts;";
text *cb = (text *) "/
CREATE OR REPLACE PACKAGE BODY update_parts AS/n/
PROCEDURE add_parts (n IN INTEGER,/n/
descrip IN part_description,/n/
partno IN part_number) is/n/
BEGIN/n/
FOR i IN 1..n LOOP/n/
INSERT INTO part_nos/n/
VALUES (partno(i), descrip(i));/n/
END LOOP;/n/
END add_parts;/n/
END update_parts;";
#define DESC_LEN 20
#define MAX_TABLE_SIZE 1200
text *pl_sql_block = (text *) "/
BEGIN/n/
update_parts.add_parts(3, :description, :partno);/n/
END;";
text descrip[3][20] = {"Frammis", "Widget", "Thingie"};
sword numbers[] = {12125, 23169, 12126};
ub2 descrip_alen[3] = {DESC_LEN, DESC_LEN, DESC_LEN};
ub2 descrip_rc[3];
ub4 descrip_cs = (ub4) 3;
ub2 descrip_indp[3];
ub2 num_alen[3] = {
(ub2) sizeof (sword),
(ub2) sizeof (sword),
(ub2) sizeof (sword) };
ub2 num_rc[3];
ub4 num_cs = (ub4) 3;
ub2 num_indp[3];
ub1 hda[256];
main()
{
printf("Connecting to Oracle...");
if (olog(&lda, hda, "scott/tiger", -1, 0, -1, 0, -1,
OCI_LM_DEF)) {
printf("Cannot logon as scott/tiger. Exiting.../n");
exit(1);
}
if (oopen(&cda, &lda, NULL, -1, -1, NULL, -1)) {
printf("Cannot open cursor, exiting.../n");
exit(1);
}
/* Drop the table. */
printf("/nDropping table...");
if (oparse(&cda, dt, -1, 0, 2))
if (cda.rc != 942)
oci_error();
printf("/nCreating table...");
if (oparse(&cda, ct, -1, 0, 2))
oci_error();
/* Parse and execute the create package statement. */
printf("/nCreating package...");
if (oparse(&cda, cp, -1, 0, 2))
oci_error();
if (oexec(&cda))
oci_error();
/* Parse and execute the create package body statement. */
printf("/nCreating package body...");
if (oparse(&cda, cb, -1, 0, 2))
oci_error();
if (oexec(&cda))
oci_error();
/* Parse the anonymous PL/SQL block that calls the
stored procedure. */
printf("/nParsing PL/SQL block...");
if (oparse(&cda, pl_sql_block, -1, 0, 2))
oci_error();
/* Bind the C arrays to the PL/SQL tables. */
printf("/nBinding arrays...");
if (obndra(&cda, (text *) ":description", -1, (ub1 *) descrip,
DESC_LEN, VARCHAR2_TYPE, -1, descrip_indp, descrip_alen,
descrip_rc, (ub4) MAX_TABLE_SIZE, &descrip_cs, (text *) 0,
-1, -1))
oci_error();
if (obndra(&cda, (text *) ":partno", -1, (ub1 *) numbers,
(sword) sizeof (sword), INT_TYPE, -1, num_indp,
num_alen, num_rc, (ub4) MAX_TABLE_SIZE, &num_cs,
(text *) 0, -1, -1))
oci_error();
printf("/nExecuting block...");
if (oexec(&cda)) oci_error();
printf("/n");
if (oclose(&cda)) {
printf("Error closing cursor!/n");
return -1;
}
if (ologof(&lda)) {
printf("Error logging off!/n");
return -1;
}
exit(1);
}
oci_error()
{
text msg[600];
sword rv;
rv = oerhms(&lda, cda.rc, msg, 600);
printf("/n/n%.*s", rv, msg);
printf("Processing OCI function %s/n", oci_func_tab[cda.fc]);
if (oclose(&cda))
printf("Error closing cursor!/n");
if (ologof(&lda))
printf("Error logging off!/n");
exit(1);
}
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
| sqlvar | text * | IN |
| sqlvl | sword | IN |
| progv (2) | ub1 * (1) | IN/OUT (3) |
| progvl | sword | IN |
| ftype | sword | IN |
| scale | sword | IN |
| indp (2) | sb2 * | IN/OUT (3) |
| alen (2) | ub2 * | IN/OUT |
| arcode (2) | ub2 * | OUT (4) |
| maxsiz | ub4 | IN |
| cursiz | ub4 * | IN/OUT (3) |
| fmt | text * | IN |
| fmtl | sword | IN |
| fmtt | sword | IN |
Note 2. If maxsiz > 1, must be an array with cardinality at least as great as maxsiz.
Note 3. IN/OUT parameter used or returned on the execute or fetch call.
Note 4. OUT parameter returned on the fetch call.
cursor A pointer to the CDA associated with the SQL statement by the oparse() call.
sqlvar Specifies the address of a character string containing the name of a placeholder (including the preceding colon) in the SQL statement.
sqlvl The length of the character string sqlvar, including the preceding colon. For example, the placeholder :EMPLOYEE has a length of nine. If the placeholder name is a null-terminated character string (as in the example in this section), this parameter can be omitted (passed as -1).
progv A pointer to a program variable or array of program variables from which input data will be retrieved or into which output data will be placed when oexec(), oexn(), or oexfet() is executed.
progvl The length in bytes of the program variable or array element. Because obndra() might be called only once for many different progv values on successive execute calls, progvl must contain the maximum length of progv.
Note: The datatype of progvl is sword. On some systems, this type might be only two bytes. When binding LONG VARCHAR and LONG VARRAW buffers, this limits the maximum length of the buffer to 64K bytes. So, to bind a longer buffer for these datatypes, set progvl to -1, and pass the actual data area length (total buffer length - sizeof (sb4)) in the first four bytes of progv. Set this value before calling obndra().
ftype The external datatype of the program variable in the user program. Oracle converts the program variable from external to internal format before it is bound to the SQL statement. There is a list of external datatypes and type codes in the section "External Datatypes" .
scale Only used for PACKED DECIMAL variables, which are not normally used in C. Set this parameter to -1. See the description of the OBNDRV routine for information about this parameter.
indp A pointer to an indicator variable, or array of indicator variables if progv is an array. As an array, indp must contain at least the same number of elements as progv.
See page 2 - 29 for more information about indicator variables.
alen A pointer to an array of elements containing the length of the data. This is the effective length of the bind variable element, not the size of the array. For example, if the progv parameter is an array declared as
text arr[5][20];
then alen should point to an array of at least five elements. The maximum usable size of the array is determined by the maxsiz parameter.
If arr in the above example is an IN parameter, each element in the array pointed to by alen should be set to the length of the data in the corresponding element in the arr array (<=20 in this example) before the execute call.
If arr in the above example is an OUT parameter, the length of the returned data appears in the array pointed to by alen after the PL/SQL block is executed.
Once the bind is done using obndra(), you can change the data length of the bind variable without rebinding. However, the length cannot be greater than that specified in alen.
arcode An array containing the column-level error return codes. This parameter points to an array that will contain the error code for the bind variable after the execute call. The error codes that can be returned in arcode are those that indicate that data in progv has been truncated or that a null occurred on a SELECT or PL/SQL FETCH, for example, ORA-01405 or ORA-01406.
If obndra() binds an array of elements (that is, maxsiz is greater than one), then arcode must also point to an array of at least equal size.
maxsiz The maximum size of an array being bound to a PL/SQL table. Values range from 1 to 32512, but the maximum size of the array depends on the datatype. The maximum array size is 32512 divided by the internal size of the datatype.
This parameter is only relevant when binding to PL/SQL tables. Set this parameter to ((ub4)0) for SQL scalar or array binds.
cursiz A pointer to the actual number of elements in the array being bound to a PL/SQL table.
If progv is an IN parameter, set the cursiz parameter to the size of the array being bound. If progv is an OUT parameter, the number of valid elements being returned in the progv array is returned after PL/SQL block is executed.
This parameter is only relevant when binding to PL/SQL tables. Set this parameter to ((ub4 *) 0) for SQL scalar or array binds.
fmt Not normally used in C. See the description of OBNDRV for more information about this parameter.
fmtl Not normally used in C. See the description of OBNDRV for more information about this parameter.
fmtt Not normally used in C. See the description of OBNDRV for more information about this parameter.
See Also
obindps(), obndrv(), oexec(), oexn(), oparse().
obndrn obndrv
Purposeobndrn() and obndrv() associate the address of a program variable with the specified placeholder in the SQL statement. The placeholder is identified by name for the obndrv() function, and by number for obndrn().
Syntaxobndrn(Cda_Def *cursor, sword sqlvn, ub1 *progv, sword progvl, sword ftype, <sword scale>, [sb2 *indp], <text *fmt>, <sword fmtl>, <sword fmtt>); obndrv(Cda_Def *cursor, text *sqlvar, [sword sqlvl], ub1 *progv, sword progvl, sword ftype, <sword scale>, [sb2 *indp], <text *fmt>, <sword fmtl>, <sword fmtt>);
Comments
You can call either obndrv() or obndrn() to bind the address of a variable in your program to a placeholder in the SQL statement being processed. If your application needs to perform piecewise operations or utilize arrays of structures, you must bind your variables using obindps() instead.
Note:
If cursor is a cursor variable that has been OPENed FOR in a PL/SQL block, then obndrn() or obndra() return an error, unless a new SQL statement or PL/SQL block has been parsed on it.
If you use obndrv(), the placeholder in the SQL statement consists of a colon (:) followed by a SQL identifier. The placeholder is not a program variable. For example, the SQL statement
SELECT ename,sal,comm FROM emp WHERE deptno = :Dept AND
comm > :Min_com
has two placeholders, :Dept and :Min_com.
If you use obndrn(), the placeholders in the SQL statement consist of a colon followed by a literal integer in the range 1 to 255. The SQL statement
SELECT ename,sal,comm FROM emp WHERE deptno = :2 AND comm > :1
has two placeholders, :1 and :2.
An obndrv() call that binds the :Dept placeholder in the first SQL statement above to the program variable dept_num is
#define INT 3 /* external datatype code for integer */
Cda_Def cursor;
sword dept_num, minimum_comm;
...
obndrv(&cursor, ":Dept", -1, (ub1 *) &dept_num,
(sword) sizeof(sword), INT, -1, (sb2*) 0, (text *) 0, -1, -1);
Because the literal ":Dept" is a null-terminated string, the sqlvl parameter is not needed; you pass it as -1. Some of the remaining parameters are optional. For example, indp, the pointer to an indicator variable, is optional and not used in this example. It is passed as 0 cast to an sb2 pointer. fmt is not used, because the datatype is not packed decimal or display signed leading separate. Its absence is indicated by passing a null pointer.
If you use obndrn(), the parameter sqlvn identifies the placeholder by number. If sqlvn is set to 1, the program variable is bound to the placeholder :1. For example, obndrn() is called to bind the program variable minimum_comm to the placeholder :2 in the second SQL statement above as follows:
obndrn(&cursor, 2, (ub1 *) &dept_num, (sword) sizeof(sword),
INT, -1, (sb2 *) 0, (text *) 0, -1, -1);
where the placeholder :2 is indicated in the sqlvn parameter by passing the value 2. The sqlvn parameter can be a variable and a literal.
You cannot use obndrn() in a PL/SQL block to bind program variables to placeholders, because PL/SQL does not recognize numbered placeholders. Always use obndra() (or obndrv()) and named placeholders within PL/SQL blocks.
The obndrv() or obndrn() function must be called after you call oparse() to parse the SQL statement and before calling oexn(), oexec(), or oexfet() to execute it. Once you have bound a program variable, you can change the value in the variable and re-execute the SQL statement without rebinding.
For example, if you have bound the address of dept_num to the placeholder ":Dept", and you now want to use new_dept_num (of the same datatype) when executing the SQL statement , you must call obndrv() again to bind the new program variable to the placeholder.
However, if you need to change the type or length of the variable, you must reparse and rebind before re-executing.
You should not use obndrv() and obndrn() after an odescr() call. If you do, you must first reparse and then rebind all variables.
At the time of the bind, Oracle stores the address of the program variable. If the same placeholder occurs more than once in the SQL statement, a single call to obndrv() or obndrn() binds all occurrences of the placeholder to the bind variable.
Note: You can bind an array using obndrv() or obndrn(), but you must then specify the number of rows with either oexn(), oexfet(), or ofen(). This is the Oracle array interface.
The completion status of the bind is returned in the return code field of the CDA. A return code of zero indicates successful completion.
If your program is linked using the deferred mode option, bind errors that would be returned immediately in non-deferred mode are not detected until the bind operation is actually performed. This happens on the first describe (odescr()) or execute (oexec(), oexn(), or oexfet()) call after the bind.
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
| sqlvar | text * | IN |
| sqlvl | sword | IN |
| sqlvn | sword | IN |
| progv | ub1 * | IN/OUT (1) |
| progvl | sword | IN |
| ftype | sword | IN |
| scale | sword | IN |
| indp | sb2 * | IN/OUT (1,2) |
| fmt | text * | IN |
| fmtl | sword | IN |
| fmtt | sword | IN |
Note 2. Can have the mode OUT when bound in a PL/SQL statement.
cursor A pointer to the CDA associated with the SQL statement by the oparse() call.
sqlvar Used only with obndrv(), this parameter specifies the address of a character string containing the name of a placeholder (including the preceding colon) in the SQL statement.
sqlvl Used only with obndrv(), the sqlvl parameter is the length of the character string sqlvar, including the preceding colon. For example, the placeholder :Employee has a length of nine. If the placeholder name is a null-terminated character string, this parameter can be omitted (passed as -1).
sqlvn Used only with obndrn(), this parameter specifies a placeholder in the SQL statement referenced by the cursor by number. For example, if sqlvn is an integer literal or a variable equal to 2, it refers to all placeholders identified by :2 within the SQL statement.
progv A pointer to a program variable or array variables. Values are input to Oracle when either oexec() or oexn() is executed. Data are retrieved when either oexfet(), ofen(), or ofetch() is performed.
progvl The length in bytes of the program variable or array element. Since obndrv() or obndrn() might be called only once for many different progv values on successive execute or fetch calls, progvl must contain the maximum length of progv.
Note: The datatype of progvl is sword. On some systems, this type might be only two bytes. When binding LONG VARCHAR and LONG VARRAW buffers, this limits the maximum length of the buffer to 64K bytes. To bind a longer buffer for these datatypes, set progvl to -1 and pass the actual data area length (total buffer length - sizeof (sb4)) in the first four bytes of progv. Set this value before calling obndrn() or obndrv().
ftype The Oracle external datatype of the program variable. Oracle converts the program variable between external and internal formats when the data is input to or retrieved from Oracle. See page 3 - 8 for a list of external datatypes.
scale The scale parameter is valid only for PACKED DECIMAL variables, which are not normally used in C applications. Set this parameter to -1 to indicate that it is unused. See the description of the OBNDRV routine for information about this parameter.
indp A pointer to a short integer (or array of short integers) that serves as indicator variables.
On Input
If the indicator variable contains a negative value when the statement is executed, the corresponding column is set to null; otherwise, it is set to the value pointed to by progv.
On output
If the indicator variable contains a negative value after the fetch, the corresponding column contained a null.
fmt Not normally used in C. See the description of OBNDRV for more information about this parameter.
fmtl Not normally used in C. See the description of OBNDRV for more information about this parameter.
fmtt Not normally used in C. See the description of OBNDRV for more information about this parameter.
See Also
obindps(), obndra(), odescr(), oexec(), oexfet(), oexn(), oparse().
obreak
Purposeobreak() performs an immediate (asynchronous) abort of any currently executing OCI function that is associated with the specified LDA. It is normally used to stop a long-running execute or fetch that has not completed.
Syntaxobreak(Lda_Def *lda);
Comments
If no OCI function is active when obreak() is called, obreak() will be ignored unless the next OCI function called is a fetch. In this case, the subsequent fetch call will be aborted.
obreak() is the only OCI function that you can call when another OCI function is in progress. It should not be used when a connect operation (olog()) is in progress, because the LDA is in an indeterminate state. obreak() cannot return a reliable error status to the LDA, because it might be called when the Oracle internal status structures are in an inconsistent state.
Note: obreak() aborts the currently executing OCI function not the connection.
obreak() is not guaranteed to work on all operating systems and does not work on all protocols. In some cases, obreak() may work with one protocol on an operating system, but may not work with other protocols on the same operating system.
Working with the OCI in non-blocking mode can provide a more consistent way of interrupting a SQL statement. See the section "Non-Blocking Mode" for more information.
The following example shows how to use obreak() in an OCI program to interrupt a query if it does not complete in six seconds. This example works under many UNIX operating systems. The example must be linked two-task to work correctly.
#include <stdio.h> #include <signal.h> #include <ocidfn.h> #include <ocidem.h> Lda_Def lda; Cda_Def cda; ub1 hda[256];
/* Define a new alarm function, to replace the standard
alarm handler. */
sighandler()
{
sword rv;
fprintf(stderr, "Alarm signal has been caught/n");
/* Call obreak() to interrupt the SQL statement in progress. */
if (rv = obreak(&lda))
fprintf(stderr, "Error %d on obreak/n", rv);
else
fprintf(stderr, "obreak performed/n");
}
err()
{
text errmsg[512];
sword n;
n = oerhms(&lda, cda.rc, errmsg, sizeof (errmsg));
fprintf(stderr, "/n-Oracle error-/n%.*s", n, errmsg);
fprintf(stderr, "while processing OCI function %s/n",
oci_func_tab[cda.fc]);
oclose(&cda);
ologof(&lda);
exit(1);
}
main(argc, argv)
int argc;
char *argv[];
{
void *old_sig;
text name[10];
/* Connect to Oracle. Program must be linked two-task,
so connect using SQL*Net. */
if (olog(&lda, hda, argv[1], -1, argv[2], -1,
(text *) 0, -1, OCI_LM_DEF)) {
printf("cannot connect as %s/n", argv[1]);
exit(1);
}
if (oopen(&cda, &lda, 0, -1, -1, 0, -1)) {
printf("cannot open cursor data area/n");
exit(1);
}
signal(SIGALRM, sighandler);
/* Parse a query statement. */
if (oparse(&cda, "select ename from emp", -1, 0, 2))
err();
if (odefin(&cda, 1, name, sizeof (name), 1,
-1, (sb2 *) 0, (text *) 0, 0, -1,
(ub2 *) 0, (ub2 *) 0))
err();
if (oexec(&cda))
err();
/* Set the timeout */
alarm(1);
/* Begin the query. */
for (;;) {
if (ofetch(&cda)) {
/* Break if no data found (should never happen,
unless the alarm fails, or the emp table has
less than 6 or so rows). */
if (cda.rc == 1403) break;
/* When the alarm is caught and obreak is performed,
a 1013 error should be detected at this point. */
err();
}
printf("%10.10s/n", name);
/* Slow the query for the timeout. */
sigpause();
}
fprintf(stderr, "Unexpected termination./n");
err();
}
Parameter
| Parameter Name | Type | Mode |
| lda | Lda_Def * | IN |
See Also
ocan
Purposeocan() cancels a query after the desired number of rows have been fetched.
Syntaxocan(Cda_Def *cursor);
Comments
ocan() informs Oracle that the operation in progress for the specified cursor is complete. The ocan() function thus frees any resources associated with the specified cursor, but keeps the cursor associated with its parsed representation in the shared SQL area.
For example, if you require only the first row of a multi-row query, you can call ocan() after the first ofetch() operation to inform Oracle that your program will not perform additional fetches.
If you use the oexfet() function to fetch your data, specifying a non-zero value for the oexfet() cancel parameter has the same effect as calling ocan() after the fetch completes.
Parameter
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
See Also
oexfet(), ofen(), ofetch(), oparse().
oclose
Purposeoclose() disconnects a cursor from the data areas in the Oracle Server with which it is associated.
Syntaxoclose(Cda_Def *cursor);
Comments
The oclose() function frees all resources obtained by the oopen(), parse, execute, and fetch operations using the cursor. If oclose() fails, the return code field of the CDA contains the error code.
Parameter
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
See Also
ocof
Purposeocof() disables autocommit, that is, automatic commit of every SQL data manipulation statement.
Syntaxocof(Lda_Def *lda);
Comments
By default, autocommit is already disabled at the start of an OCI program. Turning on autocommit can have a serious impact on performance. So, if the ocon() (autocommit on) function enables autocommit for some special circumstance, use ocof() to disable autocommit as soon as it is practical.
If ocof() fails, the return code field of the LDA indicates the reason.
Parameter
| Parameter Name | Type | Mode |
| lda | Lda_Def * | IN/OUT |
See Also
ocom
Purposeocom() commits the current transaction.
Syntaxocom(Lda_Def *lda);
Comments
The current transaction starts from the olog() call or the last orol() or ocom() call, and lasts until an ocom(), orol(), or ologof() call is issued.
If ocom() fails, the return code field of the LDA indicates the reason.
Do not confuse the ocom() call (COMMIT) with the ocon() call (turn autocommit on).
Parameter
| Parameter Name | Type | Mode |
| lda | Lda_Def * | IN/OUT |
See Also
ocon(), olog(), ologof(), orol().
ocon
Purposeocon() enables autocommit, that is, automatic commit of every SQL data manipulation statement.
Syntaxocon(Lda_Def *lda);
Comments
By default, autocommit is disabled at the start of an OCI program. This is because it is more expensive and less flexible than placing ocom() calls after each logical transaction. When autocommit is on, a zero in the return code field after executing the SQL statement indicates that the transaction has been committed.
If ocon() fails, the return code field of the LDA indicates the reason
If it becomes necessary to turn autocommit on for some special circumstance, it is advisable to follow that with a call to ocof() to disable autocommit as soon as it is practical in order to maximize performance.
Do not confuse the ocon() function with the ocom() (COMMIT) function.
Parameter
| Parameter Name | Type | Mode |
| lda | Lda_Def * | IN/OUT |
See Also
odefin
Purposeodefin() defines an output variable for a specified select-list item of a SQL query.
Syntaxodefin(Cda_Def *cursor, sword pos, ub1 *buf, sword bufl, sword ftype, <sword scale>, [sb2 *indp], <text *fmt>, <sword fmtl>, <sword fmtt>, [ub2 *rlen], [ub2 *rcode]);
Comments
An OCI program must call odefin() once for each select-list item in a SQL statement. Each call to odefin() associates an output variable in your program with a select-list item of the query. odefin() can define scalar or string program variables which are compatible with the external datatype (ftype). See Table 3-2 for a list of datatypes and compatible variables. The output variable may also be the address of an array of scalars or strings for use with the oexfet() and ofen() functions.
Note: Applications running against a release 7.3 or later server that need to perform piecewise operations or utilize arrays of structures must use the newer odefinps() routine instead of odefin().
Oracle places data in the output variables when the program calls ofetch(), ofen(), or oexfet().
If you do not know the number of select-list items in the SQL statement, or the lengths and internal datatypes of the items, you can obtain this information at runtime using the odescr() function.
You can call odefin() only after you call oparse() to parse the SQL statement. You must also call odefin() before fetching the data.
odefin() associates output variables with select-list items using the position index of the select-list item in the SQL statement. Position indices start at 1 for the first (or leftmost) select-list item. For example, in the SQL statement
SELECT ename, empno, sal FROM emp WHERE sal > :min_sal
the select-list item SAL is in position 3, EMPNO is in position 2, and ENAME is in position 1.
If the type or length of bound variables changes between queries, you must reparse and rebind before re-executing.
You call odefin() to associate output buffers with the select-list items in the above statement as follows:
#define ENAME_LEN 20
Cda_Def cursor; /* allocate a cursor */
text employee_name[ENAME_LEN];
sword employee_number;
float salary;
sb2 ind_ename, ind_empno, ind_sal;
ub2 retc_ename, retc_empno, retc_sal;
ub2 retl_ename, retl_empno, retl_sal;
...
odefin(&cursor, 1, employee_name, ENAME_LEN, SQLT_STR,
-1, &ind_ename, 0, -1, -1, &retl_ename, &retc_ename);
odefin(&cursor, 2, &employee_number, (int) sizeof(int), SQLT_INT,
-1, &ind_empno, 0, -1, -1, &retl_empno, &retc_empno);
odefin(&cursor, 3, &salary, (int) sizeof(float), SQLT_FLT,
-1, &ind_sal, 0, -1, -1, &retl_sal, &retc_sal);
Oracle provides return code information at the row level using the return code field in the CDA. If you require return code information at the column level, you must include the optional rcode parameter, as in the examples above. During each fetch, Oracle sets rcode for the select-list item processed. This return parameter contains Oracle error codes, and indicates either successful completion (zero) or an exceptional condition, such as "null item fetched", "item fetched was truncated", or other non-fatal column errors. The following codes are some of those that can be returned in the rcode parameter:
| Code | Meaning |
| 0 | Success. |
| 1405 | A null was fetched. |
| 1406 | ASCII or string buffer data was truncated. The converted data from the database did not fit into the buffer. Check the value in indp, if specified, or rlen to determine the original length of the data. |
| 1454 | Invalid conversion specified: integers not of length 1, 2, or 4; reals not of length 4 or 8; invalid packed decimal conversions; packed decimal with more than 38 digits specified. |
| 1456 | Real overflow. Conversion of a database column or expression would overflow a floating-point number on this machine. |
| 3115 | Unsupported datatype. |
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
| pos | sword | IN |
| buf | ub1 * | IN (1) |
| bufl | sword | IN |
| ftype | sword | IN |
| scale | sword | IN |
| indp | sb2 * | IN (1) |
| fmt | text * | IN |
| fmtl | sword | IN |
| fmtt | sword | IN |
| rlen | ub2 * | IN (1) |
| rcode | ub2 * | IN (1) |
cursor A pointer to the CDA specified in the associated oparse() call. This may be either a regular cursor or a cursor variable.
pos An index for a select-list item in the query. Position indices start at 1 for the first (or leftmost) select-list item. The odefin() function uses the position index to associate output variables with a given select-list item. If you specify a position index greater than the number of items in the select-list, or less than 1, the behavior of odefin() is undefined.
If you do not know the number of items in the select-list, use the odescr() routine to determine it. See the second sample program in Appendix A for an example that does this.
buf A pointer to the variable in the user program that receives the data when ofetch(), ofen(), or oexfet() executes. The variable can be of any type into which an Oracle column or expression result can be converted. See Chapter 3 for more information on datatype conversions.
Note: If odefin() is being called to set up an array fetch operation using the ofen() or oexfet() functions, then the buf parameter must be the address of an array large enough to hold the set of items to be fetched.
bufl The length in bytes of the variable being defined. If buf is an array, this is the size in bytes of one element of the array.
Note: The datatype of bufl is sword. On some systems, this type might be only two bytes. When defining LONG VARCHAR and LONG VARRAW buffers, this appears to limit the maximum length of the buffer to 64K bytes. To define a longer buffer for these datatypes, set bufl to -1 and pass the actual data area length (total buffer length - sizeof (sb4)) in the first four bytes of buf. Set this value before calling odefin().
ftype The external datatype to which the select-list item is to be converted before it is moved to the output variable. A list of the external datatypes and datatype codes can be found in the "External Datatypes" section.
scale The scale of a packed decimal number. Not normally used in C.
indp The indp value, after the fetch, indicates whether the select-list item fetched was null, truncated, or returned intact. See "Indicator Values" for additional details.
If the output buffer size was too small to hold all of the data, the output was truncated. You can obtain the length of the data in the column using the expression
*(ub2 *) indp
If oparse() parses the SQL statement, and you do not define an indicator parameter for a column, a "fetched column value was truncated" error is returned for truncated select-list items.
Note: If odefin() is being called to set up an array fetch operation using the ofen() or oexfet() functions, then the indp parameter must be the address of an array large enough to hold indicator variables for all the items that will be fetched.
The indp parameter offers only a subset of the functionality provided by the rlen and rcode parameters.
fmt Not normally used in C. See the description of the ODEFIN routine for more information about packed decimal format specifiers.
fmtl Not normally used in C. See the description of the ODEFIN routine for more information about packed decimal format specifiers.
fmtt Not normally used in C. See the description of the ODEFIN routine for more information about packed decimal format specifiers.
rlen A pointer to a ub2 into which Oracle places the length of the data (plus length bytes, in the case of variable-length datatypes) after the fetch operation completes. If odefin() is being used to associate an array with a select-list item, the rlen parameter must also be an array of ub2s of the same size. Return lengths are valid after the ofetch(), ofen(), or oexfet() operation.
rcode A pointer to an unsigned short integer that receives the column return code after the fetch. The error codes that can be returned in rcode are those that indicate that data in the column has been truncated or that a null occurred, for example, ORA-01405 or ORA-01406.
If odefin() is being used to associate an array with a select-list item, the rcode parameter must also be an array of ub2s of the same size.
See Also
odefinps(), odescr(), oexfet(), ofen(), ofetch(), oparse().
odefinps
Purposeodefinps() defines an output variable for a specified select-list item in a SQL query. This call can also specify if an operation will be performed piecewise or with arrays of structures.
Syntaxodefinps(Cda_Def *cursor, ub1 opcode, sword pos, ub1 *bufctx, sb4 bufl, sword ftype, <sword scale>, [sb2 *indp],<text *fmt>, <sb4 fmtl>, <sword fmtt>, [ub2 *rlenp], [ub2 *rcodep], sb4 buf_skip, sb4 ind_skip, sb4 len_skip, sb4 rc_skip);
Comments
odefinps() is used to define an output variable for a specified select-list item in a SQL query. Additionally, it can indicate that an application will be fetching data incrementally at runtime. This piecewise fetch is designated in the opcode parameter. odefinps() is also used when an application will be fetching data into an array of structures.
Note: This function is only compatible with Oracle server release 7.3 or later. If a release 7.3 application attempts to use this function against a release 7.2 or earlier server, an error message is likely to be generated. At that point you must restart execution.
With the introduction of odefinps() there are now two fully-supported calls for binding input parameters, the other being the older odefin(). Application developers should consider the following points when determining which define call to use:
- odefinps() is supported only when a program is linked in deferred mode. If it is necessary to link in non-deferred mode, odefin() must be used. In this case, the ability to handle piecewise operations and arrays of structures is not supported.
- odefinps() is more complex than the older bind call. Users who are not performing piecewise operations and are not using arrays of structures may choose to use odefin().
See the sections "Piecewise Insert, Update and Fetch," and "Arrays of Structures" for more information about piecewise operations, arrays of structures, skip parameters and the odefinps() call.
The following sample code demonstrates the use of odefinps() in an OCI program which performs an insert from an array of structures. This code is provided for demonstration purposes only, and does not constitute a complete program. Most of the work in this program is done in the array_fetch() routine.
For sample code demonstrating an array insert, see the description of the obindps() routine earlier in this chapter. For sample code demonstrating the use of odefinps() for a piecewise fetch, see the description of the osetpi() routine later in this chapter.
... /* OCI #include statements */ #define DEFER_PARSE 1 /* oparse flags */ #define NATIVE 1 #define VERSION_7 2 #define NO_MORE_DATA 1403 #define ARRAY_SIZE 10
#define OCI_EXIT_FAILURE 1 /* exit flags */ #define OCI_EXIT_SUCCESS 0
void array_fetch(); void print_results();
struct emp_record
{ int empno;
char ename[11];
char job[11];
int mgr;
char hiredate[10];
float sal;
float comm;
int deptno;
};
typedef struct emp_record emp_record;
struct emp_record_indicators
{ short empno;
short ename;
short job;
short mgr;
short hiredate;
short sal;
short comm;
short deptno;
};
typedef struct emp_record_indicators emp_record_indicators;
Lda_Def lda; /* login area */ ub1 hda[256]; /* host area */ Cda_Def cda; /* cursor area */
main()
{
... /* log on to oracle */
array_fetch();
... /* log off of oracle */
}
/* Function array_fetch(): This function retrieves EMP data */
/* into an array of structs and prints them. */
void array_fetch()
{
emp_record emp_records[20]; emp_record_indicators emp_records_inds[20]; int printed=0; int cont=1; int ret_val;
text *sqlstmt = (text *) "SELECT empno,ename,deptno /
FROM emp";
if (oopen(&cda, &lda, (text *)0, -1, -1, (text *)0, -1))
exit(OCI_EXIT_FAILURE);
if (oparse(&cda, sqlstmt, (sb4)-1, 0, (ub4)VERSION_7))
exit(OCI_EXIT_FAILURE);
if (odefinps(&cda, 1, 1, (ub1 *) &emp_records[0].empno,
(ub4) sizeof(emp_records[0].empno), SQLT_INT, 0,
(sb2 *) &emp_records_inds[0].empno, (text *)0, 0, 0,(ub2 *) 0, (ub2 *) 0,
(sb4) sizeof(emp_record), (sb4)
sizeof(emp_record_indicators), 0, 0))
exit(OCI_EXIT_FAILURE);
if (odefinps(&cda, 1, 2, (ub1 *) emp_records[0].ename,
(ub4) sizeof(emp_records[0].ename), SQLT_STR, 0,
(sb2 *) &emp_records_inds[0].ename, (text *)0, 0, 0,(ub2 *) 0, (ub2 *) 0,
(sb4) sizeof(emp_record), (sb4)
sizeof(emp_record_indicators), 0, 0))
exit(OCI_EXIT_FAILURE);
if (odefinps(&cda, 1, 3, (ub1 *) &emp_records[0].deptno,
(ub4) sizeof(emp_records[0].deptno), SQLT_INT, 0,
(sb2 *) &emp_records_inds[0].deptno, (text *)0, 0,
0, (ub2 *) 0, (ub2 *) 0,
(sb4) sizeof(emp_record), (sb4)
sizeof(emp_record_indicators), 0, 0))
exit(OCI_EXIT_FAILURE);
oexec(&cda)
while (cont)
{
printf(" Empno/tEname /t Deptno/n");
printf("----------/t----------/t----------/n");
ret_val=ofen(&cda,(sword) ARRAY_SIZE);
switch (cda->rc) /* switch on return value */
{
case 0:
print_results(emp_records,emp_records_inds,cda->rpc -
printed);
printed=cda->rpc;
break;
case NO_MORE_DATA:
/* print last batch? */
if (cda->rpc > printed)
{
print_results(emp_records,emp_records_inds,cda->rpc -
printed);
printed=cda->rpc;
}
cont=0;
break;
default:
exit(OCI_EXIT_FAILURE);
}
}
if (oclose(&cda))
exit(OCI_EXIT_FAILURE);
}
void print_results(emp_records,emp_records_inds,n)
emp_record emp_records[];
emp_record_indicators emp_records_inds[];
int n;
{
int i;
for (i=0;i<n;i++)
{
if (emp_records_inds[i].empno == -1)
printf("%10.s/t","");
else
printf("%10.d/t", emp_records[i].empno);
printf("%-10.10s/t", (emp_records_inds[i].ename ==-1 ? "" :
emp_records[i].ename));
if (emp_records_inds[i].deptno== -1)
printf("%10.s/n","");
else
printf("%10.d/n", emp_records[i].deptno);
}
}
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
| opcode | ub1 | IN |
| pos | sword | IN |
| bufctx | ub1 * | IN |
| bufl | sb4 | IN |
| ftype | sword | IN |
| scale | sword | IN |
| indp | sb2 * | IN |
| fmt | text * | IN |
| fmtl | sb4 | IN |
| fmtt | sword | IN |
| rlenp | ub2 * | OUT |
| rcodep | ub2 * | IN |
| buf_skip | sb4 | IN |
| ind_skip | sb4 | IN |
| len_skip | sb4 | IN |
| rc_skip | sb4 | IN |
Standard scalar and array defines are those defines which were previously possible using odefin().
cursor A pointer to the CDA associated with the SELECT statement being processed.
opcode Piecewise define: pass as 0. Arrays of structures or standard define: pass as 1.
pos An index for the select-list column which needs to be defined. Position indices start from 1 for the first, or left-most, item of the query. The odefinps() function uses the position index to associate output variables with a given select-list item. If you specify a position index greater than the number of items in the select-list, or less than 1, the behavior of odefinps() is undefined.
If you do not know the number of items in the select list, use the odescr() routine to determine it. See the second sample program in Appendix A for an example that does this.
bufctx Piecewise define: A pointer to a context block entirely private to the application. This should be used by the application to store any information about the column being defined. One possible use would be to store a pointer to a file which will be referenced later. Each output variable can then have its own separate file pointer. The pointer can be retrieved by the application during a call to ogetpi().
Array of structures or standard define: This specifies a pointer to the program variable or the beginning of an array of program variables or structures into which the column being defined will be placed when the fetch is performed. This parameter is equivalent to the buf parameter of the odefin() call.
bufl Piecewise define: The maximum possible size of the column being defined.
Array of structures or standard define: The length (in bytes) of the variable pointed to by bufctx into which the column being defined will be placed when a fetch is performed. For an array define, this should be the length of the first scalar element of the array of variables or structures pointed to by bufctx.
ftype The external datatype to which the select-list item is to be converted before it is moved to the output variable. A list of the external datatypes and datatype codes can be found in the "External Datatypes" section.
For piecewise operations, the valid datatype codes are 1 (VARCHAR2), 5 (STRING), 8 (LONG) and 24 (LONG RAW).
scale Not normally used in C. See the description of OBNDRV for more information about this parameter.
indp A pointer to an indicator variable or an array of indicator variables. If arrays of structures are used, this points to a possibly interleaved array of indicator variable structures.
fmt Not normally used in C. See the description of OBNDRV for more information about this parameter.
fmtl Not normally used in C. See the description of OBNDRV for more information about this parameter.
fmtt Not normally used in C. See the description of OBNDRV for more information about this parameter.
rlenp A pointer to an element or array of elements which will hold the length of a column or columns after a fetch is done. If arrays of structures are used, this points to a possibly interleaved array of length variable structures.
rcodep A pointer to an element or array of elements which will hold column-level error codes which are returned by a fetch. If arrays of structures are used, this points to a possibly interleaved array of return code variable structures.
buf_skip Piecewise define or standard scalar define: pass as 0.
Array of structures or standard array define: this is the skip parameter which specifies the number of bytes to be skipped in order to get to the next program variable element in the array being defined. In general, this will be the size of one program variable for a standard array define, or the size of one structure for an array of structures.
ind_skip Piecewise define or standard scalar define: pass as 0.
Array of structures or standard array define: this is the skip parameter which specifies the number of bytes which must be skipped to get to the next indicator variable in the possibly interleaved array of indicator variables pointed to by indp. In general, this will be the size of one indicator variable for a standard array define, and the size of one indicator variable structure for arrays of structures.
len_skip Piecewise define or standard define: pass as 0.
Array of structures: this is the skip parameter which specifies the number of bytes which must be skipped to get to the next column length in the possibly interleaved array of column lengths pointed to by rlenp. In general, this will be the size of one length variable for a standard array define, and the size of one length variable structure for arrays of structures.
rc_skip Piecewise define or standard define: pass as 0.
Array of structures: this is the skip parameter which specifies the number of bytes which must be skipped to get to the next return code structure in the possibly interleaved array of return codes pointed to by rcodep. In general, this will be the size of one return code variable for a standard array define, and the size of one length variable structure for arrays of structures.
See Also
obindps(), odefin(), ogetpi(), osetpi().
odescr
Purposeodescr() describes select-list items for SQL queries. The odescr() function returns internal datatype and size information for a specified select-list item.
Syntaxodescr(Cda_Def *cursor, sword pos, sb4 *dbsize, [sb2 *dbtype], [sb1 *cbuf], [sb4 *cbufl], [sb4 *dsize], [sb2 *prec], [sb2 *scale], [sb2 *nullok]);
Comments
The odescr() function replaces the older odsc(). You call odescr() after you have parsed the SQL statement (using oparse()) and after binding all input variables. odescr() obtains the following information about select-list items in a query:
- maximum size (dbsize)
- internal datatype code (dbtype)
- column name (cbuf)
- length of the column name (cbufl)
- maximum display size (dsize)
- precision of numeric items (prec)
- scale of numerics (scale)
- whether null values are permitted in the column (nullok)
So, if you have placeholders for bind variables in a SELECT statement and you will use odescr() to obtain the size or datatype of select-list items, you should do the bind operation before the describe. If you need to rebind any input variables after performing a describe, you must reparse the SQL statement before rebinding.
Note: Note that the rebind operation might change the results returned for a select-list item.
The odescr() function is particularly useful for dynamic SQL queries. That is, queries in which the number of select-list items, and their datatypes and sizes might not be known until runtime.
The return code field of the CDA indicates success (zero) or failure (non-zero) of the odescr() call.
The odescr() function uses a position index to refer to select-list items in the SQL query statement. For example, the SQL statement
SELECT ename, sal FROM emp WHERE sal > :Min_sal
contains two select-list items: ENAME and SAL. The position index of SAL is 2, and ENAME's index is 1.
The example program below is a complete C program that shows how you can describe select-list items. The program allows the user to enter SQL query statements at runtime, and prints out the name of each select-list item, the length of the name, and the datatype. See also the sample program cdemo2.c for additional information on describing select lists.
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <oratypes.h>
#include <ocidfn.h>
#include <ocidem.h>
#define NPOS 13
Cda_Def cda;
Lda_Def lda;
ub1 hda[256];
main()
{
text sql_statement[256];
sword i, pos;
text cbuf[NPOS][20];
sb4 dbsize[NPOS], cbufl[NPOS], dsize[NPOS];
sb2 dbtype[NPOS], prec[NPOS], scale[NPOS], nullok[NPOS];
if (olog(&lda, hda, "scott", -1, "tiger", -1, 0, -1,
OCI_LM_DEF)) {
printf("Cannot connect as scott. Exiting.../n");
exit(1);
}
if (oopen(&cda, &lda, 0, -1, -1, 0, -1)) {
oci_error();
exit(1);
}
for (;;) {
printf("/nEnter a query or /"exit/"> ");
gets(sql_statement);
if (strncmp(sql_statement, "exit", 4) == 0) break;
/* parse the statement */
if (oparse(&cda, sql_statement, -1, 0, 0)) {
oci_error();
continue;
}
for (pos = 1; pos <= NPOS; pos++) {
cbufl[pos] = sizeof cbuf[pos];
if (odescr(&cda, pos, &dbsize[pos], &dbtype[pos],
&cbuf[pos], &cbufl[pos], &dsize[pos],
&prec[pos], &scale[pos], &nullok[pos])) {
if (cda.rc == 1007)
break;
oci_error();
continue;
}
}
/* print out the total count and the names
of the select-list items, column sizes, and datatype codes */
pos--;
printf("/nThere were %d select-list items./n", pos);
printf("Item name Length Datatype/n");
printf("/n");
for (i = 1; i <= pos; i++) {
printf("%*.*s", cbufl[i], cbufl[i], cbuf[i]);
printf("%*c", 25 - cbufl[i], ' ');
printf("%6d %8d/n", cbufl[i], dbtype[i]);
}
}
oclose(&cda);
ologof(&lda);
exit(0);
}
oci_error()
{
text msg[512];
printf("/nOracle ERROR/n");
oerhms(&lda, cda.rc, msg, (int) sizeof msg);
printf("%s", msg);
if (cda.fc != 0)
printf("processing OCI function %s/n",
oci_func_tab[cda.fc]);
}
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
| pos | sword | IN |
| dbsize | sb4 * | OUT |
| dbtype | sb2 * | OUT |
| cbuf | sb1 * | OUT |
| cbufl | sb4 * | IN/OUT |
| dsize | sb4 * | OUT |
| prec | sb2 * | OUT |
| scale | sb2 * | OUT |
| nullok | sb2 * | OUT |
pos The position index of the select-list item in the SQL query. Each item is referenced by position index, starting at one for the first (or leftmost) item. If you specify a position index greater than the number of items in the select-list or less than one, odescr() returns a "variable not in select-list" error in the return code field of the CDA.
dbsize A pointer to a signed long that receives the maximum size of the column, as stored in the Oracle data dictionary. Values returned in dbsize are
| Oracle Column Type | Value |
| CHAR, VARCHAR2, RAW | length of the column in the table |
| NUMBER | 22 (the internal length) |
| DATE | 7 (the internal length) |
| LONG, LONG RAW | 0 |
| ROWID | (system dependent) |
| Functions returning dataype 1 (such as TO_CHAR()) | same as the dsize parameter |
The USER function in a select-list always returns the datatype code 1.
cbuf Receives the name of the select-list item, that is, the name of the column or wording of the expression. The program must allocate a string long enough to receive the item name.
cbufl Contains the length in bytes of cbuf. This parameter must be set before calling odescr(). If cbufl is not specified (that is, passed as 0), then the select-list item name is not returned. The name is truncated if it is longer than cbufl.
On return from odescr(), cbufl contains the length of the returned string in bytes.
dsize Receives the maximum display size of the select-list item if the select-list item is returned as a character string. The dsize parameter is especially useful when functions, such as SUBSTR or TO_CHAR, are used to modify the representation of a column.
prec Returns the precision of numeric select-list items. Precision is the total number of digits of a number. See "Internal Datatypes" for additional information about precision and scale.
Pass this parameter as zero if you do not require the precision value.
scale A pointer to a short that returns the scale of numeric select-list items. Pass this parameter as zero if you do not require the scale value.
For Version 6 of the RDBMS, odescr() returns the correct scale and precision of fixed-point numbers and returns precision and scale of zero for floating-point, as shown below:
| SQL Datatype | Precision | Scale |
| NUMBER(P) | P | 0 |
| NUMBER(P,S) | P | S |
| NUMBER | 0 | 0 |
| FLOAT(N) | 0 | 0 |
nullok A pointer to a short that returns zero if null values are not permitted for the column, and non-zero if nulls are permitted.
Pass this parameter as zero if you do require the null status of the select-list item.
See Also
obindps(), obndra(), obndrn(), obndrv(), odefin(), odefinps(), oparse().
odessp
Purposeodessp() is used to describe the parameters of a PL/SQL procedure or function stored in an Oracle database.
Syntaxodessp(Lda_Def *lda, text *objnam, size_t onlen, ub1 *rsv1, size_t rsv1ln, ub1 *rsv2, size_t rsv2ln, ub2 *ovrld, ub2 *pos, ub2 *level, text **argnm, ub2 *arnlen, ub2 *dtype, ub1 *defsup, ub1 *mode, ub4 *dtsiz, sb2 *prec, sb2 *scale, ub1 *radix, ub4 *spare, ub4 *arrsiz)
Comments
You call odessp() to get the properties of a stored procedure (or function) and the properties of its parameters. When you call odessp(), pass to it:
- A valid LDA for a connection that has execute privileges on the procedure.
- The name of the procedure, optionally including the package name. The package body does not have to exist, as long as the procedure is specified in the package.
- The total length of the procedure name, or -1 if it is null terminated.
odessp() returns the same information for a parameter of type cursor variable as for a regular cursor.
Your OCI program must allocate the arrays for all parameters of odessp(), and you must pass a parameter (arrsiz) that indicates the size of the arrays (or the size of the smallest array if they are not equal). The arrsiz parameter returns the number of elements of each array that was returned by odessp().
odessp() returns a non-zero value if an error occurred. The error number is in the return code field of the LDA. The following errors can be returned there:
-20000
The object named in the objnam parameter is a package, not a procedure or function.
-20001
The procedure or function named in objnam does not exist in the named package.
-20002
A database link was specified in objnam, either explicitly or by means of a synonym.
ORA-0xxxx
An Oracle code, usually indicating a syntax error in the procedure specification in objnam.
When odessp() returns successfully, the OUT array parameters contain the descriptive information about the procedure or function parameters, and the return type for a function. As an example, consider a package EMP_RECS in the SCOTT schema. The package contains two stored procedures and a stored function, all named GET_SAL_INFO. Here is the package specification:
create or replace package EMP_RECS as
procedure get_sal_info (
name in emp.ename%type,
salary out emp.sal%type);
procedure get_sal_info (
ID_num in emp.empno%type,
salary out emp.sal%type);
function get_sal_info (
name in emp.ename%type) return emp.sal%type;
end EMP_RECS;
A code fragment to describe these procedures and functions follows:
#include <stdio.h> #include <ocidfn.h> #include <ocidem.h> #define ASIZE 50
Lda_Def lda; Cda_Def cda; ub1 hda[256]; text *objnam = (text *) "scott.emp_recs.get_sal_info"; ub2 ovrld[ASIZE]; ub2 pos[ASIZE]; ub2 level[ASIZE]; text argnm[ASIZE][30]; ub2 arnlen[ASIZE]; ub2 dtype[ASIZE]; ub1 defsup[ASIZE]; ub1 mode[ASIZE]; ub4 dtsize[ASIZE]; sb2 prec[ASIZE]; sb2 scale[ASIZE]; ub1 radix[ASIZE]; ub4 spare[ASIZE]; ub4 arrsiz = (ub4) ASIZE;
main() {
int i, rv;
if (olog(&lda, hda, (text *) "scott", -1, (text *) "tiger", -1,
0, -1, OCI_LM_DEF)) {
printf("cannot connect as scott/n");
exit(1);
}
printf("connected/n");
/* call the describe function */
rv = odessp(&lda, objnam, -1, (ub1 *) 0, 0, (ub1 *) 0, 0,
ovrld, pos, level, argnm, arnlen, dtype,
defsup, mode, dtsize, prec, scale, radix,
spare, &arrsiz);
if (rv != 0)
{
printf("error in odessp %d/n", lda.rc);
}
/* print out the returned values */
printf("/nArrsiz = %ld/n", arrsiz);
if (arrsiz > ASIZE)
arrsiz = ASIZE;
printf("ProcName O'load Level Pos Datatype");
printf(" Mode Dtsize Prec Scale Radix/n");
printf("----------------------------------");
printf("-----------------------------/n");
for (i = 0; i < arrsiz; i++)
{
printf("%8.8s %6d %5d %3d %8d %4d %6d %4d %5d %5d/n",
argnm[i], ovrld[i], level[i], pos[i],
dtype[i], mode[i], dtsize[i], prec[i], scale[i],
radix[i]);
}
exit(0);
}
When this call to odessp() completes, the return parameter arrays are filled in as shown in Table 4 - 1. The arrsiz parameter returns 6, as there were a total of 5 parameters and one function return type described.
| ARRAY ELEMENT | ||||||
| PARAMETER | 0 | 1 | 2 | 3 | 4 | 5 |
| ovrld | 1 | 1 | 2 | 2 | 3 | 3 |
| pos | 1 | 2 | 1 | 2 | 0 | 1 |
| level | 1 | 1 | 1 | 1 | 1 | 1 |
| argnm | name | salary | ID_num | salary | NULL | name |
| arnlen | 4 | 6 | 6 | 6 | 0 | 4 |
| dtype | 1 | 2 | 2 | 2 | 2 | 1 |
| defsup | 0 | 0 | 0 | 0 | 0 | 0 |
| mode | 0 | 1 | 0 | 1 | 1 | 0 |
| dtsize | 10 | 22 | 22 | 22 | 22 | 10 |
| prec | 7 | 4 | 7 | 7 | ||
| scale | 2 | 0 | 2 | 2 | ||
| radix | 10 | 10 | 10 | 10 | ||
| spare (1) | n/a | n/a | n/a | n/a | n/a | n/a |
| Note 1: Reserved by Oracle for future use. | ||||||
Parameters
| Parameter Name | Type | Mode |
| lda | Lda_Def * | IN/OUT |
| objnam | text * | IN |
| onlen | size_t | IN |
| rsv1 | ub1 * | IN |
| rsv1ln | size_t | IN |
| rsv2 | ub1 * | IN |
| rsv2ln | size_t | IN |
| ovrld | ub2 * | OUT |
| pos | ub2 * | OUT |
| level | ub2 * | OUT |
| argnm | text ** | OUT |
| arnlen | ub2 * | OUT |
| dtype | ub2 * | OUT |
| defsup | ub1 * | OUT |
| mode | ub1 * | OUT |
| dtsiz | ub4 * | OUT |
| prec | sb2 * | OUT |
| scale | sb2 * | OUT |
| radix | ub1 * | OUT |
| spare | ub4 * | OUT |
| arrsiz | ub4 * | IN/OUT |
objnam The name of the procedure or function, including optional schema and package name. Quoted names are accepted. Synonyms are also accepted and are translated. Multi-byte characters can be used. The string can be null terminated. If it is not, the actual length in bytes must be passed in the onlen parameter.
onlen The length in bytes of the objnam parameter. If objnam is a null-terminated string, pass onlen as -1;. Otherwise, pass the exact length.
rsv1 Reserved by Oracle for future use.
rsv1ln Reserved by Oracle for future use.
rsv2 Reserved by Oracle for future use.
rsv2ln Reserved by Oracle for future use.
ovrld An array indicating whether the procedure is overloaded. If the procedure (or function) is not overloaded, 0 is returned. Overloaded procedures return 1...n for n overloadings of the name.
pos An array returning the parameter positions in the parameter list of the procedure. The first, or left-most, parameter in the list is position 1. When pos returns a 0, this indicates that a function return type is being described.
level For scalar parameters, level returns 0. For a record parameter, 0 is returned for the record itself, then for each parameter in the record the parameter's level in the record is indicated, starting from 1, in successive elements of the returned value of level.
For array parameters, 0 is returned for the array itself. The next element in the return array is at level 1 and describes the element type of the array.
For example, for a procedure that contains three scalar parameters, an array of ten elements, and one record containing three scalar parameters at the same level, you need to pass odessp() arrays with a minimum dimension of nine: three elements for the scalars, two for the array, and four for the record parameter.
argnm A pointer to an array of strings that returns the name of each parameter in the procedure or function. The strings are not null terminated. Each string in the array must be exactly 30 characters long.
arnlen The length in bytes of each corresponding parameter name in argnm.
dtype The Oracle datatype code for each parameter. See the PL/SQL User's Guide and Reference for a list of the PL/SQL datatypes. Numeric types, such as FLOAT, INTEGER, and REAL return a code of 2. VARCHAR2 returns 1. CHAR returns 96. Other datatype codes are shown in Table 3 - 5.
Note: A dtype value of 0 indicates that the procedure being described has no parameters.
defsup This parameter indicates whether the corresponding parameter has a default value. Zero returned indicates no default. One indicates that a default value was supplied in the procedure or function specification.
mode This parameter indicates the mode of the corresponding parameter. Zero indicates an IN parameter, one an OUT parameter, and two an IN/OUT parameter.
dtsiz The size of the datatype in bytes. Character datatypes return the size of the parameter. For example, the EMP table contains a column ENAME. If a parameter in a procedure is of the type EMP.ENAME%TYPE, the value 10 is returned for this parameter, because that is the length of the ENAME column in a single-byte character set.
For number types, 22 is returned. See the description of the dbsize parameter under odescr() for more information.
prec This parameter indicates the precision of the corresponding parameter if the parameter is numeric; otherwise, it returns zero.
scale This parameter indicates the scale of the corresponding parameter if the parameter is numeric.
radix This parameter indicates the radix of the corresponding parameter if it is numeric.
spare Reserved by Oracle for future use.
arrsiz When you call odessp(), pass the length of the arrays of the OUT parameters. If the arrays are not of equal length, you must pass the length of the shortest array. When odessp() returns, arrsiz returns the number of array elements filled in.
See Also
oerhms
Purposeoerhms() returns the text of an Oracle error message, given the error code rcode.
Syntaxoerhms(Lda_Def *lda, sb2 rcode, text *buf, sword bufsiz);
Comments
When you call oerhms(), pass the address of the LDA for the active connection as the first parameter. This is required to retrieve error messages that are correct for the database version being used on that connection.
The oerhms() function does not return zero when it completes successfully. It returns the number of characters in buf. The error message text in buf is null terminated.
When using oerhms() to return error messages from PL/SQL blocks (where the error code is between 6550 and 6599), be sure to allocate a large buf, because several messages can be returned. The maximum length of an Oracle error message is 512 characters.
For more information about the causes of Oracle errors and possible solutions, see the Oracle7 Server Messages manual.
The following example shows how to obtain an error message from a specific Oracle instance:
Lda_Def lda[2]; /* two separate connections in effect */
Cda_Def cda;
sword n_chars;
text msgbuf[512];
...
/* when an error occurs on the second connection */
n_chars = oerhms(&lda[1], cda.rc, msgbuf, (int) sizeof(msgbuf));
Parameters
| Parameter Name | Type | Mode |
| lda | Lda_Def * | IN/OUT |
| rcode | sb2 | IN |
| buf | text * | OUT |
| bufsiz | sword | IN |
rcode The LDA or CDA return code containing an Oracle error number.
buf A pointer to a buffer that receives the error message text. The message text is null terminated.
bufsiz The size of the buffer in bytes. The maximum size of the buffer is essentially unlimited. However, values larger than 1000 bytes are not normally needed.
See Also
oexec
Purposeoexec() executes the SQL statement associated with a cursor.
Syntaxoexec(Cda_Def *cursor);
Comments
Before calling oexec(), you must call oparse() to parse the SQL statement, and this call must complete successfully. If the SQL statement contains placeholders for bind variables, you must call obndrv(), obindps(), obndra() or obndrn() to bind each placeholder to the address of a program variable before calling oexec().
For queries, after oexec() is called, the program must explicitly request rows of the result set using ofen() or ofetch().
For UPDATE, DELETE, and INSERT statements, oexec() executes the entire SQL statement and sets the return code field and the rows processed count field in the CDA. Note that an UPDATE or DELETE that does not affect any rows (no rows match the WHERE clause) returns success in the return code field and zero in the rows processed count field.
Note:
If cursor is a cursor variable that has been OPENed FOR in a PL/SQL block, then oexec() returns an error unless oparse() has been called for cursor with another SQL statement or PL/SQL block.
DML statements (e.g., UPDATE, INSERT) are executed when a call is made to oexec(), oexn() or oexfet(). DDL statements (e.g., CREATE TABLE, REVOKE) are executed on the parse if you have linked in non-deferred mode or if you have liked with the deferred option and the defflg parameter of oparse() is zero. If you have linked in deferred mode and the defflg parameter is non-zero, you must call oexn() or oexec() to execute the statement.
Oracle recommends that you use the deferred parse capability whenever possible. This results in increased performance, especially in a networked environment. Note, however, that errors in the SQL statement that would be detected when oparse() is called in non-deferred mode are not detected until the first non-deferred call is made (usually an execute or describe call).
Note: It is possible to use the oexn() function in place of oexec() by binding scalar variables, not arrays, and setting the count parameter to 1. For queries, use oexfet() in preference to oexec() followed by ofen().
See the description of the ofetch() routine for an example that shows how to use oexec().
Parameter
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
See Also
obindps(), obndra(), obndrn(), obndrv(), oexfet(), oexn(), oparse().
oexfet
Purposeoexfet() executes the SQL statement associated with a cursor, then fetches one or more rows. oexfet() can also perform a cancel of the cursor (the same as an ocan() call).
Syntaxoexfet(Cda_Def *cursor, ub4 nrows, sword cancel, sword exact);
Comments
Before calling oexfet(), the OCI program must first call oparse() to parse the SQL statement, call obndra(), obndrn(), obndrv() or obindps() (if necessary) to bind input variables, then call odefin() or odefinps() to define output variables.
If the OCI program was linked using the deferred mode link option, the bind and define steps are deferred until oexfet() is called. If oparse() was called with the deferred parse flag (defflg) parameter non-zero, the parse step is also delayed until oexfet() is called. This means that your program can complete the processing of a SQL statement using a minimum of message round-trips between the client running the OCI program and the database server.
If you call oexfet() for a DML statement that is not a query, Oracle issues the error
ORA-01002: fetch out of sequence
and the execute operation fails.
Note: Using the deferred parse, bind, and define capabilities when processing a SQL statement requires more memory on the client system than the non-deferred sequence. So, you gain execution speed at the cost of some additional space.
When running against an Oracle7 database, where the SQL statement was parsed using oparse() with the lngflg parameter set to 1 or 2, a character string that is too large for its associated buffer is truncated. The column return code (rcode) is set to the error
ORA-01406: fetched column value was truncated
and the indicator parameter is set to the original length of the item. However, the oexfet() call does not return an error indication. If a null is encountered for a select-list item, the associated column return code (rcode) for that column is set to the error
ORA-01405: fetched column value is NULL
and the indicator parameter is set to -1. The oexfet() call does not return an error.
However, if no indicator parameter is defined and the program is running against an Oracle7 database, oexfet() does return an ORA-01405 error. It is always an error if a null is selected and no indicator parameter is defined, even if column return codes and return lengths are defined.
oexfet() both executes the statement and fetches the row or rows that satisfy the query. If you need to fetch additional rows after oexfet() completes, use the ofen() function. The following example shows how you can use deferred parse, bind, and define operations together with oexfet() to process a SQL statement:
Cda_Def cda;
Lda_Def lda;
text *sql_statement =
"SELECT ename, sal FROM emp WHERE deptno = :1";
float salaries[12000];
text names[12000][20];
sb2 sal_ind[12000], name_ind[12000]; char* dept_number_stg[10];
sword dept_number;
...
/* after connecting to Oracle ... */
oopen(&cda, &lda, 0, -1, -1, 0, -1);
oparse(&cda, sql_statement, -1, 1, 1); /* deferred parse*/
printf("Enter department number: ");
gets(dept_number_stg);
dept_number = (sword) atoi(dept_number_stg);
...
obndrn(&cda, 1, &dept_number, (int) sizeof (int), 3, -1,
0, 0, -1, -1);
odefin(&cda, 2, salaries, (int) sizeof (float),
4, -1, sal_ind, 0, -1, -1); /* datatype FLOAT is 4 */
odefin(&cda, 1, names, 20, 1, -1, name_ind, 0, -1, -1);
/* retrieve 12000 or fewer salaries */
oexfet(&cda, 12000, 0, 0); /* cancel and exact not set */
....
The number of rows that were fetched is returned in the rows processed count field of the CDA.
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
| nrows | ub4 | IN |
| cancel | sword | IN |
| exact | sword | IN |
nrows The number of rows to fetch. If nrows is greater than 1, you must define arrays to receive select-list values, as well as any indicator variables. See the description of odefin() for more information.
If nrows is greater than the number of rows that satisfy the query, the rows processed count field in the CDA is set to the number of rows returned, and Oracle returns the error
ORA-01403: no data found
Note: That the data is actually fetched.
cancel If this parameter is non-zero when oexfet() is called, the cursor is canceled after the fetch completes. This has exactly the effect of issuing an ocan() call, but does not require the additional call overhead.
exact If this parameter is non-zero when oexfet() is called, oexfet() returns an error if the number of rows that satisfy the query is not exactly the same as the number specified in the nrows parameter. Nevertheless, the rows are returned.
If the number of rows returned by the query is less than the number specified in the nrows parameter, Oracle returns the error
ORA-01403: no data found
If the number of rows returned by the query is greater than the number specified in the nrows parameter, Oracle returns the error
ORA-01422: Exact fetch returns more than requested
number of rows
Note: If exact is non-zero, a cancel of the cursor is always performed, regardless of the setting of the cancel parameter.
See Also
obindps(), obndra(), obndrn(), obndrv(), odefin(), odefinps(), ofen(), oparse().
oexn
Purposeoexn() executes a SQL statement. Array variables can be used to input multiple rows of data in one call.
Syntaxoexn(Cda_Def *cursor, sword iters, sword rowoff);
Comments
oexn() is similar to the older oexec(), but it allows you to take advantage of the Oracle array interface. oexn() allows operations using arrays of bind variables. oexn() is generally much faster than successive calls to oexec(), especially in a networked client-server environment.
Note:
If cursor is a cursor variable that has been OPENed FOR in a PL/SQL block, then oexn() returns an error, unless oparse() has been called on cursor with another SQL statement or PL/SQL block.
Variables are bound to placeholders in the SQL statement using obndra(), obndrv(), obndrn() or obindps(). A pointer to the scalar or array is passed to the binding function. Data must be present in bind variables before you call oexn(). For example:
Cda_Def cursor;
text names[10][20]; /* 2-dimensional array of char */
sword emp_nos[10]; /* an array of 10 integers */
sb2 ind_params[10]; /* array of indicator parameters */
text *sql_stmt = "INSERT INTO emp(ename, empno) VALUES /
(:N, :E)";
...
/* parse the statement */
oparse(&cursor, sql_stmt, -1, 1, 1); /* deferred parse */
/* bind the arrays to the placeholders */
obndrv(&cursor, ":N", -1, names, 20, SQLT_CHR,
-1, ind_params, 0, -1, -1);
/* empno is non-null, so indicator parameters are not used */
obndrv(&cursor, ":E", -1, emp_nos, (int) sizeof(int),
SQLT_INT, -1, 0, 0, -1, -1);
/* fill in the data and indicator parameters, then
execute the statement, inserting the array values */
oexn(&cursor, 10, 0);
This example declares three arrays, one of ten integers, one of ten indicators, and one of ten 20-character strings. It also defines a SQL statement that inserts multiple rows into the database. After binding the arrays, the program must place data for the first INSERT in names[0] and emp_nos[0], for the second INSERT in names[1] and emp_nos[1], and so forth. (This step is not shown in the example.) Then oexn() is called to insert the data in the arrays into the EMP table.
The completion status of oexn() is indicated in the return code field of the CDA. The rows processed count in the CDA indicates the number of rows successfully processed. If the rows processed count is not equal to iters, the operation failed on array element rows processed count + 1.
You can continue to process the rest of the array even after a failure on one of the array elements as long as a rollback did not occur (obtained from the flags1 field in the CDA). You do this by using rowoff to start operations at an array element other than the first.
In the above example, if the rows processed count was 5 at completion of oexn(), then row six was rejected. In this event, to continue the operation at row seven, call oexn() again as follows:
oexn(&cursor, 10, 6);
Note: The maximum number of elements in an array is 32767.
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
| iters | sword | IN |
| rowoff | sword | IN |
iters The total size of the array of bind variables to be inserted. The size cannot be greater than 32767 items.
rowoff The zero-based offset within the bind variable array at which to begin operations. oexn() processes (iters - rowoff) array elements if no error occurs.
See Also
ofen
Purposeofen() fetches one or multiple rows into arrays of variables, taking advantage of the Oracle array interface.
Syntaxofen(Cda_Def *cursor, sword nrows);
Comments
ofen() is similar to ofetch(); however, ofen() can fetch multiple rows into an array of variables with a single call. A pointer to the array is bound to a select-list item in the SQL query statement using odefin().
When running against an Oracle7 database, where the SQL statement was parsed using oparse() with the lngflg parameter set to 1 or 2, a character string that is too large for its associated buffer is truncated, the column return code (rcode) is set to the error
ORA-01406: fetched column value was truncated
and the indicator parameter is set to the original length of the item. However, the ofen() call does not return an error indication. If a null is encountered for a select-list item, the associated column return code (rcode) for that column is set to the error
ORA-01405: fetched column value is NULL
and the indicator parameter is set to -1. The ofen() call does not return an error.
However, if no indicator parameter is defined and the program is running against an Oracle7 database, ofen() does return the 1405 error. It is always an error if a null is selected and no indicator parameter is defined, even if column return codes and return lengths are defined.
Even when fetching a single row, Oracle recommends that Oracle7 OCI programs use oexfet(), with the nrows parameter set to 1, instead of the combination of oexec() and ofen(). Use ofen() after oexfet() to fetch additional rows when you do not know in advance the exact number of rows that a query returns.
The following example is a complete OCI program that shows how ofen() can be used to extract multiple rows using the array interface.
#include <stdio.h>
#include <oratypes.h>
#include <ocidfn.h>
#include <ocidem.h>
#define MAX_NAME_LENGTH 30
Lda_Def lda;
ub1 hda[256];
Cda_Def cda;
main()
{
static sb2 ind_a[10];
static sword empno[10];
static text names[10][MAX_NAME_LENGTH];
ub2 rl[10], rc[10];
sword i, n, rows_done;
/* connect to Oracle */
if (olog(&lda, hda, "scott/tiger",
-1, 0, -1, 0, -1, 0, OCI_LM_DEF)) {
printf("cannot connect to Oracle as scott/tiger/n");
exit(1);
}
/* open one cursor */
if (oopen(&cda, &lda, 0, -1, -1, 0, -1)) {
printf("cannot open the cursor/n");
ologof(&lda);
exit(1);
}
/* parse a query */
if (oparse(&cda, "select ename, empno from emp", -1, 1, 2)) {
oci_error(&cda);
exit(1);
}
/* define the output variables */
if (odefin(&cda, 1, names, MAX_NAME_LENGTH, 5, -1,
ind_a, 0, -1, -1, rl, rc)) {
oci_error(&cda);
exit(1);
}
if (odefin(&cda, 2, empno, (int) sizeof (int), 3, -1,
0, 0, -1, -1, 0, 0)) {
oci_error(&cda);
exit(1);
}
/* execute the SQL statement */
if (oexec(&cda)) {
oci_error(&cda);
exit(1);
}
/* use ofen to fetch the rows, 10 at a time,
and then display the results */
for (rows_done = 0;;) {
if (ofen(&cda, 10))
if (cda.rc != 1403) {
oci_error(&cda); /* some error */
exit(1);
}
/* the rpc is cumulative, so find out how many
rows to display this time (always <= 10) */
n = cda.rpc - rows_done;
rows_done += n;
for (i = 0; i < n; i++) {
if (ind_a[i])
printf("%s ", "(null)");
else
printf("%s%*c", names[i],
MAX_NAME_LENGTH - rl[i], ' ');
printf("%10d/n", empno[i]);
}
if (cda.rc == 1403) break; /* no more rows */
}
printf("%d rows returned/n", cda.rpc);
if (oclose(&cda))
exit(1);
if (ologof(&lda))
exit(1);
exit(0);
}
oci_error(cda)
Cda_Def *cda;
{
static text msg[512];
sword len;
len = oerhms(&lda, cda->rc, msg, (int) sizeof (msg));
printf("/nOracle ERROR/n");
printf("%.*s/n", len, msg);
printf("Processing OCI function %s/n",
oci_func_tab[cda->rc]);
return 0;
}
The return code field of the CDA indicates the completion status of ofen(). The rows processed count field in the CDA indicates the cumulative number of rows successfully fetched. If the rows processed count increases by nrows, ofen() may be called again to get the next batch of rows. If the rows processed count does not increase by nrows, then an error, such as "no data found", has occurred.
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
| nrows | sword | IN |
nrows The size of the defined variable array on which to operate. The size cannot be greater than 32767 items. If the size is one, then ofen() acts effectively just like ofetch().
See Also
odefin(), odefinps(), oexfet(), ofetch(), oparse().
ofetch
Purposeofetch() returns rows of a query to the program, one row at a time.
Syntaxofetch(Cda_Def *cursor);
Comments
Each select-list item of the query is placed into a buffer identified by a previous odefin() call. When running against Oracle7, where the SQL statement was parsed using oparse() with the lngflg parameter set to 1 or 2, a character string that is too large for its associated buffer is truncated, the column return code (rcode) is set to the error
ORA-01406: fetched column value was truncated
and the indicator parameter is set to the original length of the item. However, the ofetch() call does not return an error indication. If a null is encountered for a select-list item, the associated column return code (rcode) for that column is set to the error
ORA-01405: fetched column value is NULL
and the indicator parameter is set to -1. The ofetch() call does not return an error.
However, if no indicator parameter is defined and the program is running against an Oracle7 database, ofetch() does return the 1405 error. It is always an error if a null is selected and no indicator parameter is defined, even if column return codes and return lengths are defined.
Even when fetching a single row, Oracle recommends that Oracle7 OCI programs use oexfet(), with the nrows parameter set to 1, instead of the combination of oexec() and ofetch().
The following example shows how you can obtain data from Oracle using ofetch() on a query statement. This example continues the one given in the description of the odefin() function earlier in this chapter. In that example, the select-list items in the SQL statement
SELECT ENAME, EMPNO, SAL FROM EMP WHERE
SAL > :MIN_SAL
were associated with output buffers, and the addresses of column return lengths and return codes were bound. The example continues:
int rv;
oexec(&cursor); /* execute the statement */
/* fetch each row of the query */
for (;;) {
if (rv = ofetch(&cursor)) /* break on row-level error */
break;
if (ret_codes[0] == 0)
printf("%*.*s/t", retl[0], retl[0], employee_name);
else if (ret_codes[0] == 1405)
printf("%*.*s/t", retl[0], retl[0], "Null");
else
break;
if (ret_codes[1] == 0)
printf("%d/t", employee_number);
/* process remaining items */
..
. printf("/n");
}
/* check rv for abnormal termination or just end-of-fetch */
if (rv != 1403)
errrpt(&cursor);
...
Each ofetch() call returns the next row from the set of rows that satisfies a query. After each ofetch() call, the rows processed count in the CDA is incremented.
You cannot refetch rows previously fetched except by re-executing the oexec() call and moving forward through the active set again. After the last row has been returned, the next fetch returns a "no data found" return code. When this happens, the rows processed count contains the total number of rows returned by the query.
Parameter
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
See Also
odefin(), odefinps(), odescr(), oexec(), oexfet(), ofen().
oflng
Purposeoflng() fetches a portion of a LONG or LONG RAW column.
Syntaxoflng(Cda_Def *cursor, sword pos, ub1 *buf, sb4 bufl, sword dtype, ub4 *retl, sb4 offset);
Comments
LONG and LONG RAW columns can hold up to 2 gigabytes of data. The oflng() function allows you to fetch up to 64K bytes, starting at any offset, from the LONG or LONG RAW column of the current row. There can be only one LONG or LONG RAW column in a table; however, a query that includes a join operation can include in its select list several LONG-type items. The pos parameter specifies the LONG-type column that the oflng() call uses.
Note: Although the datatype of bufl is sb4, oflng() can only retrieve up to 64K at a time. If an attempt is made to retrieve more than 64K, the returned data will not be complete. The use of sb4 in the interface is for future enhancements.
Before calling oflng() to retrieve the portion of the LONG-type column, you must do one or more fetches to position the cursor at the desired row.
oflng() is useful in cases where unstructured LONG or LONG RAW column data cannot be manipulated as a solid block.; for example, a voicemail application that uses sampled speech, stored as one byte per sample, at perhaps 10000 samples per second. If the voice message is to be played out using a buffered digital-to-analog converter, and the buffer takes 64 Kbyte samples at a time, you can use oflng() to extract the message in chunks of this size, sending them to the converter buffer. See the cdemo3.c program in Appendix A for an example that demonstrates this technique.
When calling oflng() to retrieve multiple segments from a LONG-type column, it is much more efficient to retrieve sequentially from low to high offsets, rather than from high to low, or randomly.
Note: With release 7.3, it may be possible to perform piecewise operations more efficiently using the new obindps(), odefinps(), ogetpi(), and osetpi() calls. See the section "Piecewise Insert, Update and Fetch" for more information.
The program fragment below shows how to retrieve 64 Kbytes, starting at offset 70000, from a LONG column. There are two columns in the table; the LONG data is in column two.
#define DB_SIZE 65536
#define FALSE 0
#define TRUE 1
ub1 *data_area;
sb4 offset;
sb2 da_indp, id_no;
ub4 ret_len;
Cda_Def cda;
...
data_area = (ub1 *) malloc(DB_SIZE);
...
oparse(&cda, "SELECT id_no, data FROM data_table
WHERE id_no = 100", -1, TRUE, 1);/* deferred parse */
/* define the first column - id_no, with no indicator parameter */
odefin(&cda, 1, &id_no, (int) sizeof (int), 3, -1, 0, 0, 0,
-1, 0, 0);
/* define the 2nd column - data, with indicator parameter */
odefin(&cda, 2, data_area, DB_SIZE, 1, -1, &da_indp,
0, 0, -1, 0, 0);
oexfet(&cda, 1, FALSE, FALSE); /* cursor is now at the row */
oflng(&cda, 2, data_area, DB_SIZE, 1, &ret_len, (sb4) 70000);
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
| pos | sword | IN |
| buf | ub1 * | OUT |
| bufl | sb4 | IN |
| dtype | sword | IN |
| retl | ub4 * | OUT |
| offset | sb4 | IN |
pos The index position of the LONG-type column in the row. The first column is position one. If the column at the index position is not a LONG type, a "column does not have LONG datatype" error is returned. If you do not know the position, you can use odescr() to index through the select-list. When a LONG datatype code (8 or 24) is returned in the dtype parameter, the value of the loop index variable (that started at 1) is the position of the LONG-type column.
buf A pointer to the buffer that receives the portion of the LONG-type column data.
bufl The length of buf in bytes.
dtype The datatype code corresponding to the type of buf. See the "External Datatypes" section for a list of the datatype codes.
retl The number of bytes returned. If more than 65535 bytes were requested and returned, the maximum value returned in this parameter is still 65535.
offset The zero-based offset of the first byte in the LONG-type column to be fetched.
See Also
odescr(), oexfet(), ofen(), ofetch().
ogetpi
Purposeogetpi() returns information about the next chunk of data to be processed as part of a piecewise insert, update or fetch.
Syntaxogetpi (Cda_Def *cursor, sb1 *piecep,dvoid **ctxpp, eword* iterp, eword *indexp);
Comments
ogetpi() is used (in conjunction with osetpi()) in an OCI application to determine whether more pieces exist to be either inserted, updated, or fetched as part of a piecewise operation.
Note: This function is only compatible with Oracle server release 7.3 or later. If a release 7.3 application attempts to use this function against a release 7.2 or earlier server, an error message is likely to be generated. At that point you must restart execution.
See the section "Piecewise Insert, Update and Fetch" in Chatper 2 for more information about piecewise operations and the ogetpi() call.
The following sample code demonstrates the use of ogetpi() in an OCI program which performs an piecewise insert. This code is provided for demonstration purposes only, and does not constitute a complete program.
For sample code demonstrating the use of ogetpi() for a piecewise fetch, see the description of the osetpi() routine later in this chapter.
This sample program performs a piecewise insert into a LONG RAW column in an Oracle table. The program is invoked with arguments specifying the name of the file to be inserted and the size of the piece to be used for the insert. It then inserts that file and its name into the database. Most of the data processing is done in the insert_file() routine.
/* The table FILES is used for this example: * * SQL> describe FILES * Name Null? Type * FILENAME NOT NULL VARCHAR2(255) * FILECONTENT LONG RAW */
... /* OCI #include statements */ #define DEFER_PARSE 1 /* oparse flags */ #define NATIVE 1 #define VERSION_7 2
#define OCI_MORE_INSERT_PIECES -3129
#define OCI_EXIT_FAILURE 1 /* exit flags */ #define OCI_EXIT_SUCCESS 0
void insert_file();
/* Usage : piecewise_insert filename piecesize */
main(argc, argv)
int argc;
char *argv[];
{
Lda_Def lda; /* login area */
ub1 hda[256]; /* host area */
Cda_Def cda; /* cursor area */
... /* log on to Oracle */
insert_file(&lda, &cda, argv[1], atol(argv[2]));
... /*log off of Oracle */
}
/* Function insert_file(): This function loads long raw data */
/* into a memory buffer from a source file and then */
/* inserts it piecewise into the database */
/* */
/* Note: If necessary, the context pointer could be used to */
/* point to a file being used in a piecewise operation.*/
/* It is not necesssay in this example program, so a */
/* dummy value is passed instead. */
void insert_file(lda, cda, filename, piecesize)
Lda_Def *lda;
Cda_Def *cda;
text *filename;
ub4 piecesize;
{
text *longbuf; /* buffer to hold long column on insert */ ub4 len_longbuf; /* length of longbuf */ ub2 col_rcode; /* Column return code */ text errmsg[2000]; int fd; char *context = "context pointer"; ub1 piece; ub4 iteration; ub4 plsqltable; ub1 cont = (ub1)1;
text *sqlstmt = (text *)
"INSERT INTO FILES (filename, filecontent) /
VALUES (:filename, :filecontent)";
if (oopen(cda, lda, (text *)0, -1, -1, (text *)0, -1))
exit(OCI_EXIT_FAILURE);
printf("/nOpening source file %s/n", filename);
if (!(fd = open((char *)filename, O_RDONLY)))
exit(1);
/* Allocate memory for storage of one piece */
len_longbuf = piecesize;
longbuf = (text *)malloc(len_longbuf);
if (longbuf == (text *)NULL)
exit(1);
if (oparse(cda, sqlstmt, (sb4)-1, 0, (ub4)VERSION_7))
exit(OCI_EXIT_FAILURE);
if (obndrv(cda, (text *)":filename", -1, filename, -1,
SQLT_STR, -1, (sb2 *)0, (ub1 *)0, -1, -1))
exit(OCI_EXIT_FAILURE);
if (obindps(cda, 0, (text *)":filecontent",
strlen(":filecontent"), (ub1 *)context, len_longbuf,
SQLT_LBI, (sword)0, (sb2 *)0,
(ub2 *)0, &col_rcode, 0, 0, 0, 0,
0, (ub4 *)0, (text *)0, 0, 0))
exit(OCI_EXIT_FAILURE);
while (cont)
{
oexec(cda);
switch (cda.rc)
{
case 0: /* operation is finished */
cont = 0;
break;
case OCI_MORE_INSERT_PIECES: /* ORA-03129 was returned */
if ((len_longbuf = read(fd, longbuf, len_longbuf)) == -1)
exit(OCI_EXIT_FAILURE);
ogetpi(cda, &piece, (dvoid **)&context, &iteration,
&plsqltable);
if (len_longbuf < piecesize) /* last piece? */
piece = OCI_LAST_PIECE;
osetpi(cda, piece, longbuf, &len_longbuf);
break;
default:
err_report(lda, cda);
exit(OCI_EXIT_FAILURE);
}
}
ocom(lda); /* Commit the insert */
if (close(fd)) /* close file */
exit(OCI_EXIT_FAILURE);
if (oclose(cda)) /* close cursor */
exit(OCI_EXIT_FAILURE);
}
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
| piecep | sb1 * | OUT |
| ctxpp | dvoid ** | OUT |
| iterp | eword * | OUT |
| indexp | eword * | OUT |
piecep Specifies whether the next piece to be fetched or inserted is the first piece, an intermediate piece or the last piece. Possible values are OCI_FIRST_PIECE (one) or OCI_NEXT_PIECE (two).
ctxpp A pointer to the user-defined context pointer, which is optionally passed as part of an obindps() or odefinps() call. This pointer is returned to the application during the ogetpi() call. If ctxpp is passed as NULL, the parameter is ignored. The application may already know which buffer it needs to pass in osetpi() at run time.
iterp Pointer to the current iteration. During an array insert it will tell you which row you are working with. Starts from 0.
indexp Pointer to the current index of an array mapped to a PL/SQL table, if an array is bound for an insert. The value of indexp varies between zero and the value set in the cursiz parameter of the obindps() call.
See Also
obindps(), odefinps(), osetpi().
olog
Purposeolog() establishes a connection between an OCI program and an Oracle database.
Syntaxolog(Lda_Def *lda, ub1 *hda, text *uid, sword uidl, text *pswd, sword pswdl, text *conn, sword connl, ub4 mode)
Comments
An OCI program can connect to one or more Oracle instances multiple times. Communication takes place using the LDA and the HDA defined within the program. It is the olog() function that connects the LDA to Oracle.
The HDA is a program-allocated data area associated with each olog() logon call. Its contents are entirely private to Oracle, but the HDA must be allocated by the OCI program. Each concurrent connection requires one LDA-HDA pair.
Note: The HDA must be initialized to all zeros (binary zeros, not the "0" character) before the call to olog(), or runtime errors will occur. In C, this means that the HDA must be declared as global or static, rather than as a local or automatic character array. See the sample code below for typical declarations and initializations for a call to olog().
The HDA has a size of 256 bytes on 32-bit systems, and 512 bytes on 64-bit systems. If memory permits, it is possible to allocate a 512-byte HDA on a 32-bit system to increase portability of aplications.
Refer to the section "Host Data Area" for more information about HDAs.
After the olog() call, the HDA and the LDA must remain at the same program address they occupied at the time olog() was called.
For example:
#include <stdlib.h>
/* global variable declarations */
Lda_Def lda[2]; /* establish two LDAs */
ub1 hda[2][512]; /* and two HDA's */
text *uid1 = "SCOTT/TIGER";
text *uid2 = "SYSTEM";
text *pwd = "MANAGER";
...
/* first connect as scott */
if (olog(&lda[0], &hda[0], uid1, -1, (text *) 0, -1,
(text *) 0, -1, OCI_LM_DEF))
{
error_handler(&lda[0]);
exit(EXIT_FAILURE);
}
...
/* and later as the system manager */
if (olog(&lda[1], &hda[1], uid2, -1, pwd, -1,
(text *) 0, -1, OCI_LM_DEF))
{
error_handler(&lda[1]);
exit(EXIT_FAILURE);
}
When an OCI program has issued an olog() call, a subsequent ologof() call using the same LDA commits all outstanding transactions for that connection. If a program fails to disconnect or terminates abnormally, then all outstanding transactions are rolled back.
The LDA return code field indicates the result of the olog() call. A zero return code indicates a successful connection.
The mode parameter specifies whether the connection is in blocking or non-blocking mode. For more information on connection modes, see "Non-Blocking Mode" . For a short example program, see the onbset() description .
You should also refer to the section on SQL*Net in your Oracle system-specific documentation for any particular notes or restrictions that apply to your operating system.
Parameters
| Parameter Name | Type | Mode |
| lda | Lda_Def * | IN/OUT |
| hda | ub1 * | OUT |
| uid | text * | IN |
| uidl | sword | IN |
| pswd | text * | IN |
| pswdl | sword | IN |
| conn | text * | IN |
| connl | sword | IN |
| mode | ub4 | IN |
hda A pointer to a host data area struct. See Chapter 2 for more information on host data areas.
uid Specifies a string containing the username, an optional password, and an optional host machine identifier. If you include the password as part of the uid parameter, put it immediately after the username and separate it from the username with a '/'. Put the host machine identifier after the username or the password, preceded by the '@' sign.
If the password is not included in this parameter, it must be in the pswd parameter. Examples of valid uid parameters are
name
name/password
name@service_name
name/password@service_name
The following example is not a correct example of a uid:
name@service_name/password
uidl The length of the string pointed to by uid. If the string pointed to by uid is null terminated, this parameter should be passed as -1.
pswd A pointer to a string containing the password. If the password is specified as part of the string pointed to by uid, this parameter should be passed as (text *) 0.
pswdl The length of the string pointed to by pswd. If the string pointed to by pswd is null terminated, this parameter should be passed as -1.
conn Specifies a string containing a SQL*Net V2 connect descriptor to connect to a database. If the connect string is passed in as part of the uid, this parameter should be passed as (text *) 0.
connl The length of the string pointed to by conn. If the string pointed to by conn is null terminated, this parameter can be passed as -1.
mode Specifies whether the connection is in blocking or non-blocking mode. Possible values are OCI_LM_DEF (for blocking) or OCI_LM_NBL (for non-blocking).
See Also
ologof
Purposeologof() disconnects an LDA from the Oracle program global area and frees all Oracle resources owned by the Oracle user process.
Syntaxologof(Lda_Def *lda);
Comments
A COMMIT is automatically issued on a successful ologof() call; all currently open cursors are closed. If a program logs off unsuccessfully or terminates abnormally, all outstanding transactions are rolled back.
If the program has multiple active connections, a separate ologof() must be performed for each active LDA.
If ologof() fails, the reason is indicated in the return code field of the LDA.
Parameter
| Parameter Name | Type | Mode |
| lda | Lda_Def * | IN/OUT |
See Also
onbclr
Purposeonbclr() places a database connection in blocking mode.
Syntaxonbclr(Lda_Def *lda);
Comments
If there is a pending call on a non-blocking connection and onbclr() is called, the pending call (onbclr()), when resumed, will block.
Parameters
| Parameter Name | Type | Mode |
| lda | Lda_Def * | IN |
See Also
onbset
Purposeonbset() places a database connection in non-blocking mode for all subsequent OCI calls on this connection.
Syntaxonbset(Lda_Def *lda);
Comments
onbset() will succeed only if the library is linked in deferred mode and if the network driver supports non-blocking operations.
Note: onbset() requires SQL*Net Release 2.1 or higher. It cannot be used with a single-task driver.
The following example code demonstrates the use of the non-blocking calls. Before running the OCI program, you must execute this SQL script.
set echo on; connect system/manager; drop user ocitest cascade; create user ocitest identified by ocitest; grant connect,resource to ocitest; connect ocitest/ocitest;
create table oci21tab (col1 varchar2(30));
insert into oci21tab values ('A');
insert into oci21tab values ('AB');
insert into oci21tab values ('ABC');
insert into oci21tab values ('ABCD');
insert into oci21tab values ('ABCDE');
insert into oci21tab values ('ABCDEF');
insert into oci21tab values ('ABCDEFG');
insert into oci21tab values ('ABCDEFGH');
insert into oci21tab values ('ABCDEFGHI');
insert into oci21tab values ('ABCDEFGHIJ');
insert into oci21tab values ('ABCDEFGHIJK');
insert into oci21tab values ('ABCDEFGHIJKL');
commit;
This program performs a long-running, hardcoded insert and demonstrates the use of non-blocking calls. This example is included online as oci21.c and oci21.sql. See your Oracle system-specific documentation for the location of these files.
#include <stdio.h> #include <stdlib.h> #include <ctype.h> #include <string.h>
#include <oratypes.h> /* LDA and CDA struct declarations */ #include <ocidfn.h> #ifdef __STDC__ #include <ociapr.h> #else #include <ocikpr.h> #endif /* demo constants and structs */ #include <ocidem.h>
/* oparse flags */ #define DEFER_PARSE 1 #define NATIVE 1 #define VERSION_7 2
/* exit flags */ #define OCI_EXIT_FAILURE 1 #define OCI_EXIT_SUCCESS 0
#define BLOCKED -3123 /* ORA-03123 */ #define SUCCESS 0
Lda_Def lda; /* login area */ ub1 hda[HDA_SIZE]; /* host area */ Cda_Def cda; /* cursor area */
/* Function prototypes */ void log_on (); void log_off (); void setup(); void err_report(); void insert_data(); void do_exit();
/* SQL statement used in this program */
text *sqlstmt = (text *)"INSERT INTO oci21tab (col1)/
SELECT a.col1 /
FROM oci21tab a, oci21tab b";
main(argc, argv)
eword argc;
text **argv;
{
log_on(); /* logon to Oracle database */
setup(); /* prepare sql statement */
insert_data();
log_off(); /* logoff Oracle database */
do_exit(OCI_EXIT_SUCCESS);
}
/* Function: setup
* Description: This routine does the necessary setup
* to execute the SQL statement. Specifically, it does
* the open, parse, bind and define phases as needed. */
void setup()
{
if (onbset(&lda)) /* make the connection non-blocking */
{
err_report((Cda_Def *)&lda);
do_exit(OCI_EXIT_FAILURE);
}
if (onbtst(&lda)) /* verify that it is non-blocking */
{
printf("connection is still blocking!!!/n");
do_exit(OCI_EXIT_FAILURE);
}
if (oopen(&cda, &lda, (text *) 0, -1, -1,
(text *) 0, -1)) /* open */
{
err_report(&cda);
do_exit(OCI_EXIT_FAILURE);
}
if (oparse(&cda, sqlstmt, (sb4) -1, DEFER_PARSE,
(ub4) VERSION_7))
{
err_report(&cda);
do_exit(OCI_EXIT_FAILURE);
}
}
/* Function: insert_data
* Description: This routine inserts the data into the table */
void insert_data()
{
ub1 done = 0;
/* number of times statement blocked */
static ub1 blocked_cnt = 0;
while (!done)
{
switch(oexec(&cda))
{
case BLOCKED: /* will come through here multiple
* times, but print msg once */
blocked_cnt++;
break;
case SUCCESS:
done = 1;
break;
default:
err_report(&cda);
/* get out of application */
do_exit(OCI_EXIT_FAILURE);
}
}
printf("/n Execute call blocked %ld times/n", blocked_cnt);
if (onbclr(&lda)) /* clear the non-blocking status of the
* connection */
{
err_report((Cda_Def *)&lda);
do_exit(OCI_EXIT_FAILURE);
}
}
/* Function: err_report
* Description: This routine prints out the most recent
* OCI error */
void err_report(cursor)
Cda_Def *cursor;
{
sword n;
text msg[512]; /* message buffer to hold error text */
if (cursor->fc > 0)
printf("/n-- ORACLE error when processing /
OCI function %s /n/n",
oci_func_tab[cursor->fc]);
else
printf("/n-- ORACLE error/n");
n = (sword)oerhms(&lda, cursor->rc, msg, (sword) sizeof msg);
printf("%s/n", msg);
}
/* Function: do_exit
* Description: This routine exits with a status */
void do_exit(status)
eword status;
{
if (status == OCI_EXIT_FAILURE)
printf("/n Exiting with FAILURE status %d/n", status);
else
printf("/n Exiting with SUCCESS status %d/n", status);
exit(status);
}
/* Function: log_on
* Description: This routine logs onto the database as
* OCITEST/OCITEST. */
void log_on()
{
if (olog(&lda, hda, (text *)"OCITEST", -1, (text *)"OCITEST",
-1, (text*)"inst1_nonblock" , -1, OCI_LM_DEF))
{
err_report((Cda_Def *)&lda);
exit(OCI_EXIT_FAILURE);
}
printf("/n Connected to Oracle as ocitest/n");
}
/* Function: log_off
* Description: This routine closes out any cursors and logs
* off the database */
void log_off()
{
if (oclose(&cda)) /* close cursor */
{
printf("Error closing cursor 1./n");
do_exit(OCI_EXIT_FAILURE);
}
if (ologof(&lda)) /* log off the database */
{
printf("Error on disconnect./n");
do_exit(OCI_EXIT_FAILURE);
}
}
Parameters
| Parameter Name | Type | Mode |
| lda | Lda_Def * | IN/OUT |
See Also
onbtst
Purposeonbtst() tests whether a database connection is in non-blocking mode.
Syntaxonbtst(Lda_Def *lda);
Comments
If the connection is in the non-blocking mode, onbtst() returns 0. Otherwise, it returns ORA-03128 in the return code field.
Note: If the connection is in blocking mode, the user may call onbset() to place the channel in non-blocking mode, if allowed by the network driver.
Parameters
| Parameter Name | Type | Mode |
| lda | Lda_Def * | IN/OUT |
See Also
oopen
Purposeoopen() opens the specified cursor.
Syntaxoopen(Cda_Def *cursor, Lda_Def *lda, <text *dbn>, <sword dbnl>, <sword arsize>, <text *uid>, <sword uidl>);
Comments
oopen() associates a cursor data area in the program with a data area in the Oracle Server. Oracle uses these data areas to maintain state information about the processing of a SQL statement. Status concerning error and warning conditions, and other information, such as function codes, is returned to the CDA in your program, as Oracle processes the SQL statement.
A program can have many cursors active at the same time.
The oparse() function parses a SQL statement and associates it with a cursor. In the OCI functions, SQL statements are always referenced using a cursor as the handle.
It is possible to issue an oopen() call on a cursor that is already open. This has no effect on the cursor, but it does affect the value in the Oracle OPEN_CURSORS counter. Repeatedly reopening an open cursor may result in an ORA-01000 error ('maximum open cursors exceeded'). Refer to the Oracle7 Server Messages manual for information about what to do if this happens.
The return code field of the CDA indicates the result of the oopen(). A return code value of zero indicates a successful oopen() call.
See the description of the obndra() function earlier in this chapter for an example program demonstrating the use of oopen().
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | OUT |
| lda | Lda_Def * | IN/OUT |
| dbn | text * | IN |
| dbnl | sword | IN |
| arsize | sword | IN |
| uid | text * | IN |
| uidl | sword | IN |
lda A pointer to the LDA specified in the olog() call that was used to make this connection to Oracle.
dbn This parameter is included only for Oracle Version 2 compatibility. It should be passed as 0 in later versions.
dbnl This parameter is included only for Oracle Version 2 compatibility. It should be passed as -1 in later versions.
arsize Oracle7 does not use the areasize parameter. The data areas in the Oracle Server used by cursors are automatically resized as required.
uid A pointer to a character string containing the userid and the password. The password must be separated from the userid by a '/'.
uidl The length of the string pointed to by uid. If uid points to a null-terminated string, this parameter can be omitted.
See Also
oopt
Purposeoopt() sets rollback options for non-fatal Oracle errors involving multi-row INSERT and UPDATE SQL statements. It also sets wait options in cases where requested resources are not available; for example, whether to wait for locks.
Syntaxoopt(Cda_Def *cursor, sword rbopt, sword waitopt);
Comments
The rbopt parameter is not supported in Oracle Server Version 6 or later.
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
| rbopt | sword | IN |
| waitopt | sword | IN |
rbopt The action to be taken when a non-fatal Oracle error occurs. If this option is set to zero, all errors, even non-fatal errors, cause the current transaction to be rolled back. If this option is set to 2, only the failing row will be rolled back during a non-fatal row-level error. This is the default setting.
waitopt Specifies whether to wait for resources or return with an error if they are currently not available. If this option is set to zero, the program waits indefinitely if resources are not available. This is the default action. If this option is set to 4, the program will receive an error return code whenever a resource is requested but is unavailable. Use of waitopt set to 4 can cause many error return codes while waiting for internal resources that are locked for short durations. The only resource errors received are for resources requested by the calling process.
See Also
oparse
Purposeoparse() parses a SQL statement or a PL/SQL block and associates it with a cursor. The parse can optionally be deferred.
Syntaxoparse(Cda_Def *cursor, text *sqlstm, [sb4 sqll], sword defflg, ub4 lngflg);
Comments
oparse() passes the SQL statement to Oracle for parsing. If the defflg parameter is non-zero, the parse is deferred until the statement is executed, or until odescr() is called to describe the statement. Once the parse is performed, the parsed representation of the SQL statement is stored in the Oracle shared SQL cache. Subsequent OCI calls reference the SQL statement using the cursor name.
An open cursor can be reused by subsequent oparse() calls within a program, or the program can define multiple concurrent cursors when it is necessary to maintain multiple active SQL statements.
Note: When oparse() is called with the defflg parameter set to a non-zero value, you cannot receive most error indications until the parse is actually performed. The parse is performed at the first call to odescr(), oexec(), oexn(), or oexfet(). However, the SQL statement string is scanned on the client system, and some errors, such as "missing double quote in identifier", can be returned immediately.
The statement can be any valid SQL statement or PL/SQL anonymous block. Oracle parses the statement and selects an optimal access path to perform the requested function.
Data Definition Language statements are executed on the parse if you have linked in non-deferred mode or if you have liked with the deferred option and the defflg parameter of oparse() is zero. If you have linked in deferred mode and the defflg parameter is non-zero, you must call oexn() or oexec() to execute the statement.
Oracle recommends that you use the deferred parse capability whenever possible. This results in increased performance, especially in a networked environment. Note, however, that errors in the SQL statement that would be detected when oparse() is called in non-deferred mode are not detected until the first non-deferred call is made (usually an execute or describe call).
The example below opens a cursor and parses a SQL statement. The oparse() call associates the SQL statement with the cursor.
Lda_Def lda;
Cda_Def cursor;
text *sql_stmt =
"DELETE FROM emp WHERE empno = :Employee_number";
...
oopen(&cursor, &lda, (text *)0, -1, -1, (text *)0, -1);
oparse(&cursor, sql_stmt, -1, 1, 2);
SQL syntax error codes are returned in the CDA's return code field. If the statement cannot be parsed, the parse error offset field indicates the location of the error in the SQL statement text. See the section "Cursor Data Area" for a list of the information fields available in the CDA after an oparse() call.
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN/OUT |
| sqlstm | text * | IN |
| sqll | sb4 | IN |
| defflg | sword | IN |
| lngflg | ub4 | IN |
sqlstm A pointer to a string containing the SQL statement.
sqll Specifies the length of the SQL statement. If the SQL statement string pointed to by sqlstm is null terminated, this parameter can be omitted.
defflg If non-zero and the application was linked in deferred mode, the parse of the SQL statement is deferred until an odescr(), oexec(), oexn(), or oexfet() call is made.
Note: Bind and define operations are also deferred until the execute or describe step if the program was linked using the deferred mode link option.
Oracle recommends that you use the deferred parse capability whenever possible. This results in increased performance, especially in a networked environment. Note, however, that errors in the SQL statement that would be detected when oparse() is called in non-deferred mode are not detected until the first non-deferred call is made (usually an execute or describe call).
lngflg The lngflg parameter determines how Oracle handles the SQL statement or PL/SQL anonymous block. To ensure strict ANSI conformance, Oracle7 defines several datatypes and operations in a slightly different way than Oracle Version 6. The table below shows the differences between Version 6 and Oracle7 that are affected by the lngflg parameter.
| Behavior | V6 | V7 |
| CHAR columns are fixed length (including those created by a CREATE TABLE statement). | NO | YES |
| An error is issued if an attempt is made to fetch a null into an output variable that has no associated indicator variable. | NO | YES |
| An error is issued if a fetched value is truncated and there is no associated indicator variable. | YES | NO |
| Describe (odescr()) returns internal datatype 1 for fixed-length strings | YES | NO |
| Describe (odescr()) returns datatype 96 for fixed-length strings. | n/a | YES |
| 0 | Specifies version 6 behavior (the database you are connected to can be any version 6 or later database). |
| 1 | Specifies the normal behavior for the database version to which the program is connected (either version 6 or Oracle7). |
| 2 | Specifies Oracle7 behavior. If you use this value for the parameter, and you are not connected to an Oracle7 database, Oracle issues the error ORA-01011: Cannot use this language type when talking to V6 database |
odescr(), oexec(), oexfet(), oexn(), oopen().
opinit
Purposeopinit() initializes the OCI process environment. This includes specifying whether the application is single- or multi-threaded.
Syntaxopinit (ub4 mode);
Comments
The mode parameter of the opinit() call indicates whether the application making the call is running a single- or multi-threaded environment. See the section "Thread Safety" for more information about using thread-safe calls in an OCI program.
If mode is set to OCI_EV_DEF or a call to opinit() is skipped altogether, for backward compatibility a single-threaded environment is assumed. Using thread safety adds a very small amount of overhead to the program, and this can be avoided by running in single-threaded mode.
Note: Even when running a single-threaded application it is advisable to make the call to opinit(), with mode set to OCI_EV_DEF, rather than skipping it. In addition to setting the environment, the call to opinit() provides documentation that the application is not thread-safe.
If mode is set to OCI_EV_TSF, then the OCI application can make OCI calls from multiple threads of execution within a single program.
The following examples demonstrate how the same task might be accomplished in a multi-connection environment and a single-connection, multi-threaded environment. The task is to process a series of bank account transactions.
This code is provided for demonstration purposes only, and does not constitute a complete program.
The first example demonstrates how the transactions could be processed in a multi-threaded environment with a single connection. It is assumed that a user's program could call its own functions for the OS-specific thread package. Function calls could include calls to thread-safe packages from DCE or Posix for thread and semaphore management.
This program creates one session and multiple threads. Each thread executes zero or more transactions. The transactions are specified in a transient structure called "records." The transactions consist of moving a specified amount of money from one account to another. The example assumes that accounts 10001 through 10007 are set up in the database.
/* The table ACCOUNTS is used for this example: * * SQL> describe ACCOUNTS * Name Null? Type * ACCOUNT NUMBER(36) * BALANCE NUMBER(36,2) */
#include <stdio.h> #include <stdlib.h> #include <string.h> #include <pthread.h> /* dce thread/semaphore management package */ #include <ocidfn.h> #include <ocikpr.h>
void do_transaction(); void get_transaction();
#define THREADS 3 #define DEFERRED 1 #define ORACLE7_BEHAVIOR 2
struct parameters
{ Lda_Def * lda;
int thread_id;
};
typedef struct parameters parameters;
struct record_log
{ char action;
unsigned int from_account;
unsigned int to_account;
float amount;
};
typedef struct record_log record_log;
record_log records[]= { { 'M', 10001, 10002, 12.50 },
{ 'M', 10001, 10003, 25.00 },
{ 'M', 10001, 10003, 123.00 },
{ 'M', 10001, 10003, 125.00 },
{ 'M', 10002, 10006, 12.23 },
{ 'M', 10007, 10008, 225.23 },
{ 'M', 10002, 10008, 0.70 },
{ 'M', 10001, 10003, 11.30 },
{ 'M', 10003, 10002, 47.50 },
{ 'M', 10002, 10006, 125.00 },
{ 'M', 10007, 10008, 225.00 }};
static unsigned int trx_nr=0; pthread_mutex_t mutex;
ub1 hda[256];
main()
{
Lda_Def lda;
pthread_t thread[THREADS];
parameters params[THREADS];
int i; pthread_addr_t status; opinit(OCI_EV_TSF);
/* log on to Oracle w/a single session */
if(olog(lda, hda, (text *) "SCOTT/TIGER", -1, (text *) 0, -1,
(text *) 0, -1, OCI_LM_DEF))
exit(OCI_EXIT_FAILURE);
/*Create mutex for transaction retrieval */
if (pthread_mutex_init(&mutex,pthread_mutexattr_default))
{
printf("Can't initialize mutex/n");
exit(OCI_EXIT_FAILURE);
}
/*Spawn threads*/
for(i=0;i<THREADS;i++)
{
params[i].lda=&lda;
params[i].thread_id=i;
printf("Thread %d... ",i);
if (pthread_create(&thread[i],pthread_attr_default,
(pthread_startroutine_t)do_transaction,
(pthread_addr_t) ¶ms[i]))
printf("Cant create thread %d/n",i);
else
printf("Created/n");
}
/* Logoff session.... */
for(i=0;i<THREADS;i++)
{ /*wait for thread to end */
printf("Thread %d ....",i);
if (pthread_join(thread[i],&status))
printf("Error when wating for thread % to terminate/n", i);
else
printf("stopped/n");
printf("Detach thread...");
if (pthread_detach(&thread[i]))
printf("Error detaching thread! /n");
else
printf("Detached!/n");
}
printf("Stop Session....");
ologof(&lda);
/*Destroys mutex for transaction retrieval */
if (pthread_mutex_destroy(&mutex))
{
printf("Can't destroy mutex/n");
exit(1);
}
}
/* Function do_transaction(): This functions executes one */
/* transaction out of the record array. The record */
/* array is 'managed' by get_transaction(). */
void do_transaction(params)
parameters *params;
{
Lda_Def * lda=params->lda;
Cda_Def cda;
record_log *trx;
text * pls_trans=(text *) /
"BEGIN /
UPDATE ACCOUNTS /
SET BALANCE=BALANCE+:amount /
WHERE ACCOUNT=:to_account; /
UPDATE ACCOUNTS /
SET BALANCE=BALANCE-:amount /
WHERE ACCOUNT=:from_account; /
COMMIT; /
END;";
/* NOTE use of mutex for OCI calls */
if (pthread_mutex_lock(&mutex))
printf("Can't lock mutex/n");
if (oopen(&cda,lda, 0,-1,0,(text *)0,-1))
err_report(&cda);
if (oparse(&cda, pls_trans , -1, DEFERRED, ORACLE7_BEHAVIOR))
err_report(&cda);
if (pthread_mutex_unlock(&mutex))
printf("Can't unlock mutex/n");
/* Done all transactions ? */
while (trx_nr < (sizeof(records)/sizeof(record_log)))
{
get_transaction(&trx);
printf("Thread %d executing transaction/n",params->thread_id);
switch(trx->action)
{
case 'M':
/* NOTE use of mutex for OCI calls */
if (pthread_mutex_lock(&mutex))
printf("Can't lock mutex/n");
obndrv(&cda, ":amount", -1, (ub1 *)
&trx->amount, sizeof(float), SQLT_FLT, -1,
(sb2 *) 0, (text *) 0, -1, -1);
obndrv(&cda, ":to_account", -1, (ub1 *)
&trx->to_account, sizeof(int), SQLT_INT, -1,
(sb2 *) 0, (text *) 0, -1, -1);
obndrv(&cda,":from_account", -1, (ub1 *)
&trx->from_account, sizeof(int), SQLT_INT, -1, (sb2 *) 0, (text *) 0, -1, -1);
oexec(&cda);
if (pthread_mutex_unlock(&mutex))
printf("Can't unlock mutex/n");
break;
default: break;
}
/* Give other threads a chance.. */
pthread_yield();
}
/* NOTE use of mutex for OCI calls */
if (pthread_mutex_lock(&mutex))
printf("Can't lock mutex/n");
if (oclose(&cda))
err_report(&cda);
if (pthread_mutex_unlock(&mutex))
printf("Can't unlock mutex/n");
}
/* Function get_transaction: This routine returns the next */
/* transaction to process */
void get_transaction(trx)
record_log ** trx;
{
if (pthread_mutex_lock(&mutex))
printf("Can't lock mutex/n");
*trx=&records[trx_nr];
trx_nr++;
if (pthread_mutex_unlock(&mutex))
printf("Can't unlock mutex/n");
}
The second example demonstrates how the transactions could be processed in an environment with multiple connections.
This program creates as many sessions as there are threads. Each thread executes zero or more transactions. The transactions are specified in a transient structure called "records." The transactions consist of moving a specified amount of money from one account to another. The example assumes that accounts 10001 through 10007 are set up in the database.
/* The table ACCOUNTS is used for this example: * * SQL> describe ACCOUNTS * Name Null? Type * ACCOUNT NUMBER(36) * BALANCE NUMBER(36,2) */
#include <stdio.h> #include <stdlib.h> #include <string.h> #include <pthread.h> /* thread & semaphore management package */ #include <ocidfn.h> #include <ocikpr.h>
void do_transaction(); void get_transaction();
#define CONNINFO "scott/tiger" #define THREADS 3 #define DEFERRED 1 #define ORACLE7_BEHAVIOR 2
struct parameters
{ Lda_Def * lda;
int thread_id;
};
typedef struct parameters parameters;
struct record_log
{ char action;
unsigned int from_account;
unsigned int to_account;
float amount;
};
typedef struct record_log record_log;
record_log records[]= { { 'M', 10001, 10002, 12.50 },
{ 'M', 10001, 10003, 25.00 },
{ 'M', 10001, 10003, 123.00 },
{ 'M', 10001, 10003, 125.00 },
{ 'M', 10002, 10006, 12.23 },
{ 'M', 10007, 10008, 225.23 },
{ 'M', 10002, 10008, 0.70 },
{ 'M', 10001, 10003, 11.30 },
{ 'M', 10003, 10002, 47.50 },
{ 'M', 10002, 10006, 125.00 },
{ 'M', 10007, 10008, 225.00 }};
static unsigned int trx_nr=0; pthread_mutex_t mutex;
ub1 hda[THREADS][256];
main()
{
Lda_Def lda[THREADS];
pthread_t thread[THREADS];
parameters params[THREADS];
int i; pthread_addr_t status; opinit(OCI_EV_TSF);
/* log on to Oracle w/multiple connections */
for(i=0;i<THREADS;i++)
{
if(olog(lda[i], hda[i], (text *) "SCOTT/TIGER", -1, (text *) 0,
-1, (text *) 0, -1, OCI_LM_DEF))
exit(OCI_EXIT_FAILURE);
}
/* create mutex for transaction retrieval */
if (pthread_mutex_init(&mutex,pthread_mutexattr_default))
{
printf("Can't initialize mutex/n");
exit(1);
}
/* spawn threads */
for(i=0;i<THREADS;i++)
{
params[i].lda=&lda[i];
params[i].thread_id=i;
printf("Thread %d... ",i);
if (pthread_create(&thread[i],pthread_attr_default,
(pthread_startroutine_t)do_transaction,
(pthread_addr_t) ¶ms[i]))
printf("Cant create thread %d/n",i);
else
printf("Created/n");
}
... /* logoff sessions....*/
... /*destroy mutex for transaction retrieval */ }
/* Function do_transaction(): executes one transaction out of */
/* the records array. The records array is */
/* 'managed' by the get_transaction function. */
void do_transaction(params)
parameters *params;
{
Lda_Def * lda=params->lda;
Cda_Def cda;
record_log *trx;
text * pls_trans=(text *) /
"BEGIN /
UPDATE ACCOUNTS /
SET BALANCE=BALANCE+:amount /
WHERE ACCOUNT=:to_account; /
UPDATE ACCOUNTS /
SET BALANCE=BALANCE-:amount /
WHERE ACCOUNT=:from_account; /
COMMIT; /
END;";
/* NOTE lack of mutex for OCI calls */
oopen(&cda,lda, 0,-1,0,(text *)0,-1);
ioparse(&cda, pls_trans , -1, DEFERRED, ORACLE7_BEHAVIOR);
/* Done all transactions ? */
while (trx_nr < (sizeof(records)/sizeof(record_log)))
{
get_transaction(&trx);
printf("Thread %d executing transaction/n",params->thread_id);
switch(trx->action)
{
case 'M':
/* NOTE lack of mutex for OCI calls */
obndrv(&cda, ":amount", -1, (ub1 *) &trx->amount,
sizeof(float), SQLT_FLT, -1, (sb2 *) 0,
(text *) 0, -1, -1)
obndrv(&cda, ":to_account", -1, (ub1 *) &trx->to_account, sizeof(int), SQLT_INT, -1, (sb2 *) 0,
(text *) 0, -1, -1)
obndrv(&cda,":from_account", -1,
(ub1 *) &trx->from_account, sizeof(int),
SQLT_INT, -1, (sb2 *) 0, (text *) 0, -1, -1)
oexec(&cda)
break;
default: break;
}
}
/* NOTE lack of mutex for OCI calls */
if (oclose(&cda))
err_report(&cda);
}
/* Function get_transaction(): gets next transaction to process */
void get_transaction(trx)
record_log ** trx;
{
if (pthread_mutex_lock(&mutex))
printf("Can't lock mutex/n");
*trx=&records[trx_nr];
trx_nr++;
if (pthread_mutex_unlock(&mutex))
printf("Can't unlock mutex/n");
}
Parameter
| Parameter Name | Type | Mode |
| mode | ub4 | IN |
See Also
orol
Purposeorol() rolls back the current transaction.
Syntaxorol(Lda_Def *lda);
Comments
The current transaction is defined as the set of SQL statements executed since the olog() call or the last ocom() or orol() call. If orol() fails, the reason is indicated in the return code field of the LDA.
Parameter
| Parameter Name | Type | Mode |
| lda | Lda_Def * | IN/OUT |
See Also
osetpi
Purposeosetpi() sets information about the next chunk of data to be processed as part of a piecewise insert, update or fetch.
Syntaxosetpi (Cda_Def *cursor, sb1 piece, dvoid *bufp, ub4 *lenp);
Comments
An OCI application uses osetpi() to set the information about the next piecewise insert, update, or fetch. The bufp parameter is a pointer to either the buffer containing the next piece to be inserted, or to the buffer where the next fetched piece will be stored.
Note: This function is only compatible with Oracle server release 7.3 or later. If a release 7.3 application attempts to use this function against a release 7.2 or earlier server, an error message is likely to be generated. At that point you must restart execution.
See the section "Piecewise Insert, Update and Fetch" in Chatper 2 for more information about piecewise operations and the osetpi() call.
The following sample code demonstrates the use of osetpi() to perform a piecewise fetch. This code is provided for demonstration purposes only, and does not represent a complete program.
For sample code demonstrating the use of osetpi() for a piecewise insert, see the description of the osetpi() routine later in this chapter.
This sample program performs a piecewise fetch from a LONG RAW column in an Oracle table. The program extracts the data from FILECONTENT and reconstitutes it in a file called FILENAME. The program is invoked with arguments specifying the file name and the piece size to be used for the fetch. Most of the data processing is done in the fetch_file() routine.
/* The table FILES is used for this example: * * SQL> describe FILES * Name Null? Type * FILENAME NOT NULL VARCHAR2(255) * FILECONTENT LONG RAW */
... /* OCI #include statements */ #define DEFER_PARSE 1 /* oparse flags */ #define NATIVE 1 #define VERSION_7 2 #define OCI_MORE_FETCH_PIECES -3130 #define MAX_COL_SIZE 2147483648 /* 2 gigabytes */
#define OCI_EXIT_FAILURE 1 /* exit flags */ #define OCI_EXIT_SUCCESS 0
void fetch_file();
Lda_Def lda; /* login area */ ub1 hda[256]; /* host area */ Cda_Def cda; /* cursor area */
/* Usage: piecewise_fetch filename piecesize */
main(argc, argv)
int argc;
char *argv[];
{
... /* log on to Oracle */
fetch_file(argv[1], atol(argv[2]));
... /* log off of Oracle */
}
/* Function fetch_file(): retrieves contents of 'filename' */
/* from database and (re)stores it back to disk */
/* */
/* Note: If necessary, the context pointer could be used to */
/* point to a file being used in a piecewise operation.*/
/* It is not necesssay in this example program, so a */
/* dummy value is passed instead. */
void fetch_file(filename, piecesize)
text *filename;
ub4 piecesize;
{
text *longbuf; /* buffer to hold long column on insert */ ub4 len_longbuf; /* length of buffer */ text errmsg[2000]; int fd; char *context = "context pointer"; ub1 piece; eword iteration = 0; eword plsqltable; ub1 cont = 1; ub2 col_rcode; ub2 col_len;
text *sqlstmt = (text *) "SELECT filecontent /
FROM FILES /
WHERE filename=:filename";
if (oopen(&cda, &lda, (text *)0, -1, -1, (text *)0, -1))
exit(OCI_EXIT_FAILURE);
if (!(fd = open((char *)filename, O_WRONLY | O_CREAT, 511)))
exit(OCI_EXIT_FAILURE);
/* Allocate memory for storage of one piece */
len_longbuf = piecesize;
longbuf = (text *)malloc(len_longbuf);
if (longbuf == (text *)NULL)
exit(OCI_EXIT_FAILURE);
if (oparse(&cda, sqlstmt, (sb4)-1, 0, (ub4)VERSION_7))
exit(OCI_EXIT_FAILURE);
if (obndrv(&cda, (text *)":filename", -1, filename, -1,
SQLT_STR, -1, (sb2 *)0, (text *)0, -1, -1))
exit(OCI_EXIT_FAILURE);
if (odefinps(&cda, 0, 1, (ub1 *)&context, (ub4) MAX_COL_SIZE,
SQLT_LBI, 0, (sb2 *)0, (text *)0, 0, 0,
(ub2 *)&col_len, (ub2 *)&col_rcode, 0, 0, 0, 0))
exit(OCI_EXIT_FAILURE);
if (oexec(&cda)) /* execute SQL statement */
exit(OCI_EXIT_FAILURE);
while (cont) /* while pieces remain to be fetched */
{
ofetch(&cda)
switch (cda.rc) /* do fetch & switch on return value */
{
case 0: /* write last piece to buffer */
if (len_longbuf != write(fd, longbuf, len_longbuf))
exit(OCI_EXIT_FAILURE);
cont = 0;
break;
case OCI_MORE_FETCH_PIECES: /* ORA-03130 was returned */
ogetpi(&cda, &piece, &context, &iteration, &plsqltable);
if (piece!=OCI_FIRST_PIECE) /* can't write on first fetch */
if (len_longbuf != write(fd, longbuf, len_longbuf))
exit(OCI_EXIT_FAILURE);
osetpi(&cda, piece, longbuf, &len_longbuf);
break;
default:
exit(OCI_EXIT_FAILURE); /* other value indicates error */
}
}
if (close(fd)) /* close file */
exit(OCI_EXIT_FAILURE);
if (oclose(&cda)) /* close cursor */
exit(OCI_EXIT_FAILURE);
}
Parameters
| Parameter Name | Type | Mode |
| cursor | Cda_Def * | IN |
| piece | sb1 | IN |
| bufp | dvoid * | IN |
| lenp | ub4 * | IN/OUT |
piece Specifies the piece being provided or fetched. Possible values are OCI_FIRST_PIECE (one), OCI_NEXT_PIECE (two) and OCI_LAST_PIECE (three). Relevant when the buffer is being set after error ORA-03129 was returned by a call to oexec().
bufp A pointer to a data buffer. If osetpi() is called as part of a piecewise insert, this pointer must point to the next piece of the data to be transmitted. If osetpi() is called as part of a piecewise fetch, this is a pointer to a buffer to hold the next piece to be retrieved.
lenp A pointer to the length in bytes of the current piece. If a piece is provided, the value is unchanged on return. If the buffer is filled up and part of the data is truncated, lenp is modified to reflect the length of the piece in the buffer.
See Also
obindps(), odefinps(), ogetpi().
sqlld2
PurposeThe sqlld2() routine is provided for OCI programs that operate as application servers in an X/Open distributed transaction processing environment. sqlld2() fills in fields in the LDA parameter according to the connection information passed to it.
Syntaxdvoid sqlld2(Lda_Def *lda, text *cname, sb4 *cnlen);
Comments
OCI programs that operate in conjunction with a transaction manager do not manage their own connections. However, all OCI programs require a valid LDA. You use sqlld2() to obtain the LDA.
sqlld2() fills in the LDA using the connection name passed in the cname parameter. The cname parameter must match the db_name alias parameter of the XA info string of the xa_open() call. If this parameter is a null pointer or if the cnlen parameter is set to zero, an LDA for the default connection is returned. Your program must allocate the LDA, then pass the pointer to it in the lda parameter.
sqlld2() does not return a value directly. If you call sqlld2() and there is no valid connection, the error
ORA-01012: not logged on
is returned in the return code field of the lda parameter.
sqlld2() must be invoked whenever there is an active XA transaction. This means it must be invoked after xa_open() and xa_start(), and before xa_end(). Otherwise an ORA-01012 error will result.
sqlld2() is part of SQLLIB, the Oracle Precompiler library. SQLLIB must be linked into all programs that call sqlld2(). See your Oracle system-specific documentation for information about linking SQLLIB.
The example code on the following page demonstrates how you can use sqlld2() to obtain a valid LDA for a specific connection.
#include "ocidfn.h"
#include "ociapr.h"
/* define two LDAs */
Lda_Def lda1;
Lda_Def lda2;
sb4 clen1 = -1L;
sb4 clen2 = -1L;
...
/* get the first LDA for OCI use */
sqlld2(&lda1, "NYdbname", &clen1);
if (lda1.rc != 0)
handle_error();
/* get the second LDA for OCI use */
sqlld2(&lda2, "LAdbname", &clen2);
if (lda2.rc != 0)
handle_error();
Parameters
| Parameter Name | Type | Mode |
| lda | Lda_Def * | OUT |
| cname | text * | IN |
| cnlen | sb4 * | IN |
cname A pointer to the name of the database connection. If the name is a null-terminated string, you can pass the cnlen parameter as -1L. If the name is not null terminated, pass the exact length of the string in cnlen.
If the name consists of all blanks, sqlld2() returns the LDA for the default connection.
The cname parameter must match the db_name alias parameter of the XA info string of the xa_open() call.
cnlen A pointer to the length of the cname parameter. You can pass this parameter as -1 if cname is a null-terminated string. If cnlen is passed as zero, sqlld2() returns the LDA for the default connection, regardless of the contents of cname.
sqllda
PurposeThe sqllda() routine is for programs that mix precompiler code and OCI calls. A pointer to an LDA is passed to sqllda(). On return from sqllda(), the required fields in the LDA are filled in.
Syntaxdvoid sqllda(Lda_Def *lda);
Comments
If your program contains both precompiler statements and calls to OCI functions, you cannot use olog(), (or orlon() or olon()) to log on to Oracle. You must use the embedded SQL command
EXEC SQL CONNECT ...
to log on. However, many OCI functions require a valid LDA. The sqllda() function obtains the LDA. sqllda() is part of SQLLIB, the precompiler library.
sqllda() fills in the LDA using the connect information from the most recently executed SQL statement. So, the safest practice is to call sqllda() immediately after doing the EXEC SQL CONNECT ... statement.
sqllda() does not return a value directly. If you call sqllda() and there is no valid connection, the error
ORA-01012: not logged on
is returned in the return code field of the lda parameter.
The example below demonstrates how you can do multiple remote connections in a mixed Precompiler-OCI program. See Chapter 3 in the Programmer's Guide to the Oracle Precompilers for additional information about multiple remote connections.
EXEC SQL BEGIN DECLARE SECTION;
text user_id[20], passwd[20], db_string1[20], db_string2[20];
text dbn1[20], dbn2[20];
EXEC SQL END DECLARE SECTION;
...
/* host program declarations */
Lda_Def lda1; /* declare two LDAs */
Lda_Def lda2;
dvoid sqllda(Lda_Def *); /* declare the sqllda function */
...
/* set up strings */
strcpy(user_id, "scott");
strcpy(passwd, "tiger");
strcpy(db_string1, "newyork");
strcpy(db_string2, "losangeles"); strcpy(dbn1, "NY"); strcpy(dbn2, "LA");
...
/* do the connections */
EXEC SQL CONNECT :user_id IDENTIFIED BY :passwd
AT :dbn1 USING :db_string1;
/* get the first LDA for OCI use */
sqllda(&lda1);
EXEC SQL CONNECT :user_id IDENTIFIED BY :passwd
AT :dbn2 USING :db_string2;
/* get the second LDA for OCI use */
sqllda(&lda2);
Parameter
| Parameter Name | Type | Mode |
| lda | Lda_Def * | OUT |
  Prev Next |  Copyright © 1996 Oracle Corporation. All Rights Reserved. | Library | Product | Contents | Index |
283
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
