http://download.oracle.com/docs/cd/A57673_01/DOC/api/doc/OCI73/ch4a.htm
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://writeblog.csdn.net/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)
The following two sections discuss special considerations to remember when passing parameters to the OCI functions.
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
Purpose
obindps() 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().
Unlike older OCI calls, obindps() does not accept -1 for any optional or unused parameters. When it is necessary to pass a value to these parameters NULL or 0 should be used instead. The only exception to this rule is that a -1 length is acceptable for sqlvl if sqlvar is null-terminated.
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
Purpose
obndra() 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
Purpose
obndrn() 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
Purpose
obreak() 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
Purpose
ocan() 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
Purpose
oclose() 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
9058
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
