Programmer's Guide to the Oracle7 Server Call Interface | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Purpose
What the routine does.
Syntax
The routine call with its parameter list.
Comments
A detailed description of the routine, including examples.
Parameters
A 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.
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.
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.
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.
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.
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.
OBINDPS associates the address of a program variable with a placeholder in a SQL or PL/SQL statement. Unlike older OCI bind calls, OBINDPS can be used to bind placeholders to be used in piecewise operations, or operations involving arrays of structures.
SyntaxCALL OBINDPS(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:
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 |
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 binds the address of a program variable or array to a placeholder in a SQL statement or PL/SQL block.
SyntaxCALL 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 |
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 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.
SyntaxCALL 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 |
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 performs an immediate (asynchronous) abort of any currently executing OCI routine that is associated with the specified LDA. It is normally used to stop a long-running execute or fetch call that has not yet completed.
SyntaxCALL OBREAK(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 |
See Also
OLOG.
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.
SyntaxCALL 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 |
See Also
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 |
See Also
OCOF disables autocommit, that is, automatic commit of every SQL data manipulation statement.
SyntaxCALL 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 |
See Also
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 |
See Also
OCON enables autocommit, that is, automatic commit of every SQL data manipulation statement.
SyntaxCALL 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 |
See Also
ODEFIN defines an output buffer for a specified select-list item of a SQL query.
SyntaxCALL 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. |
Parameter Name | Type | Mode |
CURSOR | INTEGER*2 (32) | IN/OUT |