| Programmer's Guide to the Oracle7 Server Call Interface |  Library |
 Product |
 Contents |
 Index |
| Programmer's Guide to the Oracle7 Server Call Interface | Library |
Product |
Contents |
Index |
Purpose
What the routine does.
Syntax
The routine call with its parameter list.
Comments
A detailed description of the routine, including examples.
Parameters
A detailed description of each parameter.
See Also
Other routines that affect or are used with this routine.
Be sure to read the introductory section, "Calling OCI Routines" ![[*]](jump.gif)
. It contains important information about data structures, datatypes, parameter passing conventions, and other things you need to know in calling COBOL OCI routines.
The following declaration of the LDA and CDA is VMS specific:
DATA DIVISION.
WORKING-STORAGE SECTION.
01 LDA.
02 LDA-V2RC PIC S9(4) COMP.
02 FILLER PIC X(10).
02 LDA-RC PIC S9(4) COMP.
02 FILLER PIC X(50).
01 CURSOR-1.
02 C-V2RC PIC S9(4) COMP.
02 C-TYPE PIC S9(4) COMP.
02 C-ROWS PIC S9(9) COMP.
02 C-OFFS PIC S9(4) COMP.
02 C-FNC PIC X.
02 FILLER PIC X.
02 C-RC PIC S9(4) COMP.
02 FILLER PIC X(50).
01 CURSOR-2.
02 C-V2RC PIC S9(4) COMP.
...
* declare remaining cursor data areas
The LDA and CDA are always 64 bytes. However, the size of fields in these areas are system dependent. See the Oracle system-specific documentation for the sizes on your system.
In the parameter descriptions and code examples in this chapter, the CDA is listed as CURSOR and is as defined in CURSOR-1 above. Also, the LDA is listed as LDA.
These parameters must always be passed by reference (the address of the area is passed). This is the default parameter passing mechanism for COBOL compilers.
Warning: Even if your COBOL compiler supports call by value, do not pass integer parameters by value.
On some systems, binary integers must be declared as COMP-5, rather than COMP.
Character strings are a special type of parameter. A length parameter must be specified for character strings. Length parameters for strings are PIC S9(9) variables specifying the size in bytes of the character string.
There are several ways that you can indicate to Oracle that an optional parameter is being omitted. If your COBOL compiler permits missing parameters using a keyword such as "OMITTED", you can use this convention to indicate that you are not using the optional parameter.
Many COBOL compilers permit trailing parameters to be omitted. If the optional parameters that you want to omit are the last parameters in the list and your compiler permits this, simply do not pass them.
If the parameter is of the type PIC S9(4) or PIC S9(9) and is not an address parameter, you can declare a variable for the parameter, move a -1 value to it, and pass it normally. If the parameter is an address parameter, you cannot indicate that it is being omitted by passing a -1 as the value in the parameter. For an address parameter, you can indicate that it is not being used only if your compiler supports a mechanism for omitting parameters or for passing parameters by value. In the latter case, you pass a 0 by value.
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. See the descriptions of individual calls for more details about specific parameters.
In summary, if your compiler does not support omitted parameters or passing parameters by value, you cannot omit an optional address parameter. Make sure that when you pass an optional parameter it does not contain values that can affect the SQL statement. For example, the INDP parameter in the OBNDRA, OBNDRN, or OBNDRV routine is optional. If you are binding a DML statement, make sure that the value in the INDP parameter is zero when the statement is executed. Otherwise, the statement might fail.
In the code examples in this chapter, all optional parameters are passed. Mechanisms that are compiler specific, such as passing by value or using the OMITTED keyword, are not used.
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. See the descriptions of individual calls for more details about specific parameters.
See page 5 - 21 for the description of the OBNDRN routine for examples of how to pass omitted optional and unused parameters.
IN
A parameter that passes data to Oracle.
OUT
A parameter that receives data from Oracle on this call or a subsequent call.
IN/OUT
A parameter that passes data on the call, and that receives data on the return from this call or a subsequent call.
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.
SyntaxCALL "OBINDPS" USING CURSOR, OPCODE, SQLVAR, [SQLVL], PVCTX, PROGVL, [SCALE], [INDP], [ALENP], [RCODEP], PV-SKIP, IND-SKIP, ALEN-SKIP, RC-SKIP, MAXSIZ], [CURSIZ], [FMT], [FMTL], [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:
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.
For a C language example which uses OBINDPS to perform an insert from an array of structures, see the description of the obindps() call .
Parameters
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
| OPCODE | PIC S9(2) COMP | IN |
| SQLVAR | PIC X(n) | IN |
| SQLVL | PIC S9(9) COMP | IN |
| PVCTX | PIC S9(2) COMP | IN |
| PROGVL | PIC S9(9) COMP | IN |
| FTYPE | PIC S9(9) COMP | IN |
| SCALE | PIC S9(9) COMP | IN |
| INDP | PIC S9(4) COMP | IN/OUT |
| ALENP | PIC S9(4) COMP | IN |
| RCODEP | PIC S9(4) COMP | OUT |
| PV-SKIP | PIC S9(9) COMP | IN |
| IND-SKIP | PIC S9(9) COMP | IN |
| ALEN-SKIP | PIC S9(9) COMP | IN |
| RC-SKIP | PIC S9(9) COMP | IN |
| MAXSIZ | PIC S9(9) COMP | IN |
| CURSIZ | PIC S9(9) COMP | IN/OUT |
| FMT | PIC X(6) | IN |
| FMTL | PIC S9(9) COMP | IN |
| FMTT | PIC S9(9) COMP | IN |
Standard scalar and array binds are those binds which were previously possible using other OCI bind calls (OBNDRA, OBNDRN, and OBNDRV).
CURSOR 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 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.
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 Specifies the number of digits to the right of the decimal point for fields where FTYPE is 7 (PACKED DECIMAL). SCALE is ignored for all other types.
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 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 the size of one 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).
rRC-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 zero 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 zero for SQL scalar or array binds.
FMT A character string that contains the format specifier for a packed decimal variable. This optional parameter is only used when the type of the defined variable is PACKED DECIMAL (PIC S9(N)V9(N) COMP-3). The specifier has the form "mm.[+/-]nn", where "mm" is the total number of digits, from 1 to 38, and "nn" is the number of decimal places, or scale. For example, "09.+02" would be the format specifier for an Oracle column of the internal type NUMBER(9,2). The plus or minus sign is required. If "+" is used, "nn" is the number of digits to the right of the decimal place. If "-" is specified, then "nn" is the power of ten by which the number is multiplied before it is placed in the output buffer.
If your compiler does not allow you to omit optional parameters, then pass the length (FMTL) parameter with a value of zero to indicate that there is no format specifier.
FMTL The length of the format conversion specifier string. If zero, then FMT and FMTT are unused parameters.
FMTT Specifies the format type of the conversion format string. The only value allowed is 7 (PACKED DECIMAL)
See Also
OBNDRA, OBNDRN, OBNDRV, ODEFINPS, OGETPI, OSETPI.
OBNDRA binds the address of a program variable or array to a placeholder in a SQL statement or PL/SQL block.
SyntaxCALL "OBNDRA" USING CURSOR, SQLVAR, SQLVL, PROGV, PROGVL, FTYPE, [SCALE], [INDP], [ALEN], [ARCODE], [MAXSIZ], [CURSIZ], [FMT], [FMTL], [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 OBNDRA routine has a parameter, ALEN, that allows you to change the size of the bound variable without actually rebinding the variable.
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 OBINDPS routine instead of OBNDRA.
When you bind arrays in your program to PL/SQL tables, you must use OBNDRA, since this routine provides additional parameters that allow you to control the maximum size of the table, and to retrieve the current table size after the block has been executed.
Call OBNDRA after you call OPARSE to parse the statement containing the PL/SQL block and before calling OEXEC to execute it.
Once you have bound a program variable, you can change the value in the variable (PROGV) and the length of the variable (PROGVL) and re-execute the block without rebinding.
However, if you need to change the type of the variable, you must reparse and rebind before re-executing.
The following short but complete example program shows how you can use OBNDRA to bind tables in a COBOL program to tables in a PL/SQL block.
IDENTIFICATION DIVISION.
PROGRAM-ID. OBNDRA-TEST.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 LDA.
03 LDA-V2RC PIC S9(4) COMP.
03 FILLER PIC X(10).
03 LDA-RC PIC S9(4) COMP.
03 FILLER PIC X(50).
01 CURSOR.
03 CURS-V2RC PIC S9(4) COMP.
03 CURS-TYPE PIC S9(4) COMP.
03 CURS-ROWS-PROCESSED PIC S9(9) COMP.
03 CURS-OFFS PIC S9(4) COMP.
03 CURS-FNC PIC X.
03 FILLER PIC X.
03 CURS-RC PIC S9(4) COMP.
03 FILLER PIC X(50).
01 HOST-DATA-AREA PIC X(512).
01 ERR-RC PIC S9(4) COMP.
01 MSGBUF PIC X(500).
01 MSGBUF-L PIC S9(9) COMP.
01 PART-UPDATE.
03 DESCRIP OCCURS 3 TIMES PIC X(20).
03 PARTNOS OCCURS 3 TIMES PIC S9(9) COMP.
01 BND-VARS.
03 DESCRIP-ALEN OCCURS 3 TIMES PIC S9(4) COMP.
03 DESCRIP-RC OCCURS 3 TIMES PIC S9(4) COMP.
03 PARTNO-ALEN OCCURS 3 TIMES PIC S9(4) COMP.
03 PARTNO-RC OCCURS 3 TIMES PIC S9(4) COMP.
01 DESCRIP-CS PIC S9(9) VALUE 3 COMP.
01 DESCRIP-MAX PIC S9(9) VALUE 3 COMP.
01 DESCRIP-PH PIC X(5) VALUE ":DESC".
01 DESCRIP-PH-L PIC S9(9) VALUE 5 COMP.
01 DESCRIP-L PIC S9(9) VALUE 20 COMP.
01 PARTNO-CS PIC S9(9) VALUE 3 COMP.
01 PARTNO-MAX PIC S9(9) VALUE 3 COMP.
01 PARTNO-PH PIC X(6) VALUE ":PARTS".
01 PARTNO-PH-L PIC S9(9) VALUE 6 COMP.
01 USERNAME PIC X(11) VALUE "SCOTT".
01 USERNAME-L PIC S9(9) VALUE 5 COMP.
01 PASSWORD PIC X(5) VALUE "TIGER".
01 PASSWORD-L PIC S9(9) VALUE 5 COMP. 01 CONN PIC S9(9) VALUE 0 COMP. 01 CONN-L PIC S9(9) VALUE 0 COMP. 01 CONN-MODE PIC S9(9) VALUE 0 COMP.
01 VARCHAR2-TYPE PIC S9(9) VALUE 1 COMP.
01 INT-TYPE PIC S9(9) VALUE 3 COMP.
01 DESCRIP-LEN PIC S9(9) VALUE 20 COMP.
01 INT-L PIC S9(9) VALUE 4 COMP.
01 MAX-TABLE PIC S9(9) VALUE 20 COMP.
01 ZERO-A PIC S9(9) VALUE 0 COMP.
01 VERSION-7 PIC S9(9) VALUE 2 COMP.
01 MINUS-ONE PIC S9(9) VALUE -1 COMP.
01 DROP-TBL PIC X(20) VALUE
"DROP TABLE part_nos".
01 DROP-TBL-L PIC S9(9) VALUE 20 COMP.
01 CREATE-TBL PIC X(100) VALUE
"CREATE TABLE part_nos
- "(partno NUMBER(8), description CHAR(20))".
01 CREATE-TBL-L PIC S9(9) VALUE 100 COMP.
01 CREATE-PKG PIC X(256) VALUE
"CREATE PACKAGE update_parts AS
- "TYPE pnt IS TABLE OF NUMBER
- "INDEX BY BINARY_INTEGER;
- "TYPE pdt IS TABLE OF CHAR(20)
- "INDEX BY BINARY_INTEGER;
- "PROCEDURE add_parts (n IN INTEGER,
- "descrip IN pdt,
- "partno IN pnt);
- "END update_parts;".
01 CREATE-PKG-L PIC S9(9) VALUE 256 comp.
01 CREATE-PKG-BODY PIC X(256) VALUE
"CREATE PACKAGE BODY update_parts AS
- "PROCEDURE add_parts (n IN INTEGER,
- "descrip IN pdt,
- "partno IN pnt);
- "BEGIN
- "FOR i IN 1..n LOOP
- "INSERT INTO part_nos
- "VALUES (partno(i), descrip(i));
- "END LOOP;
- "END;
- "END update_parts;".
* PL/SQL anonymous block, calls update_parts
01 PLS-BLOCK PIC X(100) VALUE
"BEGIN add_parts(3, :DESC, :PARTS); END;".
01 PLS-BLOCK-L PIC S9(9) VALUE 100 COMP.
PROCEDURE DIVISION.
START-MAIN.
* Connect to Oracle in non-blocking mode.
* HDA must be initialized to zeros before call to OLOG.
MOVE LOW-VALUES TO HOST-DATA-AREA.
CALL "OLOG" USING LDA, HOST-DATA-AREA, USERNAME,
USERNAME-L, PASSWORD, PASSWORD-L, CONN,
CONN-L, CONN-MODE.
IF LDA-RC IN LDA NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-STOP.
DISPLAY " ".
DISPLAY "Connected to Oracle as user ", USERNAME.
DISPLAY " ".
* Open the cursor.
* Use parameters PASSWORD, etc. for unused parameters.
CALL "OOPEN" USING CURSOR, LDA, PASSWORD, PASSWORD-L,
MINUS-ONE, PASSWORD, PASSWORD-L.
IF CURS-RC IN CURSOR NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-STOP.
* Parse the drop table statement.
* The statement is executed by OPARSE because
* the DEFFLG parameter is zero.
CALL "OPARSE" USING CURSOR, DROP-TBL, DROP-TBL-L,
ZERO-A, VERSION-7.
IF CURS-RC IN CURSOR NOT = 0 AND
CURS-RC NOT = 942
PERFORM ORA-ERROR
GO TO EXIT-CLOSE.
* Parse as well as execute the create table statement.
CALL "OPARSE" USING CURSOR, CREATE-TBL, CREATE-TBL-L,
ZERO-A, VERSION-7.
IF CURS-RC IN CURSOR NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-CLOSE.
* Parse the PL/SQL block.
CALL "OPARSE" USING CURSOR, PLS-BLOCK, PLS-BLOCK-L,
ZERO-A, VERSION-7.
IF CURS-RC IN CURSOR NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-CLOSE.
* Bind the two COBOL tables.
MOVE DESCRIP-L TO DESCRIP-ALEN(1).
MOVE DESCRIP-L TO DESCRIP-ALEN(2).
MOVE DESCRIP-L TO DESCRIP-ALEN(3).
CALL "OBNDRA" USING CURSOR, DESCRIP-PH, DESCRIP-PH-L,
DESCRIP(1), DESCRIP-L, VARCHAR2-TYPE,
MINUS-ONE, ZERO-A, DESCRIP-ALEN(1),
DESCRIP-RC(1), MAX-TABLE, DESCRIP-CS,
ZERO-A, MINUS-ONE, MINUS-ONE.
IF CURS-RC IN CURSOR NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-CLOSE.
MOVE INT-L TO PARTNO-ALEN(1).
MOVE INT-L TO PARTNO-ALEN(2).
MOVE INT-L TO PARTNO-ALEN(3).
CALL "OBNDRA" USING CURSOR, PARTNO-PH, PARTNO-PH-L,
PARTNOS(1), INT-L, INT-TYPE,
MINUS-ONE, ZERO-A,
PARTNO-ALEN(1), PARTNO-RC(1),
MAX-TABLE, PARTNO-CS,
ZERO-A, MINUS-ONE, MINUS-ONE.
IF CURS-RC IN CURSOR NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-CLOSE.
* Execute the PL/SQL block, calling update_parts.
CALL "OEXEC" USING CURSOR.
IF CURS-RC IN CURSOR NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-CLOSE.
EXIT-CLOSE.
CALL "OCLOSE" USING CURSOR.
CALL "OLOGOF" USING LDA.
EXIT-STOP.
STOP RUN.
ORA-ERROR.
IF LDA-RC IN LDA NOT = 0
DISPLAY "OLOGON error"
MOVE LDA-RC TO ERR-RC
MOVE "0" TO CURS-FNC
ELSE IF CURS-RC IN CURSOR NOT = 0
MOVE CURS-RC IN CURSOR TO ERR-RC
DISPLAY "Oracle error. Code is ", ERR-RC WITH CONVERSION,
" Function is ", CURS-FNC WITH CONVERSION.
CALL "OERHMS" USING LDA, ERR-RC, MSGBUF, MSGBUF-L.
DISPLAY MSGBUF.
Parameters
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
| SQLVAR | PIC X(n) | IN |
| SQLVL | PIC S9(9) COMP | IN |
| PROGV | (Address) (1) | IN/OUT(2) |
| PROGVL | PIC S9(9) COMP | IN |
| FTYPE | PIC S9(9) COMP | IN |
| SCALE | PIC S9(9) COMP | IN |
| INDP | PIC S9(4) COMP | IN/OUT(2) |
| ALEN | PIC S9(4) COMP | IN/OUT |
| ARCODE | PIC S9(4) COMP | OUT(3) |
| MAXSIZ | PIC S9(9) COMP | IN |
| CURSIZ | PIC S9(9) COMP | IN/OUT(2) |
| FMT | PIC X(6) | IN |
| FMTL | PIC S9(9) COMP | IN |
| FMTT | PIC S9(9) COMP | IN |
Note 2. IN/OUT parameter on the execute call.
Note 3. Value returned; OUT parameter on the execute call.
CURSOR A cursor data area within the program.
SQLVAR 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 :DEPT has a length of 5.
PROGV The address of a program variable or table of program variables from which data will be retrieved when OEXEC is issued.
PROGVL The length in bytes of the program variable or array element. Since 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 PROGVL parameter is a PIC S(9) COMP. However, on some systems the underlying parameter type in the OCI library might be only two bytes. When binding LONG VARCHAR and LONG VARRAW buffers, this appears to limit the maximum length of the buffer to 64K bytes. To bind a longer buffer for these datatypes, move -1 to PROGVL and pass the actual data area length (total buffer length - 4) in the first four bytes of PROGV. Set this value before calling OBNDRA.
FTYPE
The external datatype of the program variable as defined within the user program. Oracle converts the program variable from external to internal format before it is bound to the SQL statement. A list of external datatypes and type codes is.
SCALE Specifies the number of digits to the right of the decimal point for fields where FTYPE is 7 (PACKED DECIMAL). SCALE is ignored for all other types.
INDP An indicator parameter, or a table of indicator parameters if PROGV is a table. As a table, INDP must contain at least the same number of elements as the PROGV table.
See Chapter 2 for more information about indicator variables.
ALEN A table containing the length of the data. This is the effective length in bytes of the bind variable element, not the size of the table containing the elements. For example, if PROGV is a table declared as
03 DESCRIP OCCURS 3 TIMES PIC X(20).
then ALEN must refer to a table of at least three elements.
If DESCRIP in the above example is an IN parameter, each element in the table indicated by ALEN should be set to the length of the data in the corresponding element of the DESCRIP table (20 in this example) before the execute call.
If DESCRIP in the above example is an OUT parameter, the length of the returned data appears in the table indicated by ALEN after the SQL statement or PL/SQL procedure is executed.
Once the bind is done using OBNDRA, you can change the length of the bind variable without rebinding. However, the length cannot be greater than that specified in ALEN.
ARCODE The column-level error return code. This parameter 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 the returned PROGV has been truncated or that a null occurred in the column, for example, ORA-01405 or ORA-01406.
If OBNDRA is being used to bind a table (that is, PROGV is a table), then ARCODE must also be a table of at least equal size.
MAXSIZ The maximum size for the array being bound. Values range from 1 to 32767. If OBNDRA is being used to bind a scalar, set this parameter to zero.
CURSIZ The current size of the array.
If PROGV is an IN parameter, set the value of CURSIZ to the size of the table being bound. If PROGV is an OUT parameter, then the number of valid elements being returned in the PROGV table is returned in CURSIZ after the SQL statement or PL/SQL block is executed.
To use OBNDRA to bind a scalar, you must be able to pass a zero by value in this parameter. If your COBOL compiler does not have a mechanism for passing parameters by value, you must use OBNDRV to bind scalars.
FMT The address of a character string that contains the format specifier for a packed decimal variable. This optional parameter is only used when the type of the bind variable is PACKED DECIMAL (PIC S9(N)V9(N) COMP-3) (datatype 7). The specifier has the form "mm.[+/-]nn", where "mm" is the total number of digits, from 1 to 38, and "nn" is the number of decimal places, or scale. For example, "09.+02" would be the format specifier for an Oracle column of the internal type NUMBER(9,2). The plus or minus sign is required. If "+" is used, "nn" is the number of digits to the right of the decimal place. If "-" is specified, then "nn" is the power of ten by which the number is multiplied before it is placed in the output buffer.
When this parameter is not used and your compiler does not allow you to omit optional parameters, then pass the length (FMTL) parameter with a value of zero to indicate that there is no format specifier.
FMTL The length of the format conversion specifier string. If zero, then FMT and FMTT become unused parameters.
FMTT Specifies the format type of the conversion format string. The only value allowed is 7 (PACKED DECIMAL).
See Also
OBINDPS, OBNDRN, OBNDRV, OEXEC, OEXN, OPARSE.
OBNDRN and OBNDRV associate the address of a program variable, PROGVAR, with the specified placeholder in the SQL statement. The placeholder is identified by name (SQLVAR) for the OBNDRV routine and by number for OBNDRN. OEXEC then uses these addresses to assign values to the placeholders when executing the SQL statement.
SyntaxCALL "OBNDRN" USING CURSOR, SQLVN, PROGV, PROGVL, FTYPE, [SCALE], [INDP], [FMT], [FMTL], [FMTT].
CALL "OBNDRV" USING CURSOR, SQLVAR, SQLVL,
PROGV, PROGVL, FTYPE, [SCALE], [INDP],
[FMT], [FMTL], [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 an application needs to perform piecewise operations or utilize arrays of structures, you must bind your variables using OBINDPS instead.
The placeholder in the SQL statement is a SQL identifier. It must not be an Oracle reserved word and must be preceded by a colon (:) in the SQL statement. For example, the following SQL statement
SELECT ename,sal,com FROM emp WHERE deptno = :DEPT AND
comm > :MIN_COM
has two placeholders, :DEPT and :MIN_COM.
OBNDRV and OBNDRN differ only in the way they specify the placeholder. The OBNDRV routine specifies the placeholder in the SQL statement symbolically by name. For example, an OBNDRV call that binds the :DEPT placeholder in the SQL statement above to the program variable DEPT-NUM is
DATA DIVISION.
77 DEPT-NUM PIC S9(9) COMP.
77 INT-TYPE PIC S9(9) VALUE 3 COMP.
77 PH PIC X(5) VALUE ":DEPT".
77 PH-L PIC S9(9) VALUE 5 COMP.
77 MIN-COM PIC S9(9) COMP.
77 INT-LEN PIC S9(9) VALUE 4 COMP.
77 MINUS-1S PIC S9(4) VALUE -1 COMP.
77 MINUS-1L PIC S9(9) VALUE -1 COMP.
...
PROCEDURE DIVISION.
...
CALL "OBNDRV" USING CURSOR, PH, PH-L, DEPT-NUM, INT-LEN,
INT-TYPE, MINUS-1L, MINUS-1S, MINUS-1L, MINUS-1L, MINUS-1L.
The OBNDRN routine is used to bind the program variables to placeholders that are entered in the SQL statement as numbers preceded by a colon; for example:
SELECT ename,sal,comm FROM emp WHERE deptno = :1 AND
comm > :2
When OBNDRN is called, 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 MIN-COM to the placeholder :2 in the SQL statement above as follows:
...
PROCEDURE DIVISION.
...
CALL "OBNDRN" USING CURSOR, PH-2, MIN-COM, INT-LEN, INT-TYPE,
MINUS-1L, MINUS-1S, MINUS-1L, MINUS-1L, MINUS-1L.
where the placeholder ":2" is indicated in the SQLVARNUM parameter by the value 2.
Note: When using OBNDRN, the placeholder is :N, where N is greater than or equal to 1 and not greater than 255.
In a PL/SQL block, you cannot use OBNDRN to bind program variables to placeholders, since PL/SQL does not recognize numbered placeholders. Always use OBNDRV and named placeholders in PL/SQL blocks.
The OBNDRV or OBNDRN routine must be called after you call OPARSE to parse the SQL statement and before calling OEXEC to execute it.
If the values of the program variables change, you do not need to rebind using these routines before re-executing, since it is the address of the variables that is bound. However, if you change the actual program variable, you must rebind before re-executing.
For example, if you have bound the address of DEPT-NUM to the placeholder :DEPT, and you now want to use NEW-DEPT-NUM when executing the SQL statement above, you must call OBNDRV again to bind the new program variable address to the placeholder.
Also, you cannot in general rebind a placeholder to a variable of different type without reparsing the SQL statement. So, if you need to rebind with a different variable type, call OPARSE first to reparse the statement.
In general, OBNDRV and OBNDRN are not supported after an ODESCR call. You must issue an OPARSE call after an ODESCR call before binding any remaining placeholders.
At the time of the bind, Oracle stores the address of the program variable. If the same placeholder name occurs more than once in the SQL statement, a single call to OBNDRV or OBNDRN will bind all occurrences of the placeholder. The completion status of the bind is returned in the return code field of the cursor data area. A return code of zero indicates successful completion.
If your program is linked using the deferred mode option, bind errors that would normally be returned immediately 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 | (Address) | IN/OUT |
| SQLVAR | PIC X(n) | IN |
| SQLVL | PIC S9(9) COMP | IN |
| SQLVN | PIC S9(9) COMP | IN |
| PROGV | (Address) | IN (1) |
| PROGVL | PIC S9(9) COMP | IN |
| FTYPE | PIC S9(9) COMP | IN |
| SCALE | PIC S9(9) COMP | IN |
| INDP | PIC S9(4) COMP | IN (1) |
| FMT | PIC X(6) | IN |
| FMTL | PIC S9(9) COMP | IN |
| FMTT | PIC S9(9) COMP | IN |
CURSOR A cursor data area within the program.
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 :DEPT has a length of 5.
SQLVN Used only with OBNDRN, this parameter specifies a placeholder by number in the SQL statement referenced by the cursor. For example, if SQLVN is 2, it refers to all placeholders identified by :2 within the SQL statement.
PROGV The address of a program variable or table of program variables from which data will be retrieved when OEXEC or OEXN (for tables) is executed.
PROGVL The length in bytes of the program variable. Since OBNDRV or OBNDRN might be called only once for many different PROGV values on successive OEXEC calls, PROGVL must contain the maximum length of PROGV.
Note: The PROGVL parameter is a PIC S(9) COMP. However, on some systems the underlying parameter type in the OCI library might be only two bytes. When binding LONG VARCHAR and LONG VARRAW buffers, this appears to limit the maximum length of the buffer to 64K bytes. To bind a longer buffer for these datatypes, move -1 to PROGVL, and pass the actual data area length (total buffer length - 4) in the first four bytes of PROGV. Set this value before calling OBNDRN or OBNDRV.
FTYPE
The external datatype of the program variable as defined within the user program. Oracle converts the program variable from external to internal format before it is bound to the SQL statement. A list of external datatypes and type codes is.
SCALE Specifies the number of digits to the right of the decimal point for fields where FTYPE is 7 (PACKED DECIMAL). SCALE is ignored for all other types.
INDP Indicates whether the column value should be set to null. If this parameter contains a negative value when OEXEC, OEXN, or OEXFET is called, the column is set to null; otherwise, it is set to the value in the PROGVAR parameter.
Note: If OBNDRV or OBNDRN is being used to bind a table of elements (that is, if PROGV is a table), then the INDP parameter must also be a table of indicator parameters at least as large as the PROGV table.
FMT The address of a character string that contains the format specifier for a packed decimal variable. This optional parameter is only used when the type of the bind variable is PACKED DECIMAL (PIC S9(N)V9(N) COMP-3) (datatype 7). The specifier has the form "mm.[+/-]nn", where "mm"is the total number of digits, from 1 to 38, and "nn" is the number of decimal places, or scale. For example, "09.+02" would be the format specifier for an Oracle column of the internal type NUMBER(9,2). The plus or minus sign is required. If "+" is used, "nn" is the number of digits to the right of the decimal place. If "-" is specified, then "nn" is the power of ten by which the number is multiplied before it is placed in the output buffer.
When this parameter is not used, and your compiler does not allow you to omit optional parameters, then pass the length (FMTL) parameter with a value of zero to indicate that there is no format specifier.
FMTL The length of the format conversion specifier string. If zero, then FMT and FMTT become unused parameters.
FMTT Specifies the format type of the conversion format string. The only value allowed is 7 (PACKED DECIMAL).
See Also
OBINDPS, OBNDRA, ODESCR, OEXEC, OEXFET, OEXN, OPARSE.
OBREAK performs an immediate (asynchronous) abort of any currently executing OCI routine that is associated with the specified LDA. It is normally used to stop a long-running execute or fetch call that has not yet completed.
SyntaxCALL "OBREAK" USING LDA.
Comments
If no OCI routine is active when OBREAK is called, OBREAK will be ignored unless the next OCI routine called is OFETCH. In this case, the subsequent OFETCH call will be aborted.
OBREAK is the only OCI routine that you can call when another OCI routine is in progress. It should not be used when a logon (OLOG) is in progress, since the LDA is in an indeterminate state. The OBREAK routine cannot return a reliable error status to the LDA, since 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.
See the description of obreak() for a code example in C which runs under most UNIX operating systems..
Parameter
| Parameter Name | Type | Mode |
| LDA | (Address) | IN |
See Also
OLOG.
OCAN cancels a query after the desired number of rows have been fetched.
SyntaxCALL "OCAN" USING 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 CANCELparameter has the same effect as calling OCAN after the fetch completes.
Parameter
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
See Also
OCLOSE disconnects a cursor from the data areas associated with it in the Oracle Server.
SyntaxCALL "OCLOSE" USING CURSOR.
Comments
The OCLOSE routine frees all resources obtained by the OOPEN, OPARSE, and OEXEC operations using this cursor. If OCLOSE fails, the return code field of the cursor data area contains the error code.
Parameter
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
See Also
OCOF disables autocommit, that is, automatic commit of every SQL data manipulation statement.
SyntaxCALL "OCOF" USING LDA.
Comments
By default, autocommit is already disabled at the start of an OCI program. Having autocommit ON can have a serious impact on performance. So, if the OCON (autocommit ON) routine is used to enable autocommit for some special circumstance, OCOF should be used to disable autocommit as soon as it is practical.
If OCOF fails, the reason is indicated in the return code field of the LDA.
Parameter
| Parameter Name | Type | Mode |
| LDA | (Address) | IN/OUT |
See Also
OCOM commits the current transaction.
SyntaxCALL "OCOM" USING 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 reason is indicated in the return code field of the LDA.
Do not confuse the OCOM call (COMMIT) with the OCON call (turn autocommit ON).
Parameter
| Parameter Name | Type | Mode |
| LDA | (Address) | IN/OUT |
See Also
OCON enables autocommit, that is, automatic commit of every SQL data manipulation statement.
SyntaxCALL "OCON" USING 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 calling OEXEC indicates that the transaction has been committed.
If OCON fails, the reason is indicated in the return code field of the LDA.
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 routine with the OCOM (COMMIT) routine.
Parameter
| Parameter Name | Type | Mode |
| LDA | (Address) | IN/OUT |
See Also
ODEFIN defines an output buffer for a specified select-list item in a SQL query.
SyntaxCALL "ODEFIN" USING CURSOR, POS, BUF, BUFL, FTYPE, [SCALE], [INDP], [FMT], [FMTL], [FMTT], [RLEN], [RCODE].
Comments
An OCI program must call ODEFIN once for each select-list item in the SQL statement. Each call to ODEFIN associates an output variable in the program with a select-list item of the query. The output variable must be a scalar or a character string and must be compatible with the external datatype specified in the FTYPE parameter. See Table 3 - 2 for a list of datatypes and compatible variables. For use with OEXFET or OFEN, the output variable can be a table of scalars, or strings.
Oracle places data in the output variables when the program calls OEXFET, OFEN, or OFETCH.
If you do not know the lengths and datatypes of the select-list items, you can obtain this information by calling ODESCR before calling ODEFIN.
Call ODEFIN parsing the SQL statement. Call ODEFIN before calling the fetch routine (OEXFET, OFEN, or OFETCH).
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.
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 left-most) 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:
DATA DIVISION.
77 EMP-NAME PIC X(10).
77 EMP-NAME-L PIC S9(9) VALUE 10 COMP.
77 CHAR-TYPE PIC S9(9) VALUE 1 COMP.
77 EMP-NUM PIC S9(9) COMP.
77 EMP-NUM-L PIC S9(9) VALUE 4 COMP.
77 INT-TYPE PIC S9(9) VALUE 3 COMP.
77 SAL PIC S9(4)V9(2) COMP-3.
77 SAL-L PIC S9(9) VALUE 4 COMP.
77 INDP PIC S9(4) VALUE 0 COMP.
77 FMT PIC X(6) VALUE "08.+02".
77 FMT-L PIC S9(9) VALUE 6 COMP.
77 FMT-T PIC S9(9) VALUE 7 COMP.
77 RET-LEN1 PIC S9(4) COMP.
77 RET-CODE1 PIC S9(4) COMP.
77 RET-LEN2 PIC S9(4) COMP.
77 RET-CODE2 PIC S9(4) COMP.
77 RET-LEN3 PIC S9(4) COMP.
77 RET-CODE3 PIC S9(4) COMP.
77 ZERO-ARG PIC S9(9) VALUE 0 COMP.
77 ONE PIC S9(9) VALUE 1 COMP.
77 TWO PIC S9(9) VALUE 2 COMP.
77 THREE PIC S9(9) VALUE 3 COMP.
77 SCALE PIC S9(9) VALUE 7 COMP.
...
PROCEDURE DIVISION.
...
CALL "ODEFIN" USING CURSOR, ONE, EMP-NAME, EMP-NAME-L, CHAR-TYPE,
ZERO, INDP, FMT, ZERO-ARG, FMT-T, RET-LEN1, RET-CODE1.
CALL "ODEFIN" USING CURSOR, TWO, EMP-NUM, EMP-NUM-L, INT-TYPE,
ZERO, INDP, FMT, ZERO-ARG, FMT-T, RET-LEN2, RET-CODE2.
CALL "ODEFIN" USING CURSOR, THREE, SAL, SAL-L, FMT-T,
TWO, INDP, FMT, FMT-L, FMT-T, RET-LEN3, RET-CODE3.
Oracle provides return code information at the row level using the return code field in the cursor data area. If you require return code information at the column level, you must define the optional RCODE parameter, as in the examples above.
During each fetch, Oracle sets the associated RCODE for each select-list item processed. This code contains actual error codes and indicates either successful completion (0) or an exceptional condition, such as a null item fetched, the item fetched was truncated, or other non-fatal column errors.
The following codes can be returned in the RCODE parameter:
| Code | Meaning |
| 0 | Success. |
| 1405 | A NULL value 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 real number of this machine. |
| 3115 | Unsupported datatype. |
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
| POS | PIC S9(9) COMP | IN |
| BUF | (Address) | IN (1) |
| BUFL | PIC S9(9) COMP | IN |
| FTYPE | PIC S9(9) COMP | IN |
| SCALE | PIC S9(9) COMP | IN |
| INDP | PIC S9(4) COMP | IN (1) |
| FMT | PIC X(6) | IN |
| FMTL | PIC S9(9) COMP | IN |
| FMTT | PIC S9(9) COMP | IN |
| RLEN | PIC S9(4) COMP | IN (1) |
| RCODE | PIC S9(4) COMP | IN (1) |
CURSOR The cursor data area specified in the OPARSE call for the SQL statement being defined.
POS An index for a select-list item in the query. Position indices start at 1 for the first (or left-most) select-list item. The ODEFIN routine uses the position index to associate output buffers 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, ODEFIN returns a "variable not in select list" error in the return code field of the cursor data area.
BUF The address of the variable in the user program that receives the data when OFETCH or OFEN is executed. 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 define a table fetch operation using the OFEN routine, then the BUFFER parameter must be a table large enough to hold the set of items that will be fetched.
BUFL The length in bytes of the buffer being defined. If BUF is a table, this is the size in bytes of one element of the table.
Note: The BUFL parameter is a PIC S(9) COMP. However, on some systems the underlying parameter type in the OCI library might be only two bytes. When binding LONG VARCHAR and LONG VARRAW buffers, this appears to limit the maximum length of the buffer to 64K bytes. To bind a longer buffer for these datatypes, move -1 to BUFL and pass the actual data area length (total buffer length - 4) in the first four bytes of BUF. Set this value before calling ODEFIN.
FTYPE
The code for 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 type codes is.
SCALE Specifies the number of digits to the right of the decimal point to be returned for items of FTYPE = 7 (PACKED DECIMAL). Ignored for all other datatypes.
INDP
The value of INDP after OEXFET, OFETCH, or OFEN is executed indicates whether the select-list item fetched was null, truncated, or not altered. See the section "Indicator Variables" for more information.
Note: The INDP parameter contains a subset of the functionality provided by the RLEN and RCODE parameters.
FMT A character string that contains the format specifier for a packed decimal variable. This optional parameter is only used when the type of the defined variable is PACKED DECIMAL (PIC S9(N)V9(N) COMP-3). The specifier has the form "mm.[+/-]nn", where "mm" is the total number of digits, from 1 to 38, and "nn" is the number of decimal places, or scale. For example, "09.+02" would be the format specifier for an Oracle column of the internal type NUMBER(9,2). The plus or minus sign is required. If "+" is used, "nn" is the number of digits to the right of the decimal place. If "-" is specified, then "nn" is the power of ten by which the number is multiplied before it is placed in the output buffer.
If your compiler does not allow you to omit optional parameters, then pass the length (FMTL) parameter with a value of zero to indicate that there is no format specifier.
FMTL The length of the format conversion specifier string. If zero, then FMT and FMTT are unused parameters.
FMTT Specifies the format type of the conversion format string. The only value allowed is 7 (PACKED DECIMAL)
RLEN A PIC S9(4) variable or table. Oracle places the actual length of the returned column in this variable after a fetch is performed. If ODEFIN is being used to associate a table with a select-list item, the RLEN parameter must also be a table of PIC S9(4) variables of the same size as the BUF table. Return lengths are valid after the fetch.
RCODE A PIC S9(4) variable or table. Oracle places the column return code in this variable after the fetch is performed. If ODEFIN is being used to associate a table with a select-list item, the RCODE parameter must also be a table of PIC S9(4) variables of the same size as the BUF table.
See Also
ODEFINPS, ODESCR, OEXFET, OFEN, OFETCH, OPARSE.
ODEFINPS 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.
SyntaxCALL "ODEFINPS" USING CURSOR, OPCODE, POS, BUFCTX, BUFL, FTYPE [SCALE], [INDP], [FMT], [FMTL], [FMTT], [RLENP], [RCODEP], BUF-SKIP, IND-SKIP, LEN-SKIP, 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:
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.
See the description of odefinps() for a sample C language program demonstrating the use of ODEFINPS.
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
| OPCODE | PIC S9(2) COMP | IN |
| POS | PIC S9(9) COMP | IN |
| BUFCTX | PIC S9(2) COMP | IN |
| BUFL | PIC S9(9) COMP | IN |
| FTYPE | PIC S9(9) COMP | IN |
| SCALE | PIC S9(9) COMP | IN |
| INDP | PIC S9(4) COMP | IN |
| FMT | PIC X(6) | IN |
| FMTL | PIC S9(9) COMP | IN |
| FMTT | PIC S9(9) COMP | IN |
| RLENP | PIC S9(4) COMP | OUT |
| RCODEP | PIC S9(4) COMP | IN |
| BUF-SKIP | PIC S9(9) COMP | IN |
| IND-SKIP | PIC S9(9) COMP | IN |
| LEN-SKIP | PIC S9(9) COMP | IN |
| RC-SKIP | PIC S9(9) COMP | IN |
Standard scalar and array defines are those defines which were previously possible using ODEFIN.
CURSOR 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 B 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 Specifies the number of digits to the right of the decimal point for fields where FTYPE is 7 (PACKED DECIMAL). SCALE is ignored for all other types.
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 variables.
FMT A character string that contains the format specifier for a packed decimal variable. This optional parameter is only used when the type of the defined variable is PACKED DECIMAL (PIC S9(N)V9(N) COMP-3). The specifier has the form "mm.[+/-]nn", where "mm" is the total number of digits, from 1 to 38, and "nn" is the number of decimal places, or scale. For example, "09.+02" would be the format specifier for an Oracle column of the internal type NUMBER(9,2). The plus or minus sign is required. If "+" is used, "nn" is the number of digits to the right of the decimal place. If "-" is specified, then "nn" is the power of ten by which the number is multiplied before it is placed in the output buffer
.If your compiler does not allow you to omit optional parameters, then pass the length (FMTL) parameter with a value of zero to indicate that there is no format specifier.
FMTL The length of the format conversion specifier string. If zero, then FMT and FMTT are unused parameters.
FMTT Specifies the format type of the conversion format string. The only value allowed is 7 (PACKED DECIMAL)
RLENP 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 elements.
RCODEP 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 elements.
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 describes select-list items for dynamic SQL queries. The ODESCR procedure returns internal datatype and size information for a specified select-list item.
SyntaxCALL "ODESCR" USING CURSOR, POS, DBSIZE, [DBTYPE], [CBUF], [CBUFL], [DSIZE], [PREC], [SCALE], [NULLOK].
Comments
You can call ODESCR after an OPARSE call to obtain the following information for each select-list item:
The return code field of the cursor data area indicates success (zero) or failure (non-zero) of the ODESCR call.
The ODESCR routine 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 on the next page shows how you can call ODESCR repeatedly to describe the first 12 select-list items in an arbitrary SQL statement. See the second sample program in Appendix B for additional information.
Note: A dependency exists between the results returned by a describe operation (ODESCR) and a bind operation (OBINDPS, OBNDRA, OBNDRN or OBNDRV). Because a select-list item might contain bind variables, the type returned by ODESCR can vary depending on the results of bind operations.
So, if you have placeholders for bind variables in a SELECT statement and you plan to 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 that the rebind operation might change the results returned for a select-list item.
The following complete program shows how the ODESCR routine can be used to describe the select list of arbitrary dynamic SQL statements.
* ODESCR.COB
*
* Demo example for COBOL dynamic ODESCR
IDENTIFICATION DIVISION.
PROGRAM-ID. ODESCR-TEST.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 LDA.
03 LDA-V2RC PIC S9(4) COMP.
03 FILLER PIC X(10).
03 LDA-RC PIC S9(4) COMP.
03 FILLER PIC X(50).
01 CURSOR.
03 CURS-V2RC PIC S9(4) COMP.
03 CURS-TYPE PIC S9(4) COMP.
03 CURS-ROWS-PROCESSED PIC S9(9) COMP.
03 CURS-OFFS PIC S9(4) COMP.
03 CURS-FNC PIC X.
03 FILLER PIC X.
03 CURS-RC PIC S9(4) COMP.
03 FILLER PIC X(50).
01 HOST-DATA-AREA PIC X(512).
01 XCOL-NAMES.
03 NAME OCCURS 12 TIMES PIC X(30).
01 XCOL-L.
03 NAME-L OCCURS 12 TIMES PIC S9(9)
COMP.
01 XDBSIZE.
03 DBSIZE OCCURS 12 TIMES PIC S9(4)
COMP.
01 XCOL-DTYPE.
03 DBTYPE OCCURS 12 TIMES PIC S9(4)
COMP.
01 XCOL-DSIZE.
03 DSIZE OCCURS 12 TIMES PIC S9(9)
COMP.
01 XCOL-PRECISION.
03 PRECISION OCCURS 12 TIMES PIC S9(4)
COMP.
01 XCOL-SCALE.
03 SCALE OCCURS 12 TIMES PIC S9(4)
COMP.
01 XCOL-NULLS-ALLOWED.
03 NULL-OK OCCURS 12 TIMES PIC S9(4)
COMP.
01 USER-ID PIC X(11) VALUE "SCOTT".
01 USER-ID-L PIC S9(9) VALUE 5 COMP.
01 PASSWORD PIC X(5) VALUE "TIGER".
01 PASSWORD-L PIC S9(9) VALUE 5 COMP. 01 CONN PIC S9(9) VALUE 0 COMP. 01 CONN-L PIC S9(9) VALUE 0 COMP. 01 CONN-MODE PIC S9(9) VALUE 0 COMP.
01 MINUS-ONE PIC S9(9) VALUE -1 COMP.
01 SQL-STATEMENT PIC X(80).
01 SQL-STATEMENT-L PIC S9(9) VALUE 80 COMP.
01 VERSION-7 PIC S9(9) VALUE 2 COMP.
01 POS PIC S9(9) COMP.
01 INDX PIC 9(3) COMP.
01 MSGBUF PIC X(80).
01 MSGBUF-L PIC S9(9) VALUE 80 COMP.
01 ERR-RC PIC S9(4) COMP.
01 IMD-PARSE PIC S9(9) VALUE 0 COMP.
01 SQLL PIC S9(9) COMP.
PROCEDURE DIVISION.
START-MAIN.
* Connect to Oracle in non-blocking mode.
* HDA must be initialized to zeros before call to OLOG.
MOVE LOW-VALUES TO HOST-DATA-AREA.
CALL "OLOG" USING LDA, HOST-DATA-AREA, USERNAME,
USERNAME-L, PASSWORD, PASSWORD-L, CONN,
CONN-L, CONN-MODE.
IF LDA-RC IN LDA NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-STOP.
DISPLAY " ".
DISPLAY "Connected to Oracle as user: " USER-ID. DISPLAY " ".
* Open the cursor.
* Use parameters PASSWORD, etc. for unused parameters.
CALL "OOPEN" USING CURSOR, LDA, PASSWORD, PASSWORD-L,
MINUS-ONE, PASSWORD, PASSWORD-L.
IF CURS-RC IN CURSOR NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-STOP.
DISPLAY "Enter SQL statements with ';' terminator".
PERFORM PROCESS-STATEMENT
THRU PROCESS-STATEMENT-EXIT 10000 TIMES.
EXIT-CLOSE.
CALL "OCLOSE" USING CURSOR.
EXIT-LOGOFF.
CALL "OLOGOF" USING LDA.
EXIT-STOP.
STOP RUN.
* Accept and describe SQL SELECT statements.
PROCESS-STATEMENT.
DISPLAY " ".
DISPLAY "> " WITH NO ADVANCING.
ACCEPT SQL-STATEMENT.
MOVE 0 TO SQLL.
INSPECT SQL-STATEMENT TALLYING SQLL
FOR CHARACTERS BEFORE INITIAL ';'.
IF SQLL = 80
GO TO EXIT-CLOSE.
CALL "OSQL" USING CURSOR, SQL-STATEMENT, SQLL,
IMD-PARSE, VERSION-7.
IF CURS-RC IN CURSOR NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-CLOSE.
PERFORM DESCRIBE-STATEMENT VARYING POS FROM 1 BY 1
UNTIL (CURS-RC IN CURSOR = 1007 OR POS > 12).
SUBTRACT 2 FROM POS.
DISPLAY "There were", POS WITH CONVERSION,
" select-list items".
PERFORM
VARYING INDX FROM 1 BY 1 UNTIL INDX > POS
DISPLAY NAME(INDX), NAME-L(INDX) WITH CONVERSION,
DBTYPE(INDX) WITH CONVERSION
END-PERFORM.
PROCESS-STATEMENT-EXIT.
DESCRIBE-STATEMENT.
MOVE 30 TO NAME-L(POS).
CALL "ODESCR" USING CURSOR, POS, DBSIZE(POS),
DBTYPE(POS), NAME(POS),
NAME-L(POS), DSIZE(POS),
PRECISION(POS), SCALE(POS),
NULL-OK(POS).
IF (CURS-RC IN CURSOR NOT = 0 AND
CURS-RC IN CURSOR NOT = 1007)
PERFORM ORA-ERROR
GO TO EXIT-CLOSE.
ORA-ERROR.
IF LDA-RC IN LDA NOT = 0
DISPLAY "OLOGON error"
MOVE LDA-RC TO ERR-RC
MOVE 0 TO CURS-FNC
ELSE IF CURS-RC IN CURSOR NOT = 0
MOVE CURS-RC IN CURSOR TO ERR-RC
DISPLAY "Oracle error. Code is ",
ERR-RC WITH CONVERSION,
" Function is ", CURS-FNC WITH CONVERSION.
CALL "OERHMS" USING LDA, ERR-RC, MSGBUF, MSGBUF-L.
DISPLAY MSGBUF.
Parameters
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
| POS | PIC S9(9) COMP | IN |
| DBSIZE | PIC S9(9) COMP | OUT |
| DBTYPE | PIC S9(4) COMP | OUT |
| CBUF | PIC X(n) | OUT |
| CBUFL | PIC S9(9) COMP | IN/OUT |
| DSIZE | PIC S9(9) COMP | OUT |
| PREC | PIC S9(4) COMP | OUT |
| SCALE | PIC S9(4) COMP | OUT |
| NULLOK | PIC S9(4) COMP | OUT |
POS This is the position index of the select-list item in the SQL query. Each item is referenced by position as if they were numbered left to right (or first to last) consecutively beginning with 1. If you specify a position index greater than the number of items in the select-list or less than 1, ODESCR returns a "variable not in select-list" error in the return code field of the cursor data area.
DBSIZE DBSIZE receives the maximum size of the column, as stored in the Oracle data dictionary. If the column is defined as VARCHAR2, CHAR, or NUMBER, the length returned is the maximum length specified for the column.
DBTYPE
DBTYPE receives the internal datatype code of the select-list item. A list of Oracle internal datatype codes and the possible external conversions for each of them is provided.
CBUF A character buffer in the program that receives the name of the select-list item (name of the column or wording of the expression).
CBUFL CBUFL is set to the length of CBUF. CBUFL should be set before ODESCR is called. If CBUFL is not specified or if the value contained in CBUFL is 0, then the column name is not returned.
On return from ODESCR, CBUFL contains the length of the column or expression name that was returned in CBUF. The column name in CBUF is truncated if it is longer that the length specified in CBUFL on the call.
DSIZE 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 SQL routines, like SUBSTR or TO_CHAR, are used to modify the representation of a column. Values returned in DSIZE are listed in the following table.
| 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 datatype 1 (such as TO_CHAR()) | same as the dsize parameter |
SCALE The SCALE parameter receives the scale of numeric select-list items.
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 numbers, as shown below:
| SQL Datatype | Precision | Scale |
| NUMBER(P) | P | 0 |
| NUMBER(P,S) | P | S |
| NUMBER | 0 | 0 |
| FLOAT(N) | 0 | 0 |
NULLOK NULLOK is set to 1 if null values are allowed for that column and to 0 if they are not.
See Also
OBINDPS, OBNDRA, OBNDRN, OBNDRV, ODEFIN, ODEFINPS, OPARSE.
ODESSP is used to describe the parameters of a PL/SQL procedure or function stored in an Oracle database.
SyntaxCALL "ODESSP" USING LDA, OBJNAM, ONLEN, RSV1, RSV1LN, RSV2, RSV2LN, OVRLD, POS, LEVEL, ARGNM, ARNLEN, DTYPE, DEFSUP, PMODE, DTSIZ, PREC, SCALE, RADIX, SPARE, 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:
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 (
IDnum in emp.empno%type,
salary out emp.sal%type);
function get_sal_info (
name emp.ename%type) return emp.sal%type;
end EMP_RECS;
A code fragment to describe these procedures and functions follows:
WORKING-STORAGE SECTION.
01 LDA.
03 LDA-V2RC PIC S9(4) COMP.
03 FILLER PIC X(10).
03 LDA-RC PIC S9(4) COMP.
03 FILLER PIC X(50).
01 OBJNAM PIC X(30) VALUE
"SCOTT.EMP_RECS.GET_SAL_INFO"..
01 ONLEN PIC S9(9) VALUE 27 COMP.
01 RSV1 PIC X(10) VALUE "".
01 RSV1LN PIC S9(9) VALUE 0 COMP.
01 RSV2 PIC X(10) VALUE "".
01 RSV2LN PIC S9(9) VALUE 0 COMP.
01 ARRSIZ PIC S9(9) VALUE 10 COMP.
01 PARM-OVRLD.
03 OVRLD OCCURS 10 TIMES PIC S9(4) COMP.
01 PARM-POS.
03 POS OCCURS 10 TIMES PIC S9(4) COMP.
01 PARM-LEVEL.
03 LEVEL OCCURS 10 TIMES PIC S9(4) COMP.
01 PARM-NAMES.
03 ARGNAM OCCURS 10 TIMES PIC X(30).
01 PARM-L.
03 ARNLEN OCCURS 10 TIMES PIC S9(4) COMP.
01 PARM-DTYPE.
03 DTYPE OCCURS 10 TIMES PIC S9(4) COMP.
01 PARM-DEFSUP.
03 DEFSUP OCCURS 10 TIMES PIC S9(4) COMP.
01 PARM-PMODE.
03 PMODE OCCURS 10 TIMES PIC S9(4) COMP.
01 PARM-DTSIZ
03 DTSIZ OCCURS 10 TIMES PIC S9(9) COMP.
01 PARM-PREC.
03 PREC OCCURS 10 TIMES PIC S9(4) COMP.
01 PARM-SCALE.
03 SCALE OCCURS 10 TIMES PIC S9(4) COMP.
01 PARM-RADIX.
03 RADIX OCCURS 10 TIMES PIC S9(4) COMP.
01 PARM-SPARE.
03 SPARE OCCURS 10 TIMES PIC S9(9) COMP.
...
PROCEDURE DIVISION.
START-MAIN.
...
MOVE 10 TO ARRSIZ
CALL "ODESSP" USING LDA, OBJNAM, ONLEN, RSV1, RSV1LN,
RSV2, RSV2LN, OVRLD, POS, LEVEL, ARGNM,
ARNLEN, DTYPE, DEFSUP, PMODE, DTSIZ,
PREC, SCALE, RADIX, SPARE, ARRSIZ.
DISPLAY " ".
DISPLAY "PARM NAME POS OVERLOAD DATATYPE".
PERFORM
VARYING INDX FROM 1 BY 1 UNTIL INDX > ARRSIZ
DISPLAY ARGNAMINDX), POS(INDX) WITH CONVERSION,
OVRLD(INDX) WITH CONVERSION, DTYPE(INDX)
WITH CONVERSION.
END-PERFORM.
...
When this call to ODESSP completes, the return parameter arrays are filled in as shown in Table 5 - 1. 6 is returned in the ARRSIZ parameter, as there were a total of five parameters and one function return type described.
| Parameter Name | Type | Mode |
| LDA | (Table) | IN/OUT |
| OBJNAM | PIC S9(9) COMP | IN |
| ONLEN | PIC S9(9) COMP | IN |
| RSV1 | PIC X(N) | IN |
| RSV1LN | PIC S9(9) COMP | IN |
| RSV2 | PIC X(N) | IN |
| RSV2LN | PIC S9(9) | IN |
| OVRLD | PIC S9(4) | OUT |
| POS | PIC S9(4) | OUT |
| LEVEL | PIC S9(4) | OUT |
| ARGNM | PIC X(30) OCCURS (M) TIMES | OUT |
| ARNLEN | PIC S9(4) | OUT |
| DTYPE | PIC S9(4) | OUT |
| DEFSUP | PIC S9(4) | OUT |
| PMODE | PIC S9(4) | OUT |
| DTSIZ | PIC S9(4) | OUT |
| PREC | PIC S9(4) | OUT |
| SCALE | PIC S9(4) | OUT |
| RADIX | PIC S9(4) | OUT |
| SPARE | PIC S9(9) | OUT |
| ARRSIZ | PIC S9(9) | 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 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, zero 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 zero, this indicates that a function return type is being described.
LEVEL For scalar parameters, LEVEL returns zero. For a record parameter, zero is returned for the record itself, then for each parameter in the record the parameter's level in the record is indicated, starting from one, in successive elements of the returned value of LEVEL.
For array parameters, zero is returned for the array itself. The next element in the return array is at level one, 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 An array of strings that returns the name of each parameter in the procedure or function.
ARNLEN An array returning 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 zero 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.
PMODE 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 being described is of the type EMP.ENAME%TYPE, the value 10 is returned for this parameter, since that is the length of the ENAME column.
For number types, 22 is returned. See the description of the DBSIZE parameter under ODESCR in this chapter for more information.
PREC This parameter indicates the precision of the corresponding parameter if the parameter is numeric.
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 returns the text of an Oracle error message, given the error code RCODE.
SyntaxCALL "OERHMS" USING LDA, RCODE, BUF, 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.
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, since several messages can be returned. 1000 bytes should be sufficient to handle most cases.
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:
DATA DIVISION.
01 LDA-1.
03 LDA-V2RC PIC S9(4) COMP.
03 FILLER PIC X(10).
03 LDA-RC PIC S9(4) COMP.
03 FILLER PIC X(50).
01 LDA-2.
03 LDA-V2RC PIC S9(4) COMP.
03 FILLER PIC X(10).
03 LDA-RC PIC S9(4) COMP.
03 FILLER PIC X(50).
77 MSG PIC X(512).
77 MSG-L PIC S9(9) VALUE 512 COMP.
...
PROCEDURE DIVISION.
...
* after logging on, and getting an error in another call
* on the connection using LDA-2...
CALL "OERHMS" USING LDA-2, LDA-RC in LDA-2,
MSG, MSG-L.
DISPLAY MSG.
Parameters
| Parameter Name | Type | Mode |
| LDA | (Address) | IN/OUT |
| RCODE | PIC S9(4) COMP | IN |
| BUF | PIC X(n) | OUT |
| BUFSIZ | PIC S9(9) COMP | IN |
RCODE The return code containing an Oracle error number.
BUF A character buffer that receives the error message text.
BUFSIZ The size of the buffer in bytes. The buffer size is essentially unlimited. However, a buffer of between 512 and 1024 bytes is usually sufficient.
See Also
OEXEC executes the SQL statement associated with a cursor.
SyntaxCALL "OEXEC" USING CURSOR.
Comments
Before calling OEXEC, you must call OPARSE to parse the SQL statement; this call must complete successfully. If the SQL statement is a query, you must call ODEFIN or ODEFINPS to associate each select-list item in the query with the address of a program output buffer. If the SQL statement contains placeholders for bind variables, you must also call OBINDPS, OBNDRA, OBNDRV or OBNDRN to bind each placeholder to the address of a program variable.
For queries, after OEXEC is called, your program must explicitly request each row of the result using OFEN or OFETCH.
For UPDATE, DELETE, and INSERT statements, OEXEC executes the SQL statement and sets the return code field and the rows processed count field in the cursor data area. Note that an UPDATE 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.
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).
Refer to the description of the OFETCH routine in this chapter for an example showing how OEXEC is used.
Parameter
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
See Also
OBINDPS, OBNDRA, OBNDRN, ODEFIN, ODEFINPS, OPARSE.
OEXFET executes the SQL statement associated with a cursor, then fetch data from one or more rows. A cancel (equivalent to a call to OCAN) of the cursor can also be performed by OEXFET.
SyntaxCALL "OEXFET" USING CURSOR, NROWS, CANCEL, EXACT.
Comments
Before calling OEXFET, the OCI program must first call OPARSE to parse the SQL statement, call OBINDPS, OBNDRA, OBNDRN or OBNDRV to bind input variables (if any), then call ODEFIN or ODEFINPS to define the output variables.
If the OCI program was linked using the deferred mode 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 to process a SQL statement requires more memory on the client system than the non-deferred sequence. So, you gain execution speed at the expense of the additional space required.
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 routine.
The following example shows how to use deferred parse, bind, and define operations together with OEXFET to process a SQL statement:
DATA DIVISION.
WORKING STORAGE SECTION.
01 XTABLE1.
02 ENAMES PIC X(20) OCCURS 12000 TIMES.
02 INDP PIC S9(4) OCCURS 12000 TIMES COMP.
02 RET-L PIC S9(4) OCCURS 12000 TIMES COMP.
02 RET_CODE PIC S9(4) OCCURS 12000 TIMES COMP.
77 SQL-STMT PIC X(50) VALUE "SELECT ename
- FROM emp WHERE deptno = 20".
77 ONE PIC S9(9) VALUE 1 COMP.
77 MAX-EMPLOYEES PIC S9(9) VALUE 12000 COMP.
77 ENAME-LEN PIC S9(9) VALUE 20 COMP.
77 SQL-STMT-L PIC S9(9) VALUE 50 COMP.
...
PROCEDURE DIVISION.
...
* Parse the statement. Set the deferred flag.
CALL "OPARSE" USING CURSOR, SQL-STMT, SQL-STMT-L, ONE.
...
* Call ODEFIN to define the output variables.
CALL "ODEFIN" USING CURSOR, ONE, NAMES, ENAME-LEN, CHAR-T,
ZERO, INDP, FMT, ZERO, FMT-T, RET-L, RET-CODE.
CALL "OEXFET" USING CURSOR, MAX-EMPLOYEES, ZERO, ZERO.
In this example, the EXACT parameter is set to zero, so a "no data found" error is not returned if the number of rows returned is less than MAX-EMPLOYEES.
Parameters
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
| NROWS | PIC S9(9) COMP | IN |
| CANCEL | PIC S9(9) COMP | IN |
| EXACT | PIC S9(9) COMP | IN |
NROWS The number of rows to fetch. If NROWS is greater than 1, then you must define tables to receive the select-list values, as well as any indicator parameters. 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
CANCEL If this parameter is non-zero when OEXFET is called, the cursor is cancelled 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. 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 executes a SQL statement. Tables can be bound to placeholders in the statement, taking advantage of the Oracle array interface.
SyntaxCALL "OEXN" USING CURSOR, ITERS, ROWOFF.
Comments
OEXN is similar to OEXEC, but it allows you to take advantage of the Oracle array (table) interface. OEXN allows operations using a table containing multiple bind variables. OEXN is generally much faster than successive calls to OEXEC, especially in a networked environment.
The following example declares three tables, one of ten integers (PIC S9(9) COMP), one of ten indicator variables (PIC S9(4) COMP), and one of ten 20-character strings, and defines a SQL statement that inserts multiple rows into the database. After binding the tables, the program must place data for the first INSERT in ENAMES(1) and EMP_NOS(1), for the second INSERT in ENAMES(2) and EMP_NOS(2), and so forth. (This is not shown in the example.) Then OEXN is called to insert the data in the tables into the database.
DATA DIVISION.
WORKING STORAGE SECTION.
01 XTABLE1.
02 IND-PARAMS PIC S9(4) OCCURS 10 TIMES COMP.
02 ENAMES PIC X(20) OCCURS 10 TIMES.
02 EMP-NOS PIC S9(9) OCCURS 10 TIMES COMP.
77 SQL-STMT PIC X(50) VALUE "INSERT INTO EMP
-(ENAME, EMPNO) VALUES (:N, :E)".
77 ENAME-LEN PIC S9(9) VALUE 20 COMP.
77 TABLE-LEN PIC S9(9) VALUE 10 COMP.
77 PH1 PIC X(2) VALUE ":N".
77 PH2 PIC X(2) VALUE ":E".
77 SQL-STMT-LEN PIC S9(9) VALUE 48 COMP.
...
PROCEDURE DIVISION.
...
* Parse the statement.
CALL "OPARSE" USING CURSOR, SQL-STMT, SQL-STMT-LEN, ZERO.
* Bind the tables using OBNDRV.
* First, make sure that IND-PARAMS is zeroed.
PERFORM VARYING J FROM 1 BY 1
UNTIL J > 10
MOVE ZERO TO IND-PARAMS(J)
END PERFORM.
CALL "OBNDRV" USING CURSOR, PH1, TWO, ENAMES(1), ENAME-LEN,
ONE, ZERO, IND-PARAMS(1).
CALL "OBNDRV" USING CURSOR, PH2, TWO, EMP-NOS(1), INT-LEN,
THREE, ZERO, IND-PARAMS(1).
...
* After getting the data for the ENAMES and EMP-NOS tables,
* execute the statement, inserting the values in the
* tables into the Oracle emp table.
CALL "OEXN" USING CURSOR, TABLE-LEN, ZERO.
The completion status of OEXN is indicated in the return code field of the cursor data area. The rows processed count in the cursor data area indicates the number of rows successfully processed.
The rows processed count also returns the number of rows successfully processed before an error. If the SQL statement is processing only one row per table element and if the rows processed count is not equal to ITERS, the operation failed on table element rows processed count + 1.
You can continue to process the rest of the table even after a failure on one of the table elements as long as a rollback did not occur (obtained from the flags1 field in the cursor data area). You do this by using the zero-based ROWOFF parameter to start operations at table elements other than the first. In the above example, if the rows processed count was 5 at completion of OEXN, then row 6 was rejected. In this event, to continue the operation at row 7, call OEXN again as follows:
CALL "OEXN" USING CURSOR, TABLE-LEN, SIX.
Note: The maximum number of elements in a table used by OEXN is 32767.
Parameters
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
| ITERS | PIC S9(9) COMP | IN |
| ROWOFF | PIC S9(9) COMP | IN |
ITERS The size of the table of bind variables to be used. The size cannot be greater than 32767 items.
ROWOFF The zero-based offset within the bind variable table at which to begin operations. OEXN processes (ITERS - ROWOFF) elements if no error occurs.
See Also
OFEN fetches multiple rows into tables, taking advantage of the Oracle array interface.
SyntaxCALL "OFEN" USING CURSOR, NROWS.
Comments
OFEN is similar to OFETCH; however, OFEN fetches multiple rows into a table with a single call. The address of the table is bound to a select-list item in the SQL query statement using ODEFIN or ODEFINPS.
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 output variable 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 has been 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 complete program on the following pages shows how OFEN can be used to fetch rows from an Oracle database table, ten rows at a time.
IDENTIFICATION DIVISION.
PROGRAM-ID. OFEN-TEST.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 LDA.
03 LDA-V2RC PIC S9(4) COMP.
03 FILLER PIC X(10).
03 LDA-RC PIC S9(4) COMP.
03 FILLER PIC X(50).
01 CURSOR.
03 CURS-V2RC PIC S9(4) COMP.
03 CURS-TYPE PIC S9(4) COMP.
03 CURS-ROWS-PROCESSED PIC S9(9) COMP.
03 CURS-OFFS PIC S9(4) COMP.
03 CURS-FNC PIC X.
03 FILLER PIC X.
03 CURS-RC PIC S9(4) COMP.
03 FILLER PIC X(50).
01 HOST-DATA-AREA PIC X(512).
01 XNAMES.
03 NAME OCCURS 10 TIMES PIC X(15).
01 XSAL.
03 SALARY OCCURS 10 TIMES PIC S9(4)V99
COMP-3.
01 XEMPNO.
03 EMP-NO OCCURS 10 TIMES PIC S9(9)
COMP.
01 XINDS-NAME.
03 INDI-NAME OCCURS 10 TIMES PIC S9(4)
COMP.
01 XRCODE-NAME.
03 RET-CODE-NAME OCCURS 10 TIMES PIC S9(4)
COMP.
01 XRLEN-NAME.
03 RET-LEN-NAME OCCURS 10 TIMES PIC S9(4)
COMP.
01 XINDS-EMPNO.
03 INDI-EMPNO OCCURS 10 TIMES PIC S9(4)
COMP.
01 XRCODE-EMPNO.
03 RET-CODE-EMPNO OCCURS 10 TIMES PIC S9(4)
COMP.
01 XRLEN-EMPNO.
03 RET-LEN-EMPNO OCCURS 10 TIMES PIC S9(4)
COMP.
01 XINDS-SAL.
03 INDI-SAL OCCURS 10 TIMES PIC S9(4)
COMP.
01 XRCODE-SAL.
03 RET-CODE-SAL OCCURS 10 TIMES PIC S9(4)
COMP.
01 XRLEN-SAL.
03 RET-LEN-SAL OCCURS 10 TIMES PIC S9(4)
COMP.
01 USER-ID PIC X(11) VALUE "SCOTT".
01 USER-ID-L PIC S9(9) VALUE 5 COMP.
01 PASSWORD PIC X(5) VALUE "TIGER".
01 PASSWORD-L PIC S9(9) VALUE 5 COMP. 01 CONN PIC S9(9) VALUE 0 COMP. 01 CONN-L PIC S9(9) VALUE 0 COMP. 01 CONN-MODE PIC S9(9) VALUE 0 COMP.
01 FMT-SAL PIC X(6) VALUE "07.+02".
01 FMT-SAL-L PIC S9(9) VALUE 6 COMP.
01 SAL-T PIC S9(9) VALUE 7 COMP.
01 NAME-L PIC S9(9) VALUE 15 COMP.
01 NAME-T PIC S9(9) VALUE 96 COMP.
01 MINUS-ONE PIC S9(9) VALUE -1 COMP.
01 ZERO-A PIC S9(9) VALUE 0 COMP.
01 ONE PIC S9(9) VALUE 1 COMP.
01 TWO PIC S9(9) VALUE 2 COMP.
01 THREE PIC S9(9) VALUE 3 COMP.
01 FOUR PIC S9(9) VALUE 4 COMP.
01 TEN PIC S9(9) VALUE 10 COMP.
01 N-ROWS PIC 9(4) COMP.
01 N-DONE PIC 9(4) COMP.
01 INDX PIC 9(4) COMP.
01 DUMMY PIC X(3) VALUE " ".
01 SQL-STATEMENT PIC X(33) VALUE
"SELECT ENAME, EMPNO, SAL FROM EMP".
01 SQL-STATEMENT-L PIC S9(9) VALUE 33 COMP.
01 VERSION-7 PIC S9(9) VALUE 2 COMP.
01 MSGBUF PIC X(80).
01 MSGBUF-L PIC S9(9) VALUE 80 COMP.
01 ERR-RC PIC S9(4) COMP.
01 DEL-PARSE PIC S9(9) VALUE 1 COMP.
PROCEDURE DIVISION.
START-MAIN.
* Connect to Oracle in non-blocking mode.
* HDA must be initialized to zeros before call to OLOG.
MOVE LOW-VALUES TO HOST-DATA-AREA.
CALL "OLOG" USING LDA, HOST-DATA-AREA, USERNAME,
USERNAME-L, PASSWORD, PASSWORD-L, CONN,
CONN-L, CONN-MODE.
IF LDA-RC IN LDA NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-STOP.
DISPLAY " ".
DISPLAY "Connected to Oracle as user ", USER-ID.
DISPLAY " ".
* Open the cursor.
CALL "OOPEN" USING CURSOR, LDA, PASSWORD, PASSWORD-L,
MINUS-ONE, PASSWORD, PASSWORD-L.
IF CURS-RC IN CURSOR NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-STOP.
* Parse the SQL select statement.
CALL "OPARSE" USING CURSOR, SQL-STATEMENT,
SQL-STATEMENT-L, DEL-PARSE,
VERSION-7.
IF CURS-RC IN CURSOR NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-CLOSE.
* Define the three select-list items.
CALL "ODEFIN" USING CURSOR, ONE, NAME(1), NAME-L,
NAME-T, MINUS-ONE, INDI-NAME(1),
DUMMY, ZERO-A, ,RET-LEN-NAME(1),
RET-CODE-NAME(1).
CALL "ODEFIN" USING CURSOR, TWO, EMP-NO(1), FOUR,
THREE, MINUS-ONE, INDI-EMPNO(1),
DUMMY, ZERO-A, ZERO-A,
RET-LEN-EMPNO(1),RET-CODE-EMPNO(1).
CALL "ODEFIN" USING CURSOR, THREE, SALARY(1), FOUR,
SAL-T, MINUS-ONE, INDI-SAL(1),
FMT-SAL, FMT-SAL-L, SAL-T,
RET-LEN-SAL(1),
RET-CODE-SAL(1).
* Execute the SQL statement.
CALL "OEXN" USING CURSOR, ONE, ZERO-A.
* Perform the table fetches, 10 at a time. End when
* CURS-RC = 1403 (no more data found), or on an error.
MOVE 0 TO N-ROWS.
FETCH-LOOP.
CALL "OFEN" USING CURSOR, TEN.
IF (CURS-RC NOT = 1403 AND CURS-RC NOT = 0)
PERFORM ORA-ERROR
GO TO EXIT-CLOSE.
MOVE CURS-ROWS-PROCESSED TO N-DONE.
SUBTRACT N-ROWS FROM N-DONE.
ADD N-DONE TO N-ROWS.
DISPLAY "Employee name Number Salary RC(NAME)
- " RL(NAME)".
DISPLAY "------------------------------------------
- " ---------".
PERFORM DISPLAY-LINE THRU DISPLAY-LINE-EXIT
VARYING INDX FROM 1 BY 1 UNTIL INDX > N-DONE.
IF CURS-RC IN CURSOR = 0
GO TO FETCH-LOOP.
DISPLAY " ".
DISPLAY CURS-ROWS-PROCESSED WITH CONVERSION,
" rows processed.".
EXIT-CLOSE.
CALL "OCLOSE" USING CURSOR.
CALL "OLOGOF" USING LDA.
EXIT-STOP.
STOP RUN.
DISPLAY-LINE.
IF (RET-CODE-NAME(INDX) = 1405)
MOVE "NULL" TO NAME(INDX)
MOVE 0 TO SALARY(INDX).
DISPLAY NAME(INDX), EMP-NO(INDX) WITH CONVERSION,
SALARY(INDX) WITH CONVERSION,
RET-CODE-NAME(INDX) WITH CONVERSION, " ",
RET-LEN-NAME(INDX) WITH CONVERSION.
DISPLAY-LINE-EXIT.
ORA-ERROR.
IF LDA-RC IN LDA NOT = 0
DISPLAY "OLOGON error"
MOVE LDA-RC TO ERR-RC
MOVE 0 TO CURS-FNC
ELSE IF CURS-RC IN CURSOR NOT = 0
MOVE CURS-RC IN CURSOR TO ERR-RC
DISPLAY "Oracle error. Code is ",
ERR-RC WITH CONVERSION,
" Function is ", CURS-FNC WITH CONVERSION.
CALL "OERHMS" USING LDA, ERR-RC, MSGBUF, MSGBUF-L.
DISPLAY MSGBUF.
OFEN can be called repeatedly until there is no more data to fetch, indicated by a "no more data" return message. The completion status of OFEN is indicated in the return code field of the cursor data area. The rows processed count field in the cursor data area 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 | (Address) | IN/OUT |
| NROWS | PIC S9(9) COMP | IN |
NROWS The size of the defined variable table on which to operate. The size cannot be greater than 32767 items. If NROWS is set to 1, OFEN acts effectively just like OFETCH.
See Also
ODEFIN, ODEFINPS, OEXFET, OFETCH, OPARSE.
OFETCH returns rows of a query to the user program, one row at a time.
SyntaxCALL "OFETCH" USING CURSOR.
Comments
Each select-list item of the query is placed into a buffer identified by a previous ODEFIN call.
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 output variable 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 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.
The following example shows how you can obtain data from Oracle using OFETCH on a query statement. This example continues the one shown in the description of the ODEFIN routine 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:
PROCEDURE DIVISION.
...
* Bind the output buffers to the select-list items.
CALL "ODEFIN" USING CURSOR, ONE, EMP-NAME, EMP-NAME-L, CHAR-TYPE,
ZERO, INDP, FMT, ZERO, FMT-T, RET-LEN1, RET-CODE1.
CALL "ODEFIN" USING CURSOR, TWO, EMP-NUM, EMP-NUM-L, INT-TYPE,
ZERO, INDP, FMT, ZERO, FMT-T, RET-LEN2, RET-CODE2.
CALL "ODEFIN" USING CURSOR, THREE, SAL, SAL-L, FMT-T,
TWO, INDP, FMT, FMT-L, FMT-T, RET-LEN3, RET-CODE3.
* Execute the query.
CALL "OEXEC" USING CURSOR.
* Fetch each row of the query.
PERFORM UNTIL C-RC IN CURSOR NOT = 0
CALL "OFETCH" USING CURSOR.
* Check the return code for the first column.
* Was a NULL value returned?
IF RET-CODE1 = 1405
DISPLAY "NULL " NO ADVANCING
ELSE
DISPLAY EMP-NAME NO ADVANCING.
* Check the second column return code.
IF RET-CODE2 = 1405
DISPLAY "NULL " NO ADVANCING
ELSE
DISPLAY EMP-NUM NO ADVANCING.
IF RET-CODE3 = 1405
* Check the third column code.
DISPLAY "NULL ".
ELSE
DISPLAY SAL.
END PERFORM.
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 cursor data area is incremented.
There is no way to 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 will return a "no data found" return code. When this happens, rows processed count contains the total number of rows recovered by the query.
Parameter
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
See Also
ODEFIN, ODEFINPS, ODESCR, OEXEC, OEXFET, OFEN.
OFLNG fetches a portion of a LONG or LONG RAW column.
SyntaxCALL "OFLNG" USING CURSOR, POS, BUF, BUFL, DTYPE, RETL, OFFSET.
Comments
In Oracle7, LONG and LONG RAW columns can hold up to 2 gigabytes of data. The OFLNG procedure allows you to fetch up to 64K bytes, starting at any offset, in 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 PIC S9(9) COMP, 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 PIC S9(9) COMP in the interface is for future enhancements.
Before calling OFLNG to retrieve the portion of the LONG column, you must do one or more fetches to position the cursor at the desired row.
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 example below shows how to retrieve 64 Kbytes, starting at offset 70000, from a LONG RAW column. See also the third code example in Appendix B for a complete demonstration program that uses OFLNG.
DATA DIVISION.
WORKING STORAGE SECTION.
77 SQL-STMT PIC X(50) VALUE "SELECT id_no
-FROM data_table1 WHERE id_no = :1".
77 ONE PIC S9(9) VALUE 1 COMP.
77 DATA-BUF PIC X(65536).
77 CHAR-TYPE PIC S9(9) VALUE 1 COMP.
77 RET-L PIC S9(4) COMP.
77 OFFSET PIC S9(9) VALUE 70000 COMP.
77 DB-L PIC S9(9) VALUE 65536 COMP.
77 SQL-STMT-L PIC S9(9) VALUE 45 COMP.
77 LONG-POS PIC S9(9) VALUE 2 COMP.
...
PROCEDURE DIVISION.
...
* Parse the statement. Define a variable for id_no
* Then do a fetch of one row to position the cursor.
...
CALL "OEXFET" USING CURSOR, ONE, ZERO, ONE.
DISPLAY "ROW ID NUMBER IS", ID_NO.
...
* Get the portion of the column.
CALL "OFLNG" USING CURSOR, LONG-POS, DATA-BUF, DB-L,
CHAR-TYPE, RET-L, OFFSET.
Parameters
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
| POS | PIC S9(4) COMP | IN |
| BUF | (Address) | OUT |
| BUFL | PIC S9(9) COMP | IN |
| DTYPE | PIC S9(9) COMP | IN |
| RETL | PIC S9(9) COMP | OUT |
| OFFSET | PIC S9(9) COMP | IN |
POS The index position of the LONG-type column. The first position 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.
BUF 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 datatype of BUF. See "External Datatypes" for a list of datatype codes.
RETL The number of bytes returned. If more than 65535 bytes were requested and returned, the value 65535 is returned in this parameter.
OFFSET Zero-based offset of the first byte in the LONG column to be fetched.
See Also
OGETPI returns information about the next chunk of data to be processed as part of a piecewise insert, update or fetch.
SyntaxCALL "OGETPI" USING CURSOR, PIECEP, CTXPP, ITERP, 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.
For a sample C language program illustrating the use of OGETPI in an OCI program which performs an piecewise insert, see the description of the ogetpi() call.
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
| PIECEP | PIC S9(2) COMP | OUT |
| CTXPP | PIC S9(9) COMP | OUT |
| ITERP | PIC S9(9) COMP | OUT |
| INDEXP | PIC S9(9) COMP | 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 one (for the first piece) and two (for a subsequent piece) for a fetch or insert, and three (for the last piece) for fetches only. These values are defined in ocidfn.h as OCI_FIRST_PIECE, OCI_NEXT_PIECE, and OCI_LAST_PIECE.
CTXPP 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 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
OLOG establishes a connection between an OCI program and an Oracle database.
SyntaxCALL "OLOG" USING LDA, HDA, UID, UIDL, [PASSWD], [PASSWDL], [CONN], [CONNL], CONN-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 COBOL this can be accomplished through the use of the compiler-generated figurative constant LOW-VALUES. See the sample code below for an example.
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.
When an OCI program issues 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 page 2 - 32. For a short example program in C, see the onbset() description on page 4-67.
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.
The following code fragment demonstrates a typical set of declarations and initializations for a call to OLOG.
DATA DIVISION
01 LDA.
02 LDA-V2RC PIC S9(4) COMP.
02 FILLER PIC X(10).
02 LDA-RC PIC S9(4) COMP.
02 FILLER PIC X(50).
01 HDA PIC X(512).
...
77 USER-ID PIC X(5) VALUE "SCOTT".
77 USER-ID-L PIC S9(9) VALUE 5 COMP.
77 PSW PIC X(5) VALUE "tiger".
77 PSW-L PIC S9(9) VALUE 5 COMP.
77 CONN PIC S9(9) VALUE 0 COMP.
77 CONN-L PIC S9(9) VALUE 0 COMP.
77 CONN-MODE PIC S9(9) VALUE 0 COMP.
...
PROCEDURE DIVISION
...
* CONNECT TO ORACLE IN NON-BLOCKING MODE.
* CONN-MODE = ZERO INDICATES NON-BLOCKING CONNECTION.
* HDA MUST BE INITIALIZED TO ZEROS BEFORE CALL TO OLOG.
MOVE LOW-VALUES TO HDA.
CALL "OLOG" USING LDA, HDA, USER-ID, USER-ID-L,
PSW, PSW-L, CONN, CONN-L, CONN-MODE.
IF LDA-RC NOT = 0
PERFORM ORA-ERROR
GO TO EXIT-STOP
DISPLAY "Connected to ORACLE as user ", USER-ID.
...Parameters
| Parameter Name | Type | Mode |
| LDA | (Address) | IN/OUT |
| HDA | PIC X(256) | OUT |
| UID | PIC X(n) | IN |
| UIDL | PIC S9(9) COMP | IN |
| PSWD | PIC X(n) | IN |
| PSWDL | PIC S9(9) COMP | IN |
| CONN | PIC X(n) | IN |
| CONNL | PIC S9(9) COMP | IN |
| CONN-MODE | PIC S9(9) COMP | IN |
HDA A host data area. See Chapter 2 for more information on host data areas.
UID A string containing the user ID, 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 user ID and separate it from the user ID with a '/'. Put the host system identifier after the password or user ID, separated by the '@' sign.
If you do not include the password in this parameter, it must be in the PSWD parameter. Examples of valid UID strings are
NAME
NAME/PASSWORD
NAME@SERVICENAME
NAME/PASSWORD@SERVICENAME
The following string is not a correct example of the USERID parameter:
NAME@SERVICENAME/PASSWORD
UIDL The length of the UID string.
PSWD A string containing the password. If the password is specified as part of the UID string, this parameter can be omitted.
PSWDL The length of the PSWD parameter.
CONN A string containing a SQL*Net V2 connect descriptor to connect to a database. If the connect descriptor is specified as part of the UID string, this parameter can be omitted.
CONNL The length of the CONN parameter.
CONN-MODE Specifies whether the connection is in blocking or non-blocking mode. Possible values are zero (for blocking) or one (for non-blocking).
See Also
OLOGOF disconnects an LDA from the Oracle program global area and frees all Oracle resources owned by the Oracle user process.
SyntaxCALL "OLOGOF" USING LDA.
Comments
A COMMIT is automatically issued on a successful OLOGOF call; all currently opened cursors are closed. If a program logs off unsuccessfully or terminates abnormally, all outstanding transactions are rolled back.
If the program has multiple active logons, a separate call to 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 | (Address) | IN/OUT |
See Also
OLOG.
ONBCLR places a database connection in non-blocking mode.
SyntaxCALL "ONBCLR" USING LDA.
Comments
If there is a pending call on a connection and ONBCLR is called, the pending call, when resumed, will block.
Parameters
| Parameter Name | Type | Mode |
| LDA | (Address) | IN |
See also
ONBSET places a database connection in non-blocking mode for all subsequent OCI calls on this connection.
SyntaxCALL "ONBSET" USING LDA.
Comments
ONBSET will succeed if the library is linked in deferred mode and if the network driver supports non-blocking operations.
Note: This call also requires SQL*Net Release 2.1 or higher. It is not compatible with a single-task driver.
Parameters
| Parameter Name | Type | Mode |
| LDA | (Address) | IN |
See also
ONBTST tests whether a database connection is in non-blocking mode.
SyntaxCALL "ONBTST" USING LDA.
Comments
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 | (Address) | IN |
See also
OOPEN opens the specified cursor.
SyntaxCALL "OOPEN" USING CURSOR, LDA, <DBN>, <DBNL>, <ARSIZE>, <UID>, <UIDL>.
Comments
OOPEN associates a cursor data area in the program with data areas in the Oracle Server. Oracle uses these areas to maintain state information about the processing of a SQL statement. Status concerning error and warning conditions, as well as other information, such as function codes, is returned to the cursor data area in your program as Oracle processes the SQL statement.
An OCI program can have many cursors active at the same time.
The OPARSE routine is used to parse a SQL statement and associate it with a cursor. In the OCI routines, SQL statements are always referenced using a cursor as the handle.
The return code field of the cursor data area indicates the result of the OOPEN. A return code value of zero indicates a successful OOPEN call.
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.
See the description of the OPARSE routine in this chapter for an example that uses the OOPEN routine.
Parameters
| Parameter Name | Type | Mode |
| CURSOR | (Address) | OUT |
| LDA | (Address) | IN/OUT |
| DBN | PIC X(n) | IN |
| DBNL | PIC S9(9) COMP | IN |
| ARSIZE | PIC S9(9) COMP | IN |
| UID | PIC X(n) | IN |
| UIDL | PIC S9(9) COMP | IN |
LDA 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 is not used in later versions.
DBNL This parameter is included only for Oracle Version 2 compatibility. It is not used in later versions.
ARSIZE The ARSIZE (areasize) parameter is no longer used in Oracle7, as the data areas used by cursors in the Oracle Server are resized automatically as required.
UID A character string containing the user ID and the password. The password must be separated from the user ID by a '/'.
If the connection to Oracle was established using the Version 2 OLOGON call, then UID and UIDL are used in the OOPEN call. If the OLON routine was used, UID and UIDL are ignored in the OOPEN call.
UIDL The length of the UID parameter.
See Also
OOPT is used to set rollback options for non-fatal Oracle errors involving multi-row INSERT and UPDATE SQL statements. It is also used to set wait options in cases where requested resources are not available; for example, whether to wait for locks.
SyntaxCALL "OOPT" USING CURSOR, RBOPT, WAITOPT.
Comments
The RBOPT parameter is not supported in Oracle Server Version 6 or later.
Parameters
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
| RBOPT | PIC S9(9) COMP | IN |
| WAITOPT | PIC S9(9) COMP | IN |
RBOPT The action to be taken when a non-fatal Oracle error occurs. If this option is set to 0, 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 continue without them if they are currently not available. If this option is set to 0, the program waits indefinitely if resources are not available. This is the default. If this option is set to 4, the program will receive an error return code whenever a resource is requested but is not available. 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 parses a SQL statement or a PL/SQL block and associates it with a cursor. The parse can optionally be deferred.
SyntaxCALL "OPARSE" USING CURSOR, SQLSTM, SQLL, DEFFLG, 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.
If OPARSE is used to parse a query, the fetch call returns a "fetched column value was truncated" if a column value was truncated and no indicator parameter was defined for that column using ODEFIN.
Note: When OPARSE is called with the DEFFLG parameter set, 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 call to OPARSE associates the SQL statement with the cursor.
DATA DIVISION.
WORKING STORAGE SECTION.
77 SQL-STMT PIC X(50) VALUE "SELECT ename
- " FROM emp WHERE deptno = 20".
PROCEDURE DIVISION.
...
* Open the cursor.
CALL "OOPEN" USING CURSOR, LDA, DBN, DBN-L,
AREASIZE, USER-ID, USER-ID-L.
* Parse the statement. Set the deferred flag, and the version
* flag to one, indicating "native" database version (V6 or Oracle7).
* The deferred parse flag is ignored if the database is V6..
CALL "OPARSE" USING CURSOR, SQL-STMT, SQL-STMT-L, ONE, ONE.
...
SQL syntax error codes are returned in the cursor data area's return code field and parse error offset field. Parse error offset indicates the location of the error in the SQL statement text. See "Cursor Data Area" for a list of the information fields available in the cursor data area after an OPARSE call.
Parameters
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN/OUT |
| SQLSTM | PIC X(n) | IN |
| SQLL | PIC S9(9) COMP | IN |
| DEFFLG | PIC S9(9) COMP | IN |
| LNGFLG | PIC S9(9) COMP | IN |
SQLSTM A string containing a SQL statement.
SQLL The length of the SQL statement in bytes.
DEFFLG
If non-zero, the parse of the SQL statement is deferred until an ODESCR, OEXEC, OEXN, or OEXFET call is made. Note that bind and define operations are also deferred until the execute or describe step if the program was linked using the deferred mode option. See "Deferred Statement Execution" for more information about 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.
LNGFLG The LNGFLG parameter determines the way that 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 Version 6. The table below shows the differences between Version 6 and Oracle7:
| Behavior | V6 | V7 |
| CHAR columns are fixed length (including created by a CREATE TABLE statement). | NO | YES |
| An error is issued if an attempt is made to fetch a null value 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 indicator variable. | YES | NO |
| Describe (ODESCR) returns internal datatype 1 for CHAR columns. | YES | NO |
| Describe (ODESCR) returns internal datatype 96 for CHAR columns. | n/a | YES |
| 0 | Specifies Version 6 behavior (the database to which you are connected can be either Version 6 or Oracle7). |
| 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 a V6 database |
ODESCR, OEXEC, OEXFET, OEXN, OOPEN.
OROL rolls back the current transaction.
SyntaxCALL "OROL" USING 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 | (Address) | IN/OUT |
See Also
OSETPI sets information about the next chunk of data to be processed as part of a piecewise insert, update or fetch.
SyntaxCALL "OSETPI" USING CURSOR, PIECE, BUFP, 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.
Comments
An OCI application uses OSETPI to set the information about the next piecewise insert, update, or fetch. The BUFP parameter is 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.
For a sample C language program illustrating the use of OSETPI to perform a piecewise fetch, see the description of the osetpi() routine.
| Parameter Name | Type | Mode |
| CURSOR | (Address) | IN |
| PIECE | PIC S9(2) COMP | IN |
| BUF | PIC S9(9) COMP | IN |
| LENP | PIC S9(9) COMP | IN/OUT |
PIECE Specifies the piece being provided or fetched. Possible values are one (for the first piece), two (for a subsequent piece) or three (for the last piece). These values are defined in ocidfn.h as OCI_FIRST_PIECE, OCI_NEXT_PIECE, and OCI_LAST_PIECE. Relevant when the buffer is being set after error ORA-03129 was returned by a call to OEXEC.
BUFP 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 buffer to hold the next piece to be retrieved.
LENP 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
The 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 an LDA, according to the connection information passed to it.
SyntaxCALL "SQLLD2" USING LDA, CNAME, 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 the CNLEN parameter is set to zero, an LDA for the default connection is returned. Your program must declare the LDA, then pass it as a parameter.
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 that it must be invoked after XA_OPEN and XA_START, and before XA_END. Otherwise and 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 below demonstrates how you can use SQLLD2 to obtain a valid LDA for a specific connection:
DATA DIVISION.
WORKING STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USER-ID PIC X(20).
01 PASSWORD PIC X(20).
01 DB-STRING-1 PIC X(14) VALUE "D:NEWYORK".
01 DB-STRING-2 PIC X(17) VALUE "D:LOSANGELES".
EXEC SQL END DECLARE SECTION END-EXEC.
...
01 LDA-1.
02 LDA-RC PIC S9(4) COMP.
02 FILLER PIC X(10).
02 LDA-V4RC PIC S9(4) COMP.
02 FILLER PIC X(50).
01 LDA-2.
02 LDA-RC PIC S9(4) COMP.
02 FILLER PIC X(10).
02 LDA-V4RC PIC S9(4) COMP.
02 FILLER PIC X(50).
...
PROCEDURE DIVISION.
...
* Do the first connection.
MOVE "SCOTT" TO USER-ID.
MOVE "TIGER" TO PASSWORD.
EXEC SQL DECLARE DBN1 DATABASE END-EXEC.
EXEC SQL CONNECT :USER-ID IDENTIFIED BY :PASSWORD
AT DBN1 USING :DB-STRING-1 END-EXEC.
* Get the LDA for the first connection.
CALL "SQLLDA" USING LDA-1.
* Do the second connection.
EXEC SQL DECLARE DBN2 DATABASE END-EXEC.
EXEC SQL CONNECT :USER-ID IDENTIFIED BY :PASSWORD
AT DBN2 USING :DB-STRING-2 END-EXEC.
* Get the LDA for the second connection.
CALL "SQLLDA" USING LDA-2.
Parameters
| Parameter Name | Type | Mode |
| LDA | (Address) | OUT |
| CNAME | PIC X(n) | IN |
| CNLEN | PIC S9(9) COMP | IN |
CNAME The name of the database connection. If the name consists of all blanks, SQLLD2 returns the LDA for the default connection.
CNLEN The length of the CNAME parameter. If CNLEN is passed as zero, SQLLD2 returns the LDA for the default connection, regardless of the contents of CNAME.
SQLLDA is part of the SQLLIB library that is used with the Oracle Precompilers. This routine is provided for programs that mix both precompiler code and OCI calls. An address of an LDA is passed to SQLLDA; the precompiler fills in the required fields in the LDA.
SyntaxCALL "SQLLDA" USING LDA.
Comments
If your program contains both precompiler statements and calls to OCI routines, you cannot use OLOG to log on to Oracle. You must use the embedded SQL command
EXEC SQL CONNECT ...
to log on. However, many OCI routines require a valid LDA. The SQLLDA routine 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, you should call SQLLDA immediately after doing the connect with the EXEC SQL CONNECT ... statement.
The example below demonstrates how you can do multiple remote logons in a mixed Precompiler-OCI program. Refer to Chapter 3 in the Programmer's Guide to the Oracle Precompilers, R1.5 for additional information about multiple remote logons.
DATA DIVISION.
WORKING STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USER-ID PIC X(20).
01 PASSWORD PIC X(20).
01 DB-STRING-1 PIC X(14) VALUE "D:NEWYORK".
01 DB-STRING-2 PIC X(17) VALUE "D:LOSANGELES".
EXEC SQL END DECLARE SECTION END-EXEC.
...
01 LDA-1.
02 LDA-RC PIC S9(4) COMP.
02 FILLER PIC X(10).
02 LDA-V4RC PIC S9(4) COMP.
02 FILLER PIC X(50).
01 LDA-2.
02 LDA-RC PIC S9(4) COMP.
02 FILLER PIC X(10).
02 LDA-V4RC PIC S9(4) COMP.
02 FILLER PIC X(50).
...
PROCEDURE DIVISION.
...
* Do the first connection.
MOVE "SCOTT" TO USER-ID.
MOVE "TIGER" TO PASSWORD.
EXEC SQL DECLARE DBN1 DATABASE END-EXEC.
EXEC SQL CONNECT :USER-ID IDENTIFIED BY :PASSWORD
AT DBN1 USING :DB-STRING-1 END-EXEC.
* Get the LDA for the first connection.
CALL "SQLLDA" USING LDA-1.
* Do the second connection.
EXEC SQL DECLARE DBN2 DATABASE END-EXEC.
EXEC SQL CONNECT :USER-ID IDENTIFIED BY :PASSWORD
AT DBN2 USING :DB-STRING-2 END-EXEC.
* Get the LDA for the second connection.
CALL "SQLLDA" USING LDA-2.
Parameter
| Parameter Name | Type | Mode |
| LDA | (Address) | OUT |
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |