Programmer's Guide to the Oracle7 Server Call Interface

Library

Product

Contents

Index

The OCI Routines for FORTRAN

This chapter describes each subroutine in the OCI library for the OCI FORTRAN programmer. The description of many of the functions includes an example that shows how the routine can be used in an OCI program. Examples are omitted for the simpler subroutines. 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 specific description of each parameter.

See Also

Other routines that affect or are used with this routine.

Be sure to read "Calling OCI Routines" in this chapter. It contains important information about data structures, datatypes, parameter passing conventions, and other important information about the OCI routines.

Calling OCI Routines

This section describes data structures and coding rules that are specific to applications written in FORTRAN. Refer to this section for information about data structures, datatypes, and parameter passing conventions for FORTRAN OCI programs.

Data Structures

To use the OCI routines, you must declare data structures for one or more LDAs and CDAs. In the examples in this Guide, these data areas are declared as INTEGER*2 arrays of 32 elements (total of 64 bytes) as follows:

INTEGER*2    LDA(32)

INTEGER*2    CURSOR(32)

The offsets of elements in these structures are system dependent. See your Oracle system-specific documentation for the size and alignments of the CDA and LDA components on your system.

If your FORTRAN compiler supports STRUCTURE declarations, you might find it more convenient to define STRUCTUREs for the CDA and the LDA, following the listings and Appendix A. Note, however, that the size and offsets of the LDA and CDA still depend on how the C compiler on your system aligns structure elements, since the underlying library structures are defined in C.

FORTRAN Parameter Types

Parameters for the OCI routines are of three types:

Address

Integer (INTEGER*1, INTEGER*2, INTEGER*4)

Character string (CHARACTER*n)

Address parameters pass the address of a variable in your program to Oracle. Although the concept of the address of a variable is alien in many FORTRAN contexts, it is important when using the OCIs. In FORTRAN, 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. Note that all OUT parameters are address parameters.

However, some FORTRAN compilers do not pass all parameters by reference. For example, Digital Equipment Corporation VAX/VMS compilers pass CHARACTER variables using descriptors. These compilers provide a mechanism (%REF() in the VAX/VMS case) to force passing by reference. You must use this override mechanism to make sure that all address parameters are passed as variable addresses.

Integer parameters are normally four bytes (INTEGER*4). Where two-byte or one-byte integers are required, this is noted in the parameters section. Pass all integer parameters as you would normally pass any other parameter. The OCI library routines will correctly dereference these parameters.

Warning: Even if your FORTRAN compiler supports call by value, do not pass integer parameters by value.

Character strings are a special type of parameter. A length parameter must be specified for character strings. Length parameters for strings are INTEGER*4 variables specifying the length in bytes of the character string. In the example code in the text, the function LEN_TRIM is used extensively to return the length of a character string, minus any trailing blanks. An example implementation of this function can be found in the sample programs in Appendix C.

FORTRAN Parameter Classification

There are three kinds of parameters in the parameter list of an OCI subroutine:

required parameters

optional parameters

unused parameters

Required Parameters

Required parameters are used by Oracle, and the OCI program must supply a valid value for each required parameter.

Optional Parameters

The use of optional parameters depends on the requirements of your program. The Syntax section for each routine in this chapter indicates optional parameters using square brackets ([ ]).

Unused Parameters

Unused parameters are not used by Oracle, at least for the language being discussed. For example, for cross-language compatibility some OCI functions can take the parameters FMT, FMTL, and FMTT. These are the format string specifier for a packed decimal external datatype, and the string length and type parameters. The packed decimal type is used mainly by COBOL programs, so these parameters are unused in FORTRAN. In the Syntax sections in this chapter, unused parameters are surrounded by angle brackets (< >).

If the optional or unused parameter is an INTEGER*2 or an INTEGER*4, and is not an address parameter, you can declare a variable for the parameter, code a -1 value in it, and pass it normally. In this case, you must pass the parameter by reference.

Note: A value of -1 should not be passed for unused or 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.

If a 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 passing parameters by value, or if you can physically omit items in the parameter list.

For example, you can indicate to Oracle that the INDP parameter is being omitted using the VAX/VMS compiler as follows:

  CALL OBNDRV(CURSOR, PHNAME, PHNAML, PRGVAR,

  1PRGVL, FTYPE, SCALE, %VAL(-1), FMT, FMTL, FMTT)

where a -1 is passed by value in place of the INDP parameter.

In summary, if your compiler does not support missing parameters (, ,) or passing parameters by value, you cannot omit an address parameter. In this case, you should either make sure that the value in the parameter will not cause unforeseen actions (put a 0 in the INDP parameter on an OBNDRV or OBNDRN call) or ignore a returned value (if you do not need it, ignore the value in the INDP parameter of ODEFIN after an OFETCH call).

In the code examples in this chapter, optional parameters are always passed. Compiler-specific mechanisms, such as passing by value or omitting parameters, are not used.

Unused parameters are passed in the same way as omitted optional parameters.

Refer to the description of the OBNDRN routine for more examples of how to pass optional and unused parameters.

FORTRAN Parameter Descriptions

In this chapter, parameters for the OCI routines are described in terms of their type and their mode. The type is normally either INTEGER*2 , INTEGER*4, CHARACTER*n, or arrays of these types. In the few cases where an OUT parameter may be of any type, the type is listed as (ADDRESS). In that case, you simply pass a buffer of the appropriate size. The mode of a parameter has three possible values:

A parameter that passes data to Oracle.

OUT

A parameter that receives data from Oracle on this or a subsequent call.

IN/OUT

A parameter that passes data on the call, and receives data on the return from this call or from a subsequent call.

Linking FORTRAN OCI Programs

Check your Oracle system-specific documentation for additional information about linking FORTRAN 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(CURSOR, OPCODE, SQLVAR, [SQLVL], PVCTX,
	PROGVL, FTYPE, [SCALE], [INDP], [ALENP], [RCODEP],
	PVSKIP, INDSKIP, ALENSKIP, RCSKIP, [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:

OBINDPS is supported only when a program is linked in deferred mode. If it is necessary to link in non-deferred mode, another bind routine must be used. In this case, the ability to handle piecewise operations and arrays of structures is not supported.

OBINDPS is more complex than the older bind calls. Users who are not performing piecewise operations and are not using arrays of structures may choose to use one of the older routines.

OBINDPS does not support the ability to do a positional bind. If this functionality is needed, the bind should be performed using OBNDRN.

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

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 INTEGER*2(32) IN/OUT
OPCODE INTEGER*1 IN
SQLVAR CHARACTER*n IN
SQLVL INTEGER*4 IN
PVCTX INTEGER*1 IN
PROGVL INTEGER*4 IN
FTYPE INTEGER*4 IN
SCALE INTEGER*4 IN
INDP INTEGER*2 IN/OUT
ALENP INTEGER*2 IN
RCODEP INTEGER*2 OUT
PVSKIP INTEGER*4 IN
INDSKIP INTEGER*4 IN
ALENSKIP INTEGER*4 IN
RCSKIP INTEGER*4 IN
MAXSIZ INTEGER*4 IN
CURSIZ INTEGER*4 IN/OUT
FMT CHARACTER*6 IN
FMTL INTEGER*4 IN
FMTT INTEGER*4 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 Not normally used in FORTRAN. See the description of OBNDRV for more information about this parameter.

INDP Pointer to an indicator variable or array of indicator variables. For arrays of structures this may be an interleaved array of column-level indicator variables. See page 2 - 29 for more information about indicator variables.

ALENP Piecewise bind: pass as 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).

PVSKIP 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.

INDSKIP 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).

ALENSKIP 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).

RCSKIP 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 Not normally used in FORTRAN. See the description of OBNDRV for more information about this parameter.

FMTL Not normally used in FORTRAN. See the description of OBNDRV for more information about this parameter.

