Programmer's Guide to the Oracle7 Server Call Interface Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

The OCI Routines for COBOL


This chapter describes each subroutine in the OCI library for the COBOL OCI programmer. For all but the most simple routines, an example shows how a COBOL OCI program uses the routine. The description of each routine has five parts:

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.


Calling OCI Routines

This section describes data structures and coding rules that are specific to applications written in COBOL. Refer to this section for information about data structures, datatypes, and parameter passing conventions in the COBOL call interface.

COBOL Data Areas

To use the OCI routines, you must declare data structures for one or more LDAs and CDAs.

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.

COBOL Parameter Types

Parameters for the OCI routines are of three types:

Address parameters pass the address of a variable in your program to Oracle. In COBOL, all parameters are normally passed to a subprogram by reference, so you simply pass all address parameters as you would normally pass any other parameter.

Integer parameters in OCI COBOL are in the format PIC S9(2) COMP, PIC S9(4) COMP, referred to in the other chapters of this guide as a "short integer," or in the format PIC S9(9) COMP, referred to elsewhere as "integer." All parameters should be passed by reference, which is the default parameter passing mechanism in COBOL. The OCI libraries will correctly dereference these parameters.

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.

COBOL Parameter Classification

There are three kinds of parameters in the USING phrase of an OCI subroutine:

Required Parameters

Required parameters are used by Oracle and must be supplied by the program. You must pass a valid value for each required parameter. If you do not supply a required parameter, your program will behave unpredictably.

Optional Parameters

Optional parameters are those that may or may not be used by Oracle, depending on the requirements of your program. Depending on your compiler, you can omit an optional parameter if your program does not need it. For example, you might decide that you do not want to supply an indicator variable on a bind call, because all your input values must be non-null. The INDP indicator parameter in the bind routines OBNDRV and OBNDRN is optional. In the Syntax section for each routine in this chapter, optional parameters are surrounded by square brackets ([ ]).

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.

Unused Parameters

Unused parameters are not used by Oracle, at least for the language being discussed. For example, the OCI logon routines specify an unused parameter called AUDIT. Unused parameters are passed in the same way as omitted optional parameters. In the syntax descriptions in this chapter, unused parameters are surrounded by angle brackets (<>).

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.

Parameter Descriptions

In this chapter, parameters for the OCI routines are described in terms of their type and their mode. The type is either "Address", PIC X(n), PIC S9(4) COMP or PIC S9(4) COMP-5, PIC S9(9) COMP, or PIC S9(9) COMP-5. The mode of a parameter has three possible values:

IN

A parameter that passes data to Oracle.

OUT

A parameter that receives data from Oracle on this 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.

Linking COBOL OCI Programs

Check your Oracle system-specific documentation for additional information about linking COBOL OCI programs. It may be necessary to include extra libraries for linking on some platforms.

OBINDPS

Purpose

OBINDPS associates the address of a program variable with a placeholder in a SQL or PL/SQL statement. Unlike older OCI bind calls, OBINDPS can be used to bind placeholders to be used in piecewise operations, or operations involving arrays of structures.

Syntax

CALL "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:

Unlike older OCI calls, OBINDPS does not accept -1 for any optional or unused parameters. When it is necessary to pass a value to these parameters NULL or 0 should be used instead.

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
Note: Since the OBINDPS call can be used in a variety of different circumstances, some items in the following list of parameter descriptions include different explanations for how the parameter is used for piecewise operations, arrays of structures and standard scalar or array binds.

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

Purpose

OBNDRA binds the address of a program variable or array to a placeholder in a SQL statement or PL/SQL block.

Syntax

CALL "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 1. PROGV is the address of the program variable.

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 OBNDRV

Purpose

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.

Syntax

CALL "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
Note 1. This is an IN parameter for the OEXEC routine.

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

Purpose

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.

Syntax

CALL "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
LDA The LDA specified in the OLOG call that was used to make this connection to Oracle.

See Also

OLOG.

OCAN

Purpose

OCAN cancels a query after the desired number of rows have been fetched.

Syntax

CALL "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
CURSOR The address of the cursor data area specified in the OPARSE call associated with the query.

See Also

OEXFET, OFEN, OFETCH, OPARSE.

OCLOSE

Purpose

OCLOSE disconnects a cursor from the data areas associated with it in the Oracle Server.

Syntax

CALL "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
CURSOR The cursor data area specified in the OOPEN call.

See Also

OOPEN, OPARSE.

OCOF

Purpose

OCOF disables autocommit, that is, automatic commit of every SQL data manipulation statement.

Syntax

CALL "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
LDA The LDA specified in the OLOG call that was used to make this connection to Oracle.

See Also

OCOM, OCON, OLOG.

OCOM

Purpose

OCOM commits the current transaction.

Syntax

CALL "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
LDA The LDA specified in the OLOG call that was used to make this connection to Oracle.

See Also

OCON, OLOG, OLOGOF, OROL.

OCON

Purpose

OCON enables autocommit, that is, automatic commit of every SQL data manipulation statement.

Syntax

CALL "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
LDA The LDA specified in the OLOG call that was used to make this connection to Oracle.

See Also

OCOF, OCOM, OLOG.

ODEFIN

Purpose

ODEFIN defines an output buffer for a specified select-list item in a SQL query.

Syntax

CALL "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