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



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

The OCI Routines for 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 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

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:

IN

A parameter that passes data to Oracle.

OUT

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

IN/OUT

A parameter that passes data on the call, and receives data on the return from this 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:

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.

See Also

OBNDRA, OBNDRN, OBNDRV, ODEFINPS, OGETPI, OSETPI.

OBNDRA

Purpose

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

Syntax

CALL OBNDRA(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.

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 the SQL statement or PL/SQL block is executed.

To use OBNDRA to bind a scalar, you must be able to pass a zero by value in this parameter. If your 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.

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 OBNDRN or OBNDRV.

FTYPE The external datatype of the program variable as defined within the user program. Oracle converts the program variable from external to internal format before it is bound to the SQL statement. See "External Datatype Codes" [*] for a complete list of external datatypes.

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.

See Also

OBINDPS, OBNDRA, ODESCR, OEXEC, OEXFET, OEXN, OPARSE.

OBREAK

Purpose

OBREAK performs an immediate (asynchronous) abort of any currently executing OCI routine that is associated with the specified LDA. It is normally used to stop a long-running execute or fetch call that has not yet completed.

Syntax

CALL OBREAK(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