FMTT Not normally used in FORTRAN. See the description of OBNDRV for more information about this parameter.

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(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 newer 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 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 arrays in a FORTRAN program to tables in a PL/SQL block.

    IMPLICIT INTEGER*4 (A-Z)

* Logon and CDA, plus connect strings

    INTEGER*2      CDA(32), LDA(32)
    INTEGER*2      HDA(256)

    CHARACTER*20   UID, PWD

* Variables for OBNDRA

    INTEGER*2      DSALEN(3), DSRC(3)

    INTEGER*4      DSCS

    INTEGER*2      PNALEN(3), PNRC(3)

    INTEGER*4      PNCS

* Input variables

    CHARACTER*20   DESCRP(3)

    INTEGER*4      PRTNOS(3)

* Placeholders

    CHARACTER*10   DSPH, PNPH

* Variables to hold SQL statements

    CHARACTER*20   DRPTBL, CRTTBL

    CHARACTER*500  CRTPKG, PKGBDY, PLSBLK

* Initialize all variables

    UID = 'scott'

    PWD = 'tiger'

    DESCRP(1) = 'Frammis'

    DESCRP(2) = 'Widget'

    DESCRP(3) = 'Thingie'

    PRTNOS(1) = 12125

    PRTNOS(2) = 23169

    PRTNOS(3) = 12126

    DSPH = ':DESC'

    PNPH = ':PARTS'

    DRPTBL = 'DROP TABLE part_nos'

    CRTTBL = 'CREATE TABLE part_nos

   +          (partno NUMBER(8), description CHAR(20))'

    CRTPKG = 'CREATE OR REPLACE PACKAGE update_parts AS

   +             TYPE part_number IS TABLE OF part_nos.partno%TYPE

   +                 INDEX BY BINARY_INTEGER;

   +             TYPE part_description IS TABLE OF

   +                 part_nos.description%TYPE

   +                 INDEX BY BINARY_INTEGER;

   +             PROCEDURE add_parts (n IN INTEGER,

   +                                  descrip IN part_description,

   +                                  partno INpart_number);

   +           END update_parts;'

    PKGBDY = 'CREATE OR REPLACE PACKAGE BODY update_parts AS

   +          PROCEDURE add_parts (n IN INTEGER,

   +            descrip IN part_description,

   +             partno IN part_number);

   +          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

    PLSBLK = 'BEGIN add_parts(3, :DESC, :PARTS); END;'

* Connect to Oracle in non-blocking mode.
* HDA must be initialized to zeros before call to OLOG.
    DATA HDA/256*0/

    CALL OLOG(LDA, HDA, UID, LEN_TRIM(UID),

   +          PWD, LEN_TRIM(PWD), 0, -1, 0)

    IF (LDA(7) .NE. 0) THEN

      PRINT *, 'Cannot connect with username scott.'

      GOTO 999

    END IF

    PRINT *, 'Connected to Oracle.'

* Open the cursor

    CALL OOPEN(CDA, LDA, UID, -1, -1, UID, -1)

    IF (CDA(7) .NE. 0) THEN

      PRINT *, ' Error opening cursor. Exiting....'

      GOTO 999

    END IF

* Parse drop table, also executes

    CALL OPARSE(CDA, DRPTBL, LEN_TRIM(DRPTBL), 1, 2)

    IF (CDA(7) .NE. 0) THEN

      CALL ERRRPT(LDA, CDA)

      GOTO 999

    ENDIF

* Parse create table, also executes

    CALL OPARSE(CDA, CRTTBL, LEN_TRIM(CRTTBL), 1, 2)

    IF (CDA(7) .NE. 0) THEN

      CALL ERRRPT(LDA, CDA)

      GOTO 999

    ENDIF

* Parse and execute CREATE PACKAGE

    CALL OPARSE(CDA, CRTPKG, LEN_TRIM(CRTPKG), 1, 2)

    IF (CDA(7) .NE. 0) THEN

      CALL ERRRPT(LDA, CDA)

      GOTO 999

    ENDIF

    CALL OEXEC(CDA)

    IF (CDA(7) .NE. 0) THEN

      CALL ERRRPT(CDA, LDA)

      GOTO 999

    ENDIF

* Parse and execute CREATE PACKAGE BODY

    CALL OPARSE(CDA, PKGBDY, LEN_TRIM(PKGBDY), 1, 2)

    IF (CDA(7) .NE. 0) THEN

      CALL ERRRPT(LDA, CDA)

      GOTO 999

    ENDIF

    CALL OEXEC(CDA)

    IF (CDA(7) .NE. 0) THEN

      CALL ERRRPT(CDA, LDA)

      GOTO 999

    ENDIF

* Bind the arrays to the placeholders

    DO 10 I = 1, 3

      DSALEN(I) = 20

10    PNALEN(I) = 4

    CALL OBNDRA(CDA, DSPH, LEN_TRIM(DSPH), DESCRP, 20, 1, -1,

   +            0, DSALEN, DSRC, 10, 3, 0, -1, -1)

    IF (CDA(7) .NE. 0) THEN

      CALL ERRRPT(LDA, CDA)

      GOTO 999

    ENDIF

    CALL OBNDRA(CDA, PNPH, LEN_TRIM(PNPH), PRTNOS, 4, 3, -1,

   +            0, PNALEN, PNRC, 10, 3, 0, -1, -1)

    IF (CDA(7) .NE. 0) THEN

      CALL ERRRPT(LDA, CDA)

      GOTO 999

    ENDIF

*  Execute the PL/SQL block

    CALL OEXEC(CDA)

    IF (CDA(7) .NE. 0) THEN

      CALL ERRRPT(LDA, CDA)

      GOTO 999

    ENDIF

    PRINT *, 'Parts table updated.'

999 CALL OCLOSE(CDA)

    CALL OLOGOF(LDA)

END

    SUBROUTINE ERRRPT(LDA, CDA)

    INTEGER*2 LDA(32), CDA(32)

    CHARACTER*80  MSG

    CALL OERHMS(LDA, CDA(7), MSG, 80)

    PRINT '(/, 1X, A)', MSG

    PRINT '(1X, A, I3)', 'processing OCI routine', CDA(6)

    RETURN

END

    INTEGER FUNCTION LEN_TRIM(STRING)

    CHARACTER*(*) STRING

    INTEGER NEXT

    DO 10 NEXT = LEN(STRING), 1, -1

      IF (STRING(NEXT : NEXT) .NE. ' ') THEN

        LEN_TRIM = NEXT

        RETURN

      ENDIF

10  CONTINUE

    LEN_TRIM = 0

    RETURN

END

Parameters

Parameter Name Type Mode
CURSOR INTEGER*2(32) IN/OUT
SQLVAR CHARACTER*n IN
SQLVL INTEGER*4 IN
PROGV (Address) (1) IN/OUT(2)
PROGVL INTEGER*4 IN
FTYPE INTEGER*4 IN
SCALE INTEGER*4 IN
INDP INTEGER*2 IN/OUT(2)
ALEN INTEGER*2 IN/OUT
ARCODE INTEGER*2 OUT (3)
MAXSIZ INTEGER*4 IN
CURSIZ INTEGER*4 IN/OUT(2)
FMT CHARACTER*6 IN
FMTL INTEGER*4 IN
FMTT INTEGER*4 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 This is a CDA 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 five.

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 an INTEGER. On some systems, however, 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, set PROGVL to -1 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 array of indicator variables if progv is an array. As an array, INDP must contain at least the same number of elements as PROGV. See Chapter 2 for more information about indicator variables.

ALEN A pointer to an array of elements containing the length of the data. This is the effective length of the bind variable element, not the size of the array. For example, if the PROGV parameter is an array declared as

CHARACTER*10  ARR(5)

then ALEN should also point to an array of at least five elements. The maximum usable size of the array is determined by the MAXSIZ parameter.

If ARR in the above example is an IN parameter, each element in the array pointed to by ALEN should be set to the length of the data in the corresponding element in the ARR array (ten in this example) before the execute call.

If ARR in the above example is an OUT parameter, the length of the returned data appears in the array pointed to by ALEN after the PL/SQL block is executed.

Once the bind is done using OBNDRA, you can change the data length of the bind variable without rebinding. However, the length cannot be greater than that specified in ALEN.

ARCODE An array containing the column-level error return codes. This parameter is an array that will contain the error code for the bind variable after the execute call. The error codes that can be returned in ARCODE are those that indicate that data in PROGV has been truncated, or that a null occurred on a SELECT or PL/SQL FETCH; for example, ORA-01405 or ORA-01406.

If OBNDRA is being used to bind an array of elements (that is, MAXSIZ is greater than one), then ARCODE must also point to an array of at least equal size.

MAXSIZ The maximum size of the array being bound. 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. If OBNDRA is being used to bind a scalar, set this parameter to zero. A value of one means an array one element long.

CURSIZ The actual number of elements in the array.

To use OBNDRA to bind a scalar, you must be able to pass a zero by value in this parameter. If your FORTRAN compiler does not have a mechanism for passing parameters by value, you must use OBNDRV to bind scalars. It does not work to pass a zero by reference to indicate that a scalar is being bound.

FMT Not normally used in FORTRAN. See the description of OBNDRN for more information.

FMTL Not normally used in FORTRAN. See the description of OBNDRN for more information.

FMTT Not normally used in FORTRAN. See the description of OBNDRN for more information.

See Also

OBINDPS, OBNDRV, OEXEC, OEXN, OPARSE.

OBNDRN OBNDRV

Purpose

OBNDRN and OBNDRV associate the address of a program variable with the specified placeholder in the SQL statement. The placeholder is identified by name for the OBNDRV function and by number for OBNDRN. Values must be placed in the variables before the SQL statement is executed.

Syntax
CALL OBNDRN(CURSOR, SQLVN, PROGV, PROGVL, FTYPE,
            SCALE, [INDP], <FMT>, <FMTL>, <FMTT>)
CALL OBNDRV(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.

If you use OBNDRV, the placeholder in the SQL statement is a colon (:) followed by a SQL identifier. For example, the SQL statement

SELECT ename,sal,comm FROM emp WHERE deptno = :DEPT AND

    comm > :MINCOM

has two placeholders, :DEPT and :MINCOM.

If you use OBNDRN, the placeholders in the SQL statement consist of a colon followed by a literal integer in the range 1 to 255. The SQL statement

SELECT ename,sal,comm FROM emp WHERE deptno = :2 AND comm > :1

has two placeholders, :1 and :2. An OBNDRV call that binds the :DEPT placeholder in the first SQL statement above to the program variable DEPTNO is

    INTEGER*2    CURSOR(32), INDP

    INTEGER      DEPTNO, PHDLEN

    CHARACTER*5  PHDEPT

    CHARACTER*6  FMT

*   ..

    PHDEPT = ':DEPT'

    PHDLEN = 5

    INDP = 0

    CALL OBNDRV(CURSOR, PHDEPT, PHDLEN, DEPTNO, 4, 3,

  1               -1, INDP, FMT, -1, -1)

Note that the INDP parameter is not really used; therefore, it is set to zero in the program so that a null will not be the effective bind variable. The FMT parameter is declared, but never set or used. The -1 length that is passed for the FMTL parameter, and the fact that the datatype is INTEGER (DTYPE = 3) and not PACKED DECIMAL (DTYPE = 7, ensures that the empty format string is never accessed.

If you use OBNDRN, the parameter SQLVN identifies the placeholder by number. If SQLVN is set to 1, the program variable is bound to the placeholder :1. For example, OBNDRN is called to bind the program variable MINCOM to the placeholder :2 in the second SQL statement above as follows:

CALL OBNDRN(CURSOR, 2, MINCOM, 4, 3, -1, INDP, FMT, -1, -1);

where the placeholder :2 is indicated in the SQLVN parameter by passing the value 2. The SQLVN parameter can be a literal or a variable.

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 or OEXFET to execute it.

If the value of the program variable changes, you do not need to rebind before re-executing, since it is the address of the variable that is bound, not the value. However, if you change the actual program variable, you must rebind before re-executing.

For example, if you have bound the address of DEPTN to :DEPT and you now want to use NDEPTN when executing the SQL statement above, you must call OBNDRV again to bind the new program variable to the placeholder.

Also, you cannot in general rebind a placeholder to a variable of a 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.

You should avoid using OBNDRV and OBNDRN after an ODESCR call, since bind variables can occur in a select-list item, and if bound after the describe, the size or datatype may change.

At the time of the bind, Oracle stores the address of the program variable. If the same placeholder occurs more than once in the SQL statement, a single call to OBNDRN or OBNDRV binds all occurrences of the placeholder to the bind variable.

The completion status of the bind is returned in the return code field of the CDA. A return code of zero indicates successful completion.

If your program is linked using the deferred mode option, bind errors that would 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 INTEGER*2(32) IN/OUT
SQLVAR CHARACTER*n IN
SQLVL INTEGER*4 IN
SQLVN INTEGER*4 IN
PROGV (Address) IN (1)
PROGVL INTEGER*4 IN
FTYPE INTEGER*4 IN
SCALE INTEGER*4 IN
INDP INTEGER*2 IN (1)
FMT CHARACTER*6 IN
FMTL INTEGER*4 IN
FMTT INTEGER*4 IN

Note 1. Values are IN parameters for OEXEC, OEXN, or OEXFET. CURSOR A CDA.

SQLVAR Used only with OBNDRV, this parameter is a character string containing the name of a placeholder (including the preceding colon) in the SQL statement.

SQLVL Used only with OBNDRV, the SQLVL parameter is the length of the character string SQLVAR (including the preceding colon). For example, the placeholder :EMPLOYEE has a length of nine.

SQLVN Used only with OBNDRN, this parameter specifies a placeholder in the SQL statement referenced by the cursor by number. For example, if SQLVN is 2, it refers to all placeholders identified by :2 within the SQL statement.

PROGV A program variable or array of program variables that provide input data when at execute time.

PROGVL The length in bytes of the program variable or array element. Since OBNDRV or OBNDRN might be called only once for many different PROGV values on successive execute calls, PROGVL must contain the maximum length of PROGV.

SCALE Not normally used in FORTRAN. See the description of OBNDRN for more information.

INDP INDP is an indicator parameter. If the value is negative when the statement is executed, the column is set to null; otherwise, it is set to the value in PROGV. If the array interface is being used, this parameter must be an array of two-byte integers.

FMT Not normally used in FORTRAN. See the description of OBNDRN for more information.

FMTL Not normally used in FORTRAN. See the description of OBNDRN for more information.

FMTT Not normally used in FORTRAN. See the description of OBNDRN for more information.

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(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 INTEGER*2(32) IN

LDA The LDA specified in the OLOG call that was used to make this connection to Oracle.

See Also

OLOG.

OCAN

Purpose

OCAN informs Oracle that the operation in progress for the specified cursor is complete. The OCAN routine thus frees any resources associated with the specified cursor, but keeps the cursor associated with the associated data areas in the Oracle Server.

Syntax
CALL OCAN(CURSOR)

Comments

OCAN informs Oracle that the operation in progress for the specified cursor is complete. The OCAN function thus frees any resources associated with the specified cursor, but keeps the cursor associated with its parsed representation in the shared SQL area.

For example, if you require only the first row of a multi-row query, you can call OCAN after the first OFETCH operation to inform Oracle that your program will not perform additional fetches.

If you use the OEXFET function to fetch your data, specifying a non-zero value for the OEXFET CANCEL parameter has the same effect as calling OCAN after the fetch completes.

Parameter

Parameter Name Type Mode
CURSOR INTEGER*2(32) 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(CURSOR)

Comments

The OCLOSE routine frees all resources obtained by the OOPEN, OPARSE, and execute and fetch operations using this cursor. If OCLOSE fails, the return code field of the CDA contains the error code.

Parameter

Parameter Name Type Mode
CURSOR INTEGER*2(32) IN/OUT

CURSOR The CDA 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(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 INTEGER*2(32) 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(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 INTEGER*2(32) 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(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 autcommit 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 INTEGER*2(32) 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 of a SQL query.

Syntax
CALL ODEFIN(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 string or, for use with OEXFET or OFEN, an array of scalars or strings. It must be compatible with the external datatype specified in the FTYPE parameter. See Table 3 - 2 for a list of datatypes and compatible variables.

Note: Applications running against a release 7.3 or later server that need to perform piecewise operations or utilize arrays of structures must use the newer ODEFINPS routine instead of ODEFIN.

Oracle places data in the output variables when the program calls OFETCH, OFEN, or OEXFET.

If you do not know the number, lengths, and datatypes of the select-list items, you obtain this information by calling ODESCR before calling ODEFIN.

Call ODEFIN after parsing the SQL statement. Call ODEFIN before calling the fetch routine (OFETCH, OFEN, or OEXFET).

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 > :MINSAL

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 variables with the select-list items in the above statement as demonstrated in the following sample code fragment:

      CHARACTER*10   ENAME

      INTEGER*2      ENAMEL, INDP

      INTEGER*2      RCODES(3), RETL(3)

      INTEGER        EMPNUM, SCALE

      REAL*4         SALARY

*  The following variables are declared and passed as

*  arguments to ODEFIN. They are either not used,

*  or if used (like INDP), the values can be ignored after the

*   fetch.

      INTEGER*2      INDP

      INTEGER        FMTL, FMTT, SCALE

      CHARACTER*2    FMT

...

      FMTL = 0

      CALL ODEFIN(CURSOR, 1, ENAME, ENAMEL, 1,

     1    SCALE, INDP, FMT, FMTL,

     2    FMTT, RETL(1), RCODES(1))

      CALL ODEFIN(CURSOR, 2, EMPNUM, 4, 3, SCALE,

     1    INDP, FMT, FMTL, FMTT,

     2    RETL(2), RCODES(2))

      CALL ODEFIN(CURSOR, 3, SALARY, 4, 4, SCALE,

     1    INDP, FMT, FMTL, FMTT,

     2    RETL(3), RCODES(3))

where ENAMEL contains a known length value (it can be obtained by calling ODESCR).

Oracle provides return code information at the row level using the return code field in the CDA. If you require return code information at the column level, you must include the optional RCODE parameter, as in the examples above. During each fetch, Oracle sets RCODE for each select-list item processed. This return parameter indicates either successful completion (zero) or an exception condition, such as a null item fetched, the item fetched was truncated, or other non-fatal column errors. The following codes are some of the error codes that can be returned in the RCODE parameter:

Code Meaning
0 Success.
1405 A null was fetched.
1406 ASCII or string buffer data was truncated. The converted data from the database did not fit into the buffer. Check the value in INDP, if specified, or RLEN, to determine the original length of the data.
1454 Invalid conversion specified: integers not of length 1, 2, or 4; reals not of length 4 or 8; invalid packed decimal conversions; packed decimal with more than 38 digits specified.
1456 Real overflow. Conversion of a database column or expression would overflow a floating-point number on this machine.
3115 Unsupported datatype.

Parameters

Parameter Name Type Mode
CURSOR INTEGER*2 (32) IN/OUT
POS INTEGER*4 IN
BUF (Address) IN (1)
BUFL INTEGER*4 IN
FTYPE INTEGER*4 IN
SCALE INTEGER*4 IN
INDP INTEGER*2 IN (1)
FMT CHARACTER*6 IN
FMTL INTEGER*4 IN
FMTT INTEGER*4 IN
RLEN INTEGER*2 IN (1)
RCODE INTEGER*2 IN (1)

Note 1. Values in the BUF, INDP, RLEN, and RCODE parameters are valid only after the subsequent OFETCH, OFEN, or OEXFET routine returns. These are effectively OUT parameters for those routines.

CURSOR The CDA specified in the parse call for the SQL statement.

POS The position 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 CDA.

Note: If the ODEFIN call is deferred until execution using the deferred mode link option, this error is returned on the OEXEC, OEXN, or OEXFET call.

BUF The address of the variable in the user program that will receive the data when the fetch is performed. The variable can be of any type into which an Oracle column or expression result can be converted. See Chapter 3 for more information about datatype conversions.

Note: If ODEFIN is being called to define an array fetch operation using OEXFET or OFEN, then the BUF parameter must be the address of an array 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 an array, this is the size of one element of the array.

Note: The BUFL parameter is an INTEGER. On some systems, however, 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, set BUFL to -1 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 converted before it is moved to the output buffer. See Chapter 3 for a list of the external datatypes and codes.

SCALE Not normally used in FORTRAN. See the description of the ODEFIN routine for information about the packed decimal datatype.

INDP An INTEGER*2 indicator parameter that must be passed by reference. The value of INDP after OFETCH or OFEN is executed indicates whether the select-list item fetched was null, truncated, or not altered, by returning one of the following values:

Negative

The item fetched was null.

Zero

The item fetched is stored in the output buffer unaltered.

Positive

The length of the item is greater than the specified length of the program buffer; the returned value has been truncated. The positive value returned by the indicator variable is the actual length of the item before truncation.

If ODEFIN is being called to define an array fetch operation, using the OEXFET or OFEN, then the INDP parameter must be the address of an array large enough to hold indicator variables for all the items that will be fetched.

Note: The INDP parameter offers only a subset of the functionality provided by the RLEN and RCODE parameters.

FMT Not normally used in FORTRAN. See the description of the ODEFIN routine for more information.

FMTLEN Not normally used in FORTRAN. See the description of the ODEFIN routine for more information.

FMTTYP Not normally used in FORTRAN. See the description of the ODEFIN routine for more information.

RLEN An INTEGER*2 variable or array. Oracle places the actual length of the returned column in this variable after a fetch is executed. If ODEFIN is being used to associate an array with a select-list item, the RLEN parameter must also be an array of INTEGER*2 variables of the same dimension as the BUF parameter. Return lengths are valid after the fetch.

RCODE An INTEGER*2 variable or array that must be passed by reference. Oracle places the column return code in this variable after the OFETCH, OFEN, or OEXFET operation. The error codes that can be returned in RCODE are those that indicate that data in the column has been truncated or that a null occurred; for example, ORA-01405 or ORA-01406.

If ODEFIN is being used to associate an array with a select-list item, the RCODE parameter must also be an array of INTEGER*2 variables of the same dimension as the BUF array parameter.

ODEFINPS

Purpose

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.

Syntax
CALL ODEFINPS(CURSOR, OPCODE, POS, BUFCTX, BUFL, FTYPE, 
	[SCALE], [INDP], [FMT], [FMTL], [FMTT], [RLENP], 
	[RCODEP], BUFSKIP, INDSKIP, LENSKIP, RCSKIP)

Comments

ODEFINPS is used to define an output variable for a specified select-list item in a SQL query. Additionally, it can indicate that an application will be fetching data incrementally at runtime. This piecewise fetch is designated in the OPCODE parameter. ODEFINPS is also used when an application will be fetching data into an array of structures.

Note: This function is only compatible with Oracle server release 7.3 or later. If a release 7.3 application attempts to use this function against a release 7.2 or earlier server, an error message is likely to be generated. At that point you must restart execution.

With the introduction of ODEFINPS there are now two fully-supported calls for binding input parameters, the other being the older ODEFIN. Application developers should consider the following points when determining which define call to use:

ODEFINPS is supported only when a program is linked in deferred mode. If it is necessary to link in non-deferred mode, ODEFIN must be used. In this case, the ability to handle piecewise operations and arrays of structures is not supported.

ODEFINPS is more complex than the older bind call. Users who are not performing piecewise operations and are not using arrays of structures may choose to use ODEFIN.

Unlike older OCI calls, ODEFINPS 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 ODEFINPS call.

See the description of odefinps() for a sample C language program demonstrating the use of ODEFINPS.

Parameters

Parameter Name Type Mode
CURSOR INTEGER*2(32) IN/OUT
OPCODE INTEGER*1 IN
POS INTEGER*4 IN
BUFCTX INTEGER*1 IN
BUFL INTEGER*4 IN
FTYPE INTEGER*4 IN
SCALE INTEGER*4 IN
INDP INTEGER*2 IN
FMT CHARACTER*6 IN
FMTL INTEGER*4 IN
FMTT INTEGER*4 IN
RLENP INTEGER*2 OUT
RCODEP INTEGER*2 IN
BUFSKIP INTEGER*4 IN
INDSKIP INTEGER*4 IN
LENSKIP INTEGER*4 IN
RCSKIP INTEGER*4 IN

Note: Since the ODEFINPS 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 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 C for an example that does this.

BUFCTX Piecewise define: A pointer to a context block entirely private to the application. This should be used by the application to store any information about the column being defined. One possible use would be to store a pointer to a file which will be referenced later. Each output variable can then have its own separate file pointer. The pointer can be retrieved by the application during a call to OGETPI.

Array of structures or standard define: This specifies a pointer to the program variable or the beginning of an array of program variables or structures into which the column being defined will be placed when the fetch is performed. This parameter is equivalent to the BUF parameter of the ODEFIN call.

BUFL Piecewise define: The maximum possible size of the column being defined.

Array of structures or standard define: The length (in bytes) of the variable pointed to by BUFCTX into which the column being defined will be placed when a fetch is performed. For an array define, this should be the length of the first scalar element of the array of variables or structures pointed to by BUFCTX.

FTYPE The external datatype to which the select-list item is to be converted before it is moved to the output variable. A list of the external datatypes and datatype codes can be found in the "External Datatypes" section.

For piecewise operations, the valid datatype codes are 1 (VARCHAR2), 5 (STRING), 8 (LONG) and 24 (LONG RAW).

SCALE Not normally used in FORTRAN. See the description of OBNDRV for more information about this parameter.

INDP A pointer to an indicator variable or an array of indicator variables. If arrays of structures are used, this points to a possibly interleaved array of indicator variables.

FMT Not normally used in FORTRAN. See the description of OBNDRV for more information about this parameter.

FMTL Not normally used in FORTRAN. See the description of OBNDRV for more information about this parameter.

FMTT Not normally used in FORTRAN. See the description of OBNDRV for more information about this parameter.

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.

BUFSKIP 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.

INDSKIP 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.

LENSKIP 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.

RCSKIP 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

Purpose

ODESCR describes select-list items for dynamic SQL queries. The ODESCR routine returns internal datatype and size information for a specified select-list item.

Syntax
CALL ODESCR(CURSOR, POS, DBSIZE, [DBTYPE], [CBUF],
            [CBUFL], [DSIZE],
            [PREC], [SCALE], [NULLOK])

Comments

You can call ODESCR after parsing the SQL statement to determine the number of select-list items in a query and obtain the following information about each select-list item:

maximum length of the item's name (DBSIZE)

internal datatype code (DBTYPE)

item name (CBUF)

the length in bytes of the name (CBUFL)

maximum display size (DSIZE)

precision of a numeric (PREC)

scale of a numeric (SCALE)

null status of the column (NULLOK)

This routine is used for interactive or dynamic SQL queries, that is, queries in which the number of select-list items, as well as their datatypes and sizes, might not be known until runtime.

The return code field of the CDA indicates success (zero) or failure (non-zero) of the ODESCR call.

The ODESCR 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 > :MINSAL

contains two select-list items: ENAME and SAL. The position index of SAL is 2, and ENAME's index is 1. The following example shows how you can call ODESCR in a loop to describe the first 12 select-list items in an arbitrary SQL statement. See the second OCI sample program in Appendix C for additional information

     INTEGER        DBSIZE(12), CBUFL(12), DSIZE(12)

     INTEGER*2      DBTYPE(12), PREC(12), SCALE(12), NOK(12)

     CHARACTER*12   CBUF(12)

     CHARACTER*80   SQLSTM

...

*  After logging on and opening a cursor, get and process

*  SQL statements in a loop

     DO WHILE (0 .EQ. 0)

25      PRINT '(/, ''$'', A)', 'SQL> '

        READ '(A)', SQLSTM

        IF (SQLSTM(1:4) .EQ. 'exit') GOTO 999  ! exit loop

        CALL OPARSE(CDA, SQLSTM, LEN_TRIM(SQLSTM), 1, 2)

        IF (CDA(7) .NE. 0) THEN

          CALL ERRRPT(LDA, CDA)

          GOTO 25

        END IF

     DO 50 COUNT = 1, 12

       CBUFL(COUNT) = 12   ! length of CHARACTER buffer

       CALL ODESCR(CDA, COUNT, DBSIZE(COUNT), DBTYPE(COUNT),

1       CBUF(COUNT), CBUFL(COUNT), DSIZE(COUNT),

2       PREC(COUNT), SCALE(COUNT), NOK(COUNT))

       IF (CDA(7) .EQ. 1007) THEN     ! end of select-list

        GOTO 100

       ELSE IF (CDA(7) .NE. 0) THEN

        CALL ERRRPT(LDA, CDA)

        GOTO 25

       END IF

50   CONTINUE

*  Print out the total count and the names, columns sizes,

*  and types of each select-list item

100     COUNT = COUNT - 1

        PRINT '(1X, A, I3, A, /)', 'There were', COUNT,

     1        ' select-list items.'

        PRINT *,'NAME        ',' COL_LEN',' DB_SIZE','    TYPE'

        PRINT *, '------------------------------------'

        DO 150 J = 1, COUNT

150       PRINT '(1X, A, 3I8)', CBUF(J), CBUFL(J),

     1           DBSIZE(J), DBTYPE(J)

      END DO   ! end main loop

Note: A dependency exists between the results returned by a describe operation (ODESCR) and a bind operation (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.

Parameters

Parameter Name Type Mode
CURSOR INTEGER*2(32) IN/OUT
POS INTEGER*4 IN
DBSIZE INTEGER*4 OUT
DBTYPE INTEGER*2 OUT
CBUF CHARACTER*n OUT
CBUFL INTEGER*4 IN/OUT
DSIZE INTEGER*4 OUT
PREC INTEGER*2 OUT
SCALE INTEGER*2 OUT
NULLOK INTEGER*2 OUT

CURSOR A CDA in the program. The ODESCR routine uses the cursor to reference a specific SQL query statement that has been passed to Oracle by a prior OPARSE call.

POS 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 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 CDA.

DBSIZE An integer that 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.

Note: It is generally more efficient to establish a column-level RCODE with ODEFIN rather than using ODESCR after each fetch. For an example of using column-level return codes, see the example code under the OFETCH description in this chapter.

DBTYPE A two-byte integer that receives the internal datatype code of the select-list item. See Table 3 - 1 for a list of Oracle internal datatype codes, and the possible external conversions for each of them.

CBUF The address of 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 An integer that 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 zero, 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 than the length specified in CBUFL on the call.

DSIZE An integer that 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, such as SUBSTR or TO_CHAR, are used to modify the representation of a column. Values returned in DSIZE are

Oracle Column Type Value
CHAR, VARCHAR2, RAW length of the column in the table
NUMBER 22 (the internal length)
DATE 7 (the internal length)
LONG, LONG RAW 0
ROWID (system dependent)
Functions returning dataype 1 (such as TO_CHAR()) same as the DSIZE parameter

PREC An INTEGER*2 variable that receives the precision of select-list items.

SCALE An INTEGER*2 variable that 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

For Oracle7, the SQL types REAL, DOUBLE PRECISION, FLOAT, and FLOAT(N) return the correct precision and a scale of -127.

NULLOK An INTEGER*2 variable that is set to 1 if null values are allowed for that column and to 0 if they are not.

ODESSP

Purpose

ODESSP is used to describe the parameters of a PL/SQL procedure or function stored in an Oracle database.

Syntax

CALL ODESSP(LDA, OBJNAM, ONLEN, RSV1, RSV1LN, RSV2, RSV2LN, OVRLD, POS, LEVEL, ARGNM, ARNLEN, DTYPE, DEFSUP, MODE, 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:

A valid LDA for a connection that has, at the least, execute privileges on the procedure.

The name of the procedure, optionally including the package name. The package body does not have to exist, as long as the procedure is specified in the package.

The total length of the procedure name.

If the procedure exists and the connection specified in the LDA parameter has permission to execute the procedure, ODESSP returns information about each parameter of the procedure in a set of array parameters. It also returns information about the return type if it is a function.

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:

*  Declare parameters

     CHARACTER*35     OBJNAM

      INTEGER*2        OVRLD(10), POS(10), LEVEL(10), ARNLEN(10)

     INTEGER*2        DTYPE(10), PREC(10), SCALE(10)

     INTEGER*4        DTSIZE(10), SPARE(10), ARRSIZ, ONLEN, I

     CHARACTER*20     ARGNAM(10)

     LOGICAL*1        DEFSUP(10), MODE(10), RADIX(10)

...

*  Set the OBJECT NAME

     OBJNAM = 'SCOTT.EMP_RECS.GET_SAL_INFO'

     ONLEN  =  LEN_TRIM(OBJNAM)

*  Call the describe routine

     CALL ODESSP(LDA, OBJNAM, ONLEN, 0, 0, 0, 0

    +            OVRLD, POS, LEVEL, ARGNAM, ARNLEN, DTYPE,

    +            DEFSUP, MODE, DTSIZE, PREC, SCALE, RADIX,

    +            SPARE, ARRSIZ)

*  Print out some of the values

     WRITE (*, '(1X, A)')

    +    'Overload  Level  Pos  Procname           Datatype'

     DO 100 I = 1, ARRSIZ

         WRITE (*, 9000) OVRLD(I), LEVEL(I), POS(I), ARGNAM(I),

    +                    DTYPE(I)

9000   FORMAT (1X, I10, I8, I5, A20, I5)

 100   CONTINUE

...

When this call to ODESSP completes, the return parameter arrays are filled in as shown in Table 6 - 1. 6 is returned in the ARRSIZ parameter, as there were a total of 5 parameters and one function return type described.

ARRAY ELEMENT
PARAMETER 0 1 2 3 4 5
OVRLD 1 1 2 2 3 3
POS 1 2 1 2 0 1
LEVEL 1 1 1 1 1 1
ARGNM name salary ID_num salary NULL name
ARNLEN 4 6 6 6 0 4
DTYPE 1 2 2 2 2 1
DEFSUP 0 0 0 0 0 0
MODE 0 1 0 1 1 0
DTSIZE 10 22 22 22 22 10
PREC 7 4 7 7
SCALE 2 0 2 2
RADIX 10 10 10 10
SPARE¹ n/a n/a n/a n/a n/a n/a
Note 1: Reserved by Oracle for future use.

Table 6 - 1. ODESSP Return Values

Parameters

Parameter Name Type Mode
LDA INTEGER*2(32) IN/OUT
OBJNAM CHARACTER*N IN
ONLEN INTEGER*4 IN
RSV1 (Address) IN
RSV1LN INTEGER*4 IN
RSV2 (Address) IN
RSV2LN INTEGER*4 IN
OVRLD INTEGER*2 OUT
POS INTEGER*2 OUT
LEVEL INTEGER*2 OUT
ARGNM CHARACTER*N(30) OUT
ARNLEN INTEGER*2 OUT
DTYPE INTEGER*2 OUT
DEFSUP LOGICAL*1 OUT
MODE LOGICAL*1 OUT
DTSIZ INTEGER*4 OUT
PREC INTEGER*2 OUT
SCALE INTEGER*2 OUT
RADIX LOGICAL*1 OUT
SPARE INTEGER*4 OUT
ARRSIZ INTEGER*4 IN/OUT

LDA The LDA used in the OLOG call.

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, 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.

MODE This parameter indicates the mode of the corresponding parameter. Zero indicates an IN parameter, one an OUT parameter, and two an IN/OUT parameter.

DTSIZ The size of the datatype in bytes. Character datatypes return the size of the parameter. For example, the EMP table contains a column ENAME. If a parameter in a procedure 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

ODESCR.

OERHMS

Purpose

OERHMS returns the text of an Oracle error message, given the error code RCODE.

Syntax
CALL OERHMS(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 the 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:

      INTEGER*2      LDA(32,2)

      CHARACTER*512  MSGBUF

...

      CALL OERHMS(LDA(1,2), CDA(7,2), MSGBUF, 512)

*  Or, on a VAX.

          CALL OERHMS(LDA(1,2), CDA(7,2), %REF(MSGBUF), 512)

Parameters

Parameter Name Type Mode
LDA INTEGER*2(32) IN/OUT
RCODE INTEGER*2 IN
BUF CHARACTER*n OUT
BUFSIZ INTEGER*4 IN

LDA The LDA specified in the OLOG call that was used to make this connection to Oracle.

RCODE The return code containing an Oracle error number.

BUF A buffer that receives the error message text. The maximum size of the buffer is essentially unlimited for Oracle7.

BUFSIZ The size of the buffer in bytes.

See Also

OERMSG, OLOG.

OEXEC

Purpose

OEXEC executes the SQL statement associated with a cursor.

Syntax
CALL OEXEC(CURSOR)

Comments

Before calling OEXEC, you must call OPARSE to parse the SQL statement; this call must complete successfully. If the SQL statement contains placeholders for bind variables, you must call OBINDPS, OBNDRA, OBNDRN or OBNDRV to bind each placeholder to the address of a program variable before calling OEXEC.

For queries, after OEXEC is called, your program must explicitly request each row of the result using OFEN or OFETCH.

For SQL UPDATE, DELETE, and INSERT statements, OEXEC executes the SQL statement and sets the return code field and the rows processed count field in the CDA. 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 INTEGER*2(32) IN/OUT

CURSOR The CDA specified in the associated OPARSE call.

OEXFET

Purpose

OEXFET executes the SQL statement associated with a cursor, then fetches one or more rows. A cancel (effectively, OCAN) of the cursor can also be performed by OEXFET.

Syntax
CALL OEXFET(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 (if necessary) to bind input variables, then call ODEFIN or ODEFINPS to define output variables.

If the OCI program was linked using the deferred mode link option, the bind and define steps are deferred until OEXFET is called. If OPARSE was called with the deferred parse flag (DEFFLG) parameter non-zero, the parse step is also delayed until OEXFET is called. This means that your program can complete the processing of a SQL statement using a minimum of message round-trips between the client running the OCI program and the database server.

If you call OEXFET for a DML statement that is not a query, Oracle issues the error

ORA-01002:  fetch out of sequence

and the execute operation fails.

Note: Using the deferred parse, bind, and define capabilities to process a SQL statement requires more memory on the client system than the non-deferred sequence. So, you gain execution speed at the cost of some additional space.

When running against an Oracle7 database where the SQL statement was parsed using OPARSE with the LNGFLG Parameter set to 1 or 2, a character string that is too large for its associated buffer is truncated, the column return code (RCODE) is set to the error

ORA-01406:  fetched column value was truncated

and the indicator parameter is set to the original length of the item. However, the OEXFET call does not return an error indication. If a null is encountered for a select-list item, the associated column return code (RCODE) for that column is set to the error

ORA-01405:  fetched column value is NULL

and the indicator parameter is set to -1. The OEXFET call does not return an error.

However, if no indicator parameter is defined and the program is running against an Oracle7 database, OEXFET does return an ORA-01405 error. It is always an error if a null is selected and no indicator parameter is defined, even if column return codes and return lengths are defined.

OEXFET both executes the statement and fetches the row or rows that satisfy the query. If you need to fetch additional rows after OEXFET completes, use the OFEN function.

The following example shows how you can use deferred parse, bind, and define operations together with OEXFET to process a SQL statement:

     INTEGER*2       CURSOR(32), INDPB, SALI(1000), NAMEI(1000)

     CHARACTER*80    SQLSTM, FMT

     REAL*4          SALS(1000)

     CHARACTER*20    NAMES(1000)

     INTEGER         DEPNUM, SQLSTL

     SQLSTM = 'SELECT ENAME, SAL FROM EMP WHERE DEPTNO = :1'

*  After logging on and opening the cursor, do

*  a deferred parse:

     CALL OPARSE(CURSOR, SQLSTM, LEN_TRIM(SQLSTM), 1, 2)

*  Bind the input variable

     CALL OBNDRV(CURSOR, 1, DEPNUM, 4, 3, -1, INDPB,FMT,-1,-1)

     PRINT '(''$'', a)', 'Enter department number: '

     READ '(I6)', DEPNUM

*  Define the salary and ename arrays

     CALL ODEFIN(CURSOR, 2, SALS, 4, 4, -1 SALI)

     CALL ODEFIN(CURSOR, 1, NAMES, 20, 1, NAMEI)

*  Call OEXFET to parse, bind, define, execute, and fetch

     CALL OEXFET(CURSOR, 1000, 0, 0)

The number of rows that were fetched is returned in the rows processed count field of the CDA.

Parameters

Parameter Name Type Mode
CURSOR INTEGER*2(32) IN/OUT
NROWS INTEGER*4 IN
CANCEL INTEGER*4 IN
EXACT INTEGER*4 IN

CURSOR The CDA specified in the associated OPARSE call.

NROWS The number of rows to fetch. If NROWS is greater than 1, then you must define arrays to receive the select-list values, as well as any indicator variables. See the description of ODEFIN for more information.

If NROWS is greater than the number of rows that satisfy the query, the rows processed count field in the CDA is set to the number of rows returned, and Oracle returns the error

ORA-01403:  no data found

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.

OEXN

Purpose

OEXN executes a SQL statement. Array variables can be used as input data.

Syntax
CALL OEXN(CURSOR, ITERS, ROWOFF)

Comments

OEXN is similar to OEXEC, but it allows you to take advantage of the Oracle array interface. OEXN allows operations using an array of bind variables. OEXN is generally much faster than successive calls to OEXEC, especially in a networked client-server environment.

Arrays are bound to placeholders in the SQL statement using OBINDPS, OBNDRA, OBNDRV or OBNDRN. The address of the first element of the array is passed to the bind routine.

The example below declares three arrays, one of ten integers, one of ten indicator parameters, and one of ten 20-character arrays, and defines a SQL statement that inserts multiple rows into the database. After binding the arrays, the program must place data for the first INSERT in ENAMES(1) and EMPNOS(1), for the second INSERT in ENAMES(2) and EMPNOS(2), and so forth. (This step is not shown in the example.) Then OEXN is called to insert the data in the arrays.

     INTEGER*2     CURSOR(32), INDPS(10)

     INTEGER       EMPNOS(10), FMTL, FMTT

     CHARACTER*20  ENAMES(10)

     CHARACTER*4   PH1, PH2, FMT

     CHARACTER*100 SQLSTM

     SQLSTM = 'INSERT INTO emp (ename, empno) VALUES (:N, :E)'

     PH1 = ':N'

     PH2 = ':E'

     FMTL = -1

*  Parse the statement

     CALL OPARSE(CURSOR, SQLSTM, 46, 1, 1)

*  Bind the arrays to the placeholders. Bind variables will be

*  non-NULL, so pass 0 in the indicator parameter array

     DO 10 I = 1, 10

10       INDPS(I) = 0

     CALL OBNDRV(CURSOR, PH1, 2, ENAMES(1), 20, 1,

    1     SCALE, INDPS(1), FMT, FMTL, FMTT)

     CALL OBNDRV(CURSOR, PH2, 2, EMPNOS(1), 4, 3,

    1     SCALE, INDPS(1), FMT, FMTL, FMTT)

*  After obtaining data in the EMPNOS and ENAMES arrays,

*  execute the statement, inserting the values in the arrays

     CALL OEXN(CURSOR, 10, 0)

The completion status of OEXN is indicated in the return code field of the CDA. The rows processed count in the CDA indicates the number of rows successfully processed.

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 array element, and if the rows processed count is not equal to ITERS, the operation failed on array element rows processed count + 1.

You can continue to process the rest of the array even after a failure on one of the array elements as long as a rollback did not occur (obtained from the flags1 field in the CDA). You do this by using the zero-based ROWOFF parameter to start operations at array 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(CURSOR, 10, 6)

Note: The maximum number of elements in an array is 32767.

Parameters

Parameter Name Type Mode
CURSOR INTEGER*2(32) IN/OUT
ITERS INTEGER*4 IN
ROWOFF INTEGER*4 IN

CURSOR The CDA specified in the OPARSE call.

ITERS The size of the array of bind variables to be used. The size cannot be greater than 32767 items. If the size is 1, OEXN acts effectively just like OEXEC.

ROWOFF The zero-based offset within the bind variable array at which to begin operations. OEXN processes (ITERS - ROWOFF) array elements if no error occurs.

See Also

OEXEC, OEXFET.

OFEN

Purpose

OFEN fetches multiple rows into arrays of variables, taking advantage of the Oracle array interface.

Syntax
CALL OFEN(CURSOR, NROWS)

Comments

OFEN is similar to OFETCH; however, OFEN can fetch multiple rows into an array of variables with a single call. A pointer to the array is bound to a select-list item in the SQL query statement using ODEFIN.

When running against an Oracle7 database where the SQL statement was parsed using OPARSE with the LNGFLG parameter set to 1 or 2, a character string that is too large for its associated buffer is truncated, the column return code (RCODE) is set to the error

ORA-01406:  fetched column value was truncated

and the indicator parameter is set to the original length of the item. However, the OFEN call does not return an error indication. If a null is encountered for a select-list item, the associated column return code (RCODE) for that column is set to the error

ORA-01405:  fetched column value is NULL

and the indicator parameter is set to -1. The OFEN call does not return an error.

However, if no indicator parameter is defined and the program is running against an Oracle7 database, OFEN does return the 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.

Even when fetching a single row, Oracle recommends that Oracle7 OCI programs use OEXFET, with the NROWS parameter set to 1, instead of the combination of OEXEC and OFEN. Use OFEN after OEXFET to fetch additional rows when you do not know in advance the exact number of rows that a query returns.

The following example is a complete program that shows how OFEN can be used to extract data, using the array interface

     PROGRAM TSTOFN

     INTEGER*2 CDA(32), LDA(32)
     INTEGER*2 HDA(256)

     INTEGER*2 INDARR(10), RLEN(10), RCODE(10)

     INTEGER*4 EMPNO(10), ROWS, UIDL, PWDLEN, DBNLEN, SQLLEN

     CHARACTER*10 ENAMES(10)

     CHARACTER*20 UID, PASSWD

     CHARACTER*28 SQLSTM

     LOGICAL*1 FMT(2), DBN(2), DUMMY(2)

     UID = 'SCOTT'

     UIDLEN = 5
     PASSWD = 'TIGER'
     PWDLEN = 5


     DATA HDA/256*0/

     CALL OLOG(LDA, HDA, UID, UIDLEN, PASSWD, PWDLEN, 0, 
               -1, 0)

     IF (LDA(7) .NE. 0) GOTO 911

     CALL OOPEN(CDA, LDA, DBN, DBNLEN, -1, UID, UIDL)

     IF (CDA(7) .NE. 0) GOTO 920

     SQLSTM = 'SELECT ENAME, EMPNO FROM EMP'

     SQLLEN = 28

     CALL OPARSE(CDA, SQLSTM, SQLLEN, 1, 1)

     IF (CDA(7) .NE. 0) GOTO 920

     CALL ODEFIN(CDA, 1, ENAMES, 10, 1, -1, INDARR,

    1   FMT, 0, -1, RLEN, RCODE)

     IF (CDA(7) .NE. 0) GOTO 920

     CALL ODEFIN(CDA, 2, EMPNO, 4, 3, -1, DUMMY,

    1   FMT, 0, -1, DUMMY, DUMMY)

     IF (CDA(7) .NE. 0) GOTO 920

     CALL OEXEC(CDA)

     IF (CDA(7) .NE. 0) GOTO 920

     ROWS = 0

100  CALL OFEN(CDA, 10)

        IF (CDA(7) .NE. 1403 .AND. CDA(7) .NE. 0) GOTO 920

        N = CDA(3) - ROWS

        ROWS = ROWS + N

        DO 110 I = 1, N

           IF (INDARR(I) .NE. 0) THEN

              WRITE (*, 9100)

9100          FORMAT(1X, '    (null)')

           ELSE

              WRITE (*, 9110) ENAMES(I)

9110          FORMAT (1X, A10)

           ENDIF

           WRITE (*, 9120) EMPNO(I)

9120       FORMAT (1X, I8)

110     CONTINUE

        IF (CDA(7) .EQ. 0) GOTO 100

      WRITE (*, 9130) CDA(3)

9130  FORMAT (1X, I4, ' rows returned.')

      GOTO 950

911   WRITE(*, 9000) UID

9000  FORMAT(' Cannot connect to Oracle as ', A11)

      GOTO 950

920   WRITE(*, 9010) CDA(7)

9010  FORMAT(' Oracle error', / , I4)

950   END

The completion status of OFEN is indicated in the return code field of the CDA. The rows processed count field in the CDA indicates the cumulative number of rows successfully fetched. If the rows processed count increases by NROWS, OFEN may be called again to get the next batch of rows. If the rows processed count does not increase by NROWS, then an error, such as "no data found", occurred.

Parameters

Parameter Name Type Mode
CURSOR INTEGER*2(32) IN/OUT
NROWS INTEGER*4 IN

CURSOR The CDA associated with the SQL statement by the OPARSE call.

NROWS The size of the defined variable array on which to operate. That is, the maximum number of rows to fetch at a time. The size cannot be greater than 32767 items. If the size is 1, OFEN acts effectively just like OFETCH.

OFETCH

Purpose

OFETCH returns rows of a query to the user program, one row at a time.

Syntax
CALL OFETCH(CURSOR)

Comments

Each select-list item of the query is placed into a buffer identified by a previous ODEFIN or ODEFINPS call.

ORA-01406:  fetched column value was truncated

and the indicator parameter is set to the original length of the item. However, the OFETCH call does not return an error indication.

If a null is encountered for a select-list item, the associated column return code (RCODE) for that column is set to the error

ORA-01405:  fetched column value is NULL

and the indicator parameter is set to -1. The OFETCH call does not return an error.

However, if no indicator parameter is defined and the program is running against an Oracle7 database, OFETCH does return the 1405 error. It is always an error if a null is selected and no indicator parameter is defined, even if column return codes and return lengths are defined.

Even when fetching a single row, Oracle recommends that Oracle7 OCI programs use OEXFET, with the parameter set to 1, instead of the combination of OEXEC and OFETCH.

The following example shows how you can obtain data from Oracle using OFETCH on a query statement. This example continues the one shown in the description of the ODEFIN routine earlier in this section. In that example, the select-list items in the SQL statement

SELECT ename, empno, sal FROM emp WHERE

    sal > :MINSAL

were associated with output buffers, and the addresses of column return lengths and return codes were bound. The example continues:

...

*  Execute the statement

     CALL OEXEC(CURSOR)

*  Fetch each row of the query

     DO 10 I = 1, 1000000

       CALL OFETCH(CURSOR)

*  Was there a row level error or warning?

     IF (CURSOR(7) .NE. 0) THEN

       GOTO 20

*  Check column level return codes for NULL values

     IF (RCODE(1) .EQ. 1405) THEN

       WRITE (*, 100)

100    FORMAT(1X, 'NULL      ')

     ELSE

       WRITE (*, 200) ENAME

200  FORMAT(1X, A10)

*  Process remaining two items in select-list in the same way.

10   CONTINUE

*  Check return code (CURSOR(7)), and process error if it's

*  not 1403 "no more data found".

20   IF (CURSOR(7) .NE. 1403) THEN

        CALL ERRRPT(CURSOR)

* continue with program...

Each OFETCH call returns the next row from the set of rows that satisfies a query. After each OFETCH call, the rows processed count in the CDA is incremented.

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 INTEGER*2(32) IN/OUT

CURSOR The CDA associated with the SQL statement in the OPARSE or OSQL3 call.

OFLNG

Purpose

OFLNG fetches a portion of a LONG or LONG RAW column.

Syntax
CALL OFLNG(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 routine 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 INTEGER*4, 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 INTEGER *4 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 you could retrieve 64 Kbytes, starting at offset 70000, from a LONG RAW column. See the third sample program in Appendix C for a complete example that uses OFLNG

      LOGICAL*1     DAREA(65536)

     INTEGER*4     OFFSET, RETLEN, DBSIZE, SQLSTL

     INTEGER*4     LNGPOS

     INTEGER*2     CURSOR(32)

     CHARACTER*80  SQLSTM

...

     SQLSTM = 'SELECT idno FROM data_table1 WHERE idno = 100'

     SQLSTL = 44

     DBSIZE = 65536

     CALL OPARSE(CURSOR, SQLSTM, SQLSTL, 1, 1)

*  Do the defines, and then execute

*  and fetch from row desired

     CALL OEXFET(CURSOR, 1, 0, 0)

*  cursor is now at right row

*  set position in row of LONG RAW column

*  (this could also be obtained dynamically using ODESCR)

*  then fetch the portion of the column

     LNGPOS = 2

     CALL OFLNG(CURSOR, LNGPOS, DAREA, DBSIZ

Parameters

Parameter Name Type Mode
CURSOR INTEGER*2(32) IN/OUT
POS INTEGER*2 IN
BUF (Address) OUT
BUFL INTEGER*4 IN
DTYPE INTEGER*4 IN
RETL INTEGER*4 OUT
OFFSET INTEGER*4 IN

CURSOR The CDA specified in the associated OPARSE or OSQL3 call.

POS The index position of the LONG-type column in the row. 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. This parameter must be passed by reference.

BUFL The length of BUF in bytes.

DTYPE The code corresponding to the external datatype of BUF. See the "External Datatypes" section for a list of the datatype codes.

RETL The number of bytes returned. If more than 65535 bytes were requested and returned, the value 65535 is returned in this parameter.

OFFSET The zero-based offset of the first byte in the LONG-type column to be fetched.

See Also

ODESCR, OEXFET, OFEN, OFETCH.

OGETPI

Purpose

OGETPI returns information about the next chunk of data to be processed as part of a piecewise insert, update or fetch.

Syntax
CALL OGETPI(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.

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.

Parameters

Parameter Name Type Mode
CURSOR INTEGER*2(32) IN/OUT
PIECEP INTEGER*1 OUT
CTXPP INTEGER*4 OUT
ITERP INTEGER*4 OUT
INDEXP INTEGER*4 OUT

CURSOR The CDA associated with the SQL or PL/SQL statement being processed.

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

OBINDPS, ODEFINPS, OSETPI.

OLOG

Purpose

OLOG establishes a connection between an OCI program and an Oracle database.

Syntax
CALL OLOG(LDA, HDA, UID, UIDL, [PSWD], [PSWDL],
          [CONN], [CONNL], 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 FORTRAN this can be accomplished through the use of the DATA statement. 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 "Non-Blocking Mode" . For a short example program in C, see the onbset() description .

You should also refer to the section on SQL*Net in your Oracle system-specific documentation for any particular notes or restrictions that apply to your operating system.

The following code fragment demonstrates a typical set of declarations and initializations for a call to OLOG.

       INTEGER*2          LDA(32)
       INTEGER*2          HDA(256)
       CHARACTER*80       UID, PSWD
       INTEGER*4          UIDL, PSWDL, MODE
...
       UID = 'SCOTT'
       PSWD = 'TIGER'
       UIDL = LEN_TRIM(UID)
       PSWDL = LEN_TRIM(PSWD)
       MODE = 0
...
*  CONNECT TO ORACLE IN NON-BLOCKING MODE.
*  MODE = 0 INDICATES A NON-BLOCKING CONNECTION.
*  HDA MUST BE INITIALIZED TO ZEROS BEFORE CALL TO OLOG.

       DATA HDA/256*0/

       CALL OLOG(LDA, HDA, UID, UIDL, PSWD, PSWDL, 0, -1, MODE)

       IF (LDA(7).NE.0) THEN
           CALL ERRRPT(LDA(1), LDA(1))
           GOTO 999
       END IF

       WRITE (*, '(1X, A, A)') 'Connected to ORACLE as user ', UID
...

Parameters

Parameter Name Type Mode
LDA INTEGER*2(32) IN/OUT
HDA INTEGER*2(128) OUT
UID CHARACTER*n IN
UIDL INTEGER*4 IN
PSWD CHARACTER*n IN
PSWDL INTEGER*4 IN
CONN CHARACTER*n IN
CONNL INTEGER*4 IN
MODE INTEGER*4 IN

LDA The LDA specified in the OLOG call that was used to make this connection to Oracle.

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@DSERVICENAME

The following string is not a correct example of the UID parameter:

NAME@SERVICENAME/PASSWORD

UIDL The length of the UID string.

PSWD The string containing the password. If the password is specified as part of the string pointed to by UID, this parameter can be omitted.

PSWDL The length of the password.

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.

MODE Specifies whether the connection is in blocking or non-blocking mode. Possible values are zero (for blocking mode) or one (for non-blocking mode).

See Also

OLOGOF, ONBSET, SQLLDA.

OLOGOF

Purpose

OLOGOF disconnects an LDA from the Oracle program global area and frees all Oracle resources owned by the Oracle user process.

Syntax
CALL OLOGOF(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 INTEGER*2(32) IN/OUT

LDA The LDA specified in the OLOG call that was used to make this connection to Oracle.

See Also

OLOG.

ONBCLR

Purpose

ONBCLR places a database connection in non-blocking mode.

Syntax
CALL ONBCLR(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 INTEGER*2(32) IN/OUT

LDA The LDA specified in the OLOG call that was used to make this connection to Oracle.

See Also

OLOG, ONBSET, ONBTST.

ONBSET

Purpose

ONBSET places a database connection in non-blocking mode for all subsequent OCI calls on this connection.

Syntax
CALL ONBSET(LDA)

Comments

ONBSET will succeed if the library is linked in deferred mode and if the network driver supports non-blocking operations.

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 INTEGER*2(32) IN/OUT

LDA The LDA specified in the OLOG call that was used to make this connection to Oracle.

See Also

OLOG, ONBCLR, ONBTST.

ONBTST

Purpose

ONBTST tests whether a database connection is in non-blocking mode.

Syntax
CALL ONBTST(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. Non-blocking connections require Oracle7 Server release 7.2 or higher, and SQL*Net release 2.1 or higher. Non-blocking connections are not possible with a single-task driver.

Parameters

Parameter Name Type Mode
LDA INTEGER*2(32) IN/OUT

LDA The LDA specified in the OLOG call that was used to make this connection to Oracle.

See Also

OLOG, ONBCLR, ONBSET.

OOPEN

Purpose

OOPEN opens the specified cursor.

Syntax
CALL OOPEN(CURSOR, LDA, <DBN>, <DBNL>,
           <ARSIZE>, <UID>, <UIDL>)

Comments

OOPEN associates a CDA in the program with data storage areas in the Oracle Server. Oracle uses these data storage 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 CDA 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 CDA 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 a code example that uses OOPEN.

Parameters

Parameter Name Type Mode
CURSOR INTEGER*2(32) OUT
LDA INTEGER*2(32) IN/OUT
DBN CHARACTER*n IN
DBNL INTEGER*4 IN
ARSIZE INTEGER*4 IN
UID CHARACTER*n IN
UIDL INTEGER*4 IN

CURSOR A cursor data area associated with the program.

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 with 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 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 UDL are ignored in the OOPEN call.

UDL The length of the UID parameter.

See Also

OLOG, OPARSE.

OOPT

Purpose

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.

Syntax
CALL OOPT(CURSOR, RBOPT, WAITOPT)

Comments

The RBOPT parameter is not supported in Oracle Server Version 6 or later.

Parameters

Parameter Name Type Mode
CURSOR INTEGER*2(32) IN/OUT
RBOPT INTEGER*4 IN
WAITOPT INTEGER*4 IN

CURSOR The CDA used in the OOPEN call for this cursor.

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

OOPEN.

OPARSE

Purpose

OPARSE parses a SQL statement or a PL/SQL block and associates it with a cursor. The parse can optionally be deferred.

Syntax
OPARSE(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.

The following example opens a cursor and parses a SQL statement. The OPARSE call associates the SQL statement with the cursor.

     INTEGER*2      LDA(32), CURSOR(32)

     CHARACTER*120  SQLSTM

..

     SQLSTM = 'DELETE FROM emp WHERE empno = :EMPLOYEE_NUMBER'

...

*  After connecting to Oracle..

     CALL OOPEN(CURSOR, LDA, 0, 0, 0, 0, 0)

     CALL OPARSE(CURSOR, SQLSTM, LEN_TRIM(SQLSTM), 1, 1)

SQL syntax error codes are returned in the CDA'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 (CDA)" for a list of the information fields available in the CDA after an OPARSE call.

Parameters

Parameter Name Type Mode
CURSOR INTEGER*2(32) IN/OUT
SQLSTM CHARACTER*n IN
SQLL INTEGER*4 IN
DEFFLG INTEGER*4 IN
LNGFLG INTEGER*4 IN

CURSOR The CDA specified in the OOPEN call.

SQLSTM A string containing the SQL statement.

SQLL The length of the SQL statement string 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 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

The LNGFLG parameter has three possible settings:

0 Specifies Version 6 behavior (the database you are connected to can be either Version 6 or Oracle7).
1 Specifies the normal behavior for the database version the program is connected to (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

See Also

ODESCR, OEXEC, OEXFET, OEXN, OOPEN.

OROL

Purpose

OROL rolls back the current transaction.

Syntax
CALL OROL(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 INTEGER*2(32) IN/OUT

LDA The LDA specified in the OLOG call that was used to make this connection to Oracle.

See Also

OCOM, OLOG.

OSETPI

Purpose

OSETPI sets information about the next chunk of data to be processed as part of a piecewise insert, update or fetch.

Syntax
CALL OSETPI(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 either the buffer containing the next piece to be inserted, or to the buffer where the next fetched piece will be stored.

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.

Parameters

Parameter Name Type Mode
CURSOR INTEGER*2(32) IN
PIECE INTEGER*1 IN
BUFP INTEGER*4 IN
LENP INTEGER*4 IN/OUT

CURSOR The cursor data area associated with the SQL or PL/SQL statement.

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

OBINDPS, ODEFINPS, OGETPI.

SQLLD2

Purpose

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.

Syntax
CALL SQLLD2(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.

This example demonstrates how you can use SQLLD2 to obtain a valid LDA for a specific connection:

     INTEGER*2     LDA1(32), LDA2(32)

     CHARACTER*20  DBNAM1, DBNAM2

     INTEGER*4     CNLEN1, CNLEN2

*  Set up handles

     DBNAM1 = 'D:NEWYORK'

     DBNAM2 = 'D:LOSANGLES'

     DB_STRING1 = 'D:NEWYORK'

     DB_STRING2 = 'D:LOSANGELES'

     CNLEN1     = 9

     CNLEN2     = 12

*  Get the LDAs

     CALL SQLLD2(LDA1, DBNAM1, CNLEN1)

     CALL SQLLD2(LDA2, DBNAM2, CNLEN2)

Parameters

Parameter Name Type Mode
LDA INTEGER*2(32) OUT
CNAME CHARACTER*n IN
CNLEN INTEGER*4 IN

LDA A local data area. You must declare this data area before calling SQLLD2.

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

Purpose

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. The address of an LDA is passed to SQLLDA; the precompiler fills in the required fields in the LDA.

Syntax
CALL SQLLDA(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 for additional information about multiple remote logons.

EXEC SQL BEGIN DECLARE SECTION;

     CHARACTER*20 USRNAM, PASSWD, DBSTR1, DBSTR2

EXEC SQL END DECLARE SECTION;

...

*  Host program declarations

     INTEGER*2     LDA1(32), LDA2(32)

*  Set up arrays

     USRNAM = 'SCOTT'

     PASSWD = 'TIGER'

     DBSTR1 = 'D:NEWYORK'

     DBSTR2 = 'D:LOSANGELES'

*  Do the connections

     EXEC SQL DECLARE dbn1 DATABASE;

     EXEC SQL CONNECT :USRNAM IDENTIFIED BY :PASSWD

         AT dbn1 USING :DBSTR1;

*  Get the first LDA for OCI use

     CALL SQLLDA(LDA1)

     EXEC SQL DECLARE dbn2 DATABASE;

     EXEC SQL CONNECT :USRNAM IDENTIFIED BY :PASSWD

       AT dbn2 USING :DBSTR2;

*  Get the second LDA for OCI use

     CALL SQLLDA(LDA2)

Parameter

Parameter Name Type Mode
LDA INTEGER*2(32) OUT

LDA A local data area. You must declare this area before calling SQLLDA.

Prev Next

Library

Product

Contents

Index

*Parameter Name*	*Type*	*Mode*
CURSOR	INTEGER*2(32)	IN/OUT
OPCODE	INTEGER*1	IN
SQLVAR	CHARACTER*n	IN
SQLVL	INTEGER*4	IN
PVCTX	INTEGER*1	IN
PROGVL	INTEGER*4	IN
FTYPE	INTEGER*4	IN
SCALE	INTEGER*4	IN
INDP	INTEGER*2	IN/OUT
ALENP	INTEGER*2	IN
RCODEP	INTEGER*2	OUT
PVSKIP	INTEGER*4	IN
INDSKIP	INTEGER*4	IN
ALENSKIP	INTEGER*4	IN
RCSKIP	INTEGER*4	IN
MAXSIZ	INTEGER*4	IN
CURSIZ	INTEGER*4	IN/OUT
FMT	CHARACTER*6	IN
FMTL	INTEGER*4	IN
FMTT	INTEGER*4	IN

*Code*	*Meaning*
0	Success.
1405	A null was fetched.
1406	ASCII or string buffer data was truncated. The converted data from the database did not fit into the buffer. Check the value in INDP, if specified, or RLEN, to determine the original length of the data.
1454	Invalid conversion specified: integers not of length 1, 2, or 4; reals not of length 4 or 8; invalid packed decimal conversions; packed decimal with more than 38 digits specified.
1456	Real overflow. Conversion of a database column or expression would overflow a floating-point number on this machine.
3115	Unsupported datatype.

*Oracle Column Type*	*Value*
CHAR, VARCHAR2, RAW	length of the column in the table
NUMBER	22 (the internal length)
DATE	7 (the internal length)
LONG, LONG RAW	0
ROWID	(system dependent)
Functions returning dataype 1 (such as TO_CHAR())	same as the DSIZE parameter

*SQL Datatype*	*Precision*	*Scale*
NUMBER(P)	P	0
NUMBER(P,S)	P	S
NUMBER	0	0
FLOAT(N)	0	0

ARRAY ELEMENT
PARAMETER	0	1	2	3	4	5
OVRLD	1	1	2	2	3	3
POS	1	2	1	2	0	1
LEVEL	1	1	1	1	1	1
ARGNM	name	salary	ID_num	salary	NULL	name
ARNLEN	4	6	6	6	0	4
DTYPE	1	2	2	2	2	1
DEFSUP	0	0	0	0	0	0
MODE	0	1	0	1	1	0
DTSIZE	10	22	22	22	22	10
PREC		7	4	7	7
SCALE		2	0	2	2
RADIX		10	10	10	10
SPARE¹	n/a	n/a	n/a	n/a	n/a	n/a
Note 1: Reserved by Oracle for future use.

*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 you are connected to can be either Version 6 or Oracle7).
1	Specifies the normal behavior for the database version the program is connected to (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`