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" . 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 selec