Programmer's Guide to the Oracle7 Server Call Interface

Library

Product

Contents

Index

Additional OCI Routines (COBOL)

This appendix describes OCI routines that have been superseded by newer ones in more recent versions of the Oracle7 OCI. The new routines offer increased functionality or performance and should be used in new OCI programs that access Oracle7.

These older routines are still available in Oracle7. However, they might not be available in future versions of the OCI.

ODSC

Purpose

ODSC describes select-list items for dynamic SQL queries. The ODSC routine returns internal datatype and size information for a specified select-list item. Use ODESCR in place of ODSC in new Oracle7 OCI programs.

Syntax
CALL "ODSC" USING CURSOR, POS, DBSIZE, [FSIZE],
     [RCODE], [DBTYPE], [CBUF], [CBUFL], [DSIZE].

Comments

You can call ODSC after an OSQL3 or OEXEC call to obtain the maximum size (DBSIZE), internal datatype code (DBTYPE), column name (CBUF), and maximum display size (DSIZE) of select-list items in a query. This routine is used for dynamic SQL queries, that is, queries in which the number of select-list items, as well as their datatypes and sizes, may not be known until runtime.

The ODSC routine may also be called after an OFETCH to return the actual size (FSIZE) of the item just fetched.

The return code field of the cursor data area indicates success (zero) or failure (non-zero) of the ODSC call.

The ODSC 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 > :MIN_SAL

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 would call ODSC to describe the two select-list items in this SQL statement:

DATA DIVISION.

77   ENAME-L     PIC S9(4) COMP.

77   SAL-L       PIC S9(4) COMP.

77   ENAME-TYPE  PIC S9(4) COMP.

77   SAL-TYPE    PIC S9(4) COMP.

77   MAX-DISP-L  PIC S9(4) COMP.

77   FSIZE       PIC S9(4) COMP.

77   RCODE       PIC S9(4) COMP.

77   COL-NAME    PIC X(240).

77   C-NAME-L    PIC S9(4) COMP.

...

PROCEDURE DIVISION.

CALL "ODSC" USING CURSOR, 1, ENAME-L, FSIZE, RCODE,

    ENAME-TYPE, COL-NAME, C-NAME-L, MAX-DISP-L.

...

CALL "ODSC" USING CURSOR, 2, SAL-L, FSIZE, RCODE,

    SAL-TYPE, COL-NAME, C-NAME-L, MAX-DISP-L.

Note: A dependency exists between the results returned by a describe operation (ODSC) and a bind operation (OBNDRN or OBNDRV). Because a select-list item might contain bind variables, the type returned by ODSC 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 ODSC 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 Status
CURSOR Address IN/OUT
POS PIC S9(9) COMP IN
DBSIZE PIC S9(4) COMP IN
FSIZE PIC S9(4) COMP IN 1
RCODE PIC S9(4) COMP IN 1
DBTYPE PIC S9(4) COMP OUT
CBUF PIC X(n) OUT
CBUFL PIC S9(4) COMP IN/OUT
DSIZE PIC S9(4) COMP OUT

Note 1. Parameter can also be an OUT after OFETCH or OFEN.

CURSOR The cursor data area associated with the SQL statement being described. The ODSC routine uses the cursor to reference a specific SQL query statement that has been passed to Oracle by a prior OSQL3 call.

POSITION 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, ODSC returns a "variable not in select-list" error in the return code field of the cursor data area.

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

FSIZE FSIZE receives the actual size of the data value returned by the last OFETCH operation. This parameter is only meaningful when ODSC is issued after an OFETCH call. The value returned is the actual length of the value as stored in the database before it is moved to the user buffer where padding or truncation may take place. Oracle suppresses leading zeros on numeric data and trailing blanks on VARCHAR data before storing the values in the database.

RCODE RCODE receives the column return code returned by the last fetch operation. This parameter is meaningful only when ODSC is issued after an OFETCH call.

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

DBTYPE DBTYPE receives the internal datatype code of the select-list item. A list of Oracle internal datatype codes and the possible external conversions for each of them is provided in Table 3 - 2.

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 CBUFL is set to the length of CBUF. The column name is truncated if it is longer than CBUFL. If CBUFL is not specified or if the value contained in CBUFL is 0, then the column name is not returned. On return from ODSC, CBUFL contains the length of the column or expression name.

DSIZE DSIZE 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 routines, like SUBSTR or TO_CHAR, are used to modify the representation of a column.

See Also

ODESDCR.

OERMSG

Purpose

OERMSG returns the Oracle error message text corresponding to the return code parameter RCODE. Use OERHMS instead of OERMSG in new Oracle7 OCI programs or wherever longer error messages must be returned.

Syntax

CALL "OERMSG" USING RCODE, MSG.

Comments

The OERMSG routine is similar to OERHMS in that it returns an Oracle error message text. If there is no message that corresponds to the return code, the message "unknown Oracle error code" is returned.

Note: Programs that have multiple active logons should not use OERMSG to obtain error message text. Use OERHMS instead. Also, Precompiler programs that contain OCI calls cannot use OERMSG; use OERHMS instead.

The following example shows a subroutine that can be used to report oracle errors:

ORA-ERR

.   IF LDA-RC NOT = 0

        DISPLAY "OLOGON ERROR"

        MOVE LDA-RC TO ERR-RC

        MOVE "0" TO ERR-FUNC

    ELSE IF C-RC IN CURSOR-1 NOT = 0

        MOVE C-RC IN CURSOR-1 TO ERR-RC

        MOVE C-FNC IN CURSOR-1 TO ERR-FUNC

    ELSE MOVE C-RC IN CURSOR-2 TO ERR-RC

        MOVE C-FNC IN CURSOR-2 TO ERR-FUNC

        MOVE ERR-RC TO ERR-RCX

.   DISPLAY "Oracle ERROR. CODE IS ", ERR-RCX ,",FUNCTION IS ",

             ERR-FUNC

.   CALL "OERMSG" USING ERR-RC, MSGBUF

.   DISPLAY MSGBUF.

Parameters

Parameter Name Type Status
RCODE PIC S9(4) COMP IN
MSG PIC X(n) OUT

RCODE The return code value for which the message text is to be returned. This can be either the V2 return code or the normal return code.

MSG The address of a program buffer to receive the error message text. The maximum message size that can be returned is 70 characters. If the message is longer than the buffer size or 70 characters, the message is truncated.

See Also

OERHMS.

OLON

Purpose

OLON logs a program on to an Oracle database. Communication between the program and Oracle is established using the logon data area defined in the program. Use OLOG in place of ORLON in new Oracle7 OCI programs.

Syntax
CALL "OLON" USING LDA, UID, UIDL, [PSWD],
     [PSWDL], <AUDIT>.

Comments

A program can log on to Oracle many times, but if the program logs on using OLON, only one connection can be active at a time. To switch to another connection you must first log off using the OLOGOF procedure, then log on again using OLON. For multiple concurrent logons using different LDAs, use the ORLON routine.

OLON cannot be used in a program that mixes OCI calls and Precompiler statements. See the description of SQLLDA in Chapter 5 for more information. To connect to Oracle:

DATA DIVISION.

WORKING STORAGE DIVISION

.   77    USED-ID       PIC X(6)  VALUE "system"

.   77    USER-ID-L     PIC S9(9) VALUE 6 COMP

.   77    PASSWORD      PIC X(7)  VALUE "manager"

.   77    PASSWORD-L    PIC S9(9) VALUE 7 COMP

.   77    NO-AUDIT      PIC S9(9) VALUE 0 COMP

.   ...

PROCEDURE DIVISION.

..

.   CALL "OLON" USING LDA, USER-ID, USER-ID-L, PASSWORD,

                      PASSWORD-L, NO-AUDIT

.   ...

Parameters

Parameter Name Type Status
LDA (Address) IN/OUT
UID PIC X(n) IN
UIDL PIC S9(9) COMP IN
PSWD PIC X(n) IN
PSWDL PIC S9(9) COMP IN
AUDIT PIC S9(9) COMP IN

LDA The LDA specified in the OLOG call that was used to make this connection to Oracle. The return code field indicates the result of the OLON call. Zero indicates a successful logon.

UID A character string containing the user ID and possibly the password. If the password is included as part of the user ID, it must be separated from the user ID by a `/'.

If the user ID is `/' and a valid automatic logon account exists on the database, then the logon succeeds under the user ID of the person running the program.

UIDL The length of the UID string.

PSWD A character string containing the password. If the password is specified as part of USER-ID, this parameter can be omitted.

If the UID parameter does not include a password and the PSWD parameter is invalid or omitted, the logon will fail with an error code in the return code field of the LDA.

PSWDL The length of the PSWD string.

AUDIT This parameter is not currently used. It should be passed as zero.

See Also

OLOG.

ONAME

Purpose

ONAME retrieves the names of select-list items in a SQL query statement. Use ODESCR in place of ONAME in new Oracle7 OCI programs.

Syntax
CALL "ONAME" USING CURSOR, POS, <TBUF>,
     <TBUFL>, CBUF, CBUFL.

Comments

ONAME can be called only after the SQL statement has been parsed using OSQL3. The maximum length of an item name that can be returned is 240 bytes.

Note: ONAME obtains the names of select-list items, not their values. In the SQL statement "SELECT AVG(SAL) FROM EMP", there is one select-list item. Its name is "AVG(SAL)".

ONAME operates by referencing the select-list items by position index. Indices are numbered sequentially from left to right, beginning with 1. If you specify a position number greater than the number of select-list items, ONAME returns a "no data found" status in the return code field of the cursor data area.

For queries such as "SELECT * FROM EMP", in which the number of columns is unknown, you can obtain the column names dynamically by repeatedly calling ONAME until a "no data found" error occurs. For example, the following code fragment shows how to use ONAME to obtain column names:

DATA DIVISION.

WORKING STORAGE SECTION.

77   NAME-BUFFER    PIC X(240).

77   SQL-STMT       PIC X(17) VALUE "SELECT * FROM EMP".

77   SQL-STMT-LEN   PIC S9(9) VALUE 17 COMP.

77   NAME-BUFFER-L  PIC S9999 COMP.

77   J              PIC S9(9) COMP.

...

PROCEDURE DIVISION.

...

*  Parse the statement.

CALL "OSQL3" USING CURSOR, SQL-STMT, SQL-STMT-LEN.

*  Get and display the names of each select-list item.

PERFORM DISP-ITEM VARYING J FROM 1 BY 1

    UNTIL C-RC IN CURSOR NOT = 0.

...

DISP-ITEM

.   MOVE 240 TO NAME-BUFFER-L

.   CALL "ONAME" USING CURSOR, J, OMITTED, OMITTED,

         NAME-BUFFER, NAME-BUFFER-L

.   DISPLAY NAME-BUFFER.

...

Note: ONAME provides only a subset of the functionality of the ODSC and ODESCR routines.

Parameters

Parameter Name Type Status
CURSOR Address IN/OUT
POS PIC S9(9) COMP IN
TBUF PIC X(n) IN
TBUFL PIC S9(4) COMP IN
CBUF PIC X(n) OUT
CBUFL PIC S9(4) COMP IN/OUT

CURSOR The cursor data area for the query. The ONAME routine uses the cursor address to obtain the names from the parsed SQL statement in the Oracle shared SQL cache.

POS The position index of the select-list item in the SQL query. The first (or left-most) select-list item is position 1.

TBUF A character string. This parameter is included only for Oracle Version 2 compatibility. For later versions, it is unused.

TBUFL This parameter is included only for Oracle Version 2 compatibility. For later versions, it is unused.

CBUF The name of the select-list item. If CBUFL is passed as 0, the name is not stored. The name returned in CBUF is blank padded to the length specified in the CBUFL input parameter.

CBUFL Before calling ONAME, CBUFL must be set to the length of CBUF. After ONAME completes, CBUFL contains the length of the name that was returned in CBUF. If CBUFL is not specified or if the value contained in CBUFL is 0, no name is returned in CBUF. If the select-list item name is longer than the value in CBUFL, the returned name is truncated.

See Also

ODESCR.

ORLON

Purpose

ORLON establishes concurrent communications between a user's program and Oracle through SQL*Net. Use OLOG in place of ORLON in new Oracle7 OCI programs.

Syntax
CALL "ORLON" USING LDA, HDA, UID, UIDL,
     [PSWD], [PSWDL], <AUDIT>.

Comments

A program can log on to Oracle multiple times. Communication takes place using the logon data area and the host data area defined within the program. The ORLON routine connects the LDA to Oracle.

The host data area is a program-allocated data area associated with each ORLON logon call. Its contents are entirely private to Oracle but the HDA must be allocated by the user program, just like the logon data area and cursor data area.

Note: Refer to the section "Host Data Area" in Chapter 2 for important information about allocating an HDA.

Each concurrent logon requires one LDA-HDA pair. For example:

DATA DIVISION.

WORKING STORAGE SECTION.

01  LDA-1.

    02  LDA-V2RC    PIC S9(4) COMP.

    02  FILLER      PIC X(10).

    02  LDA-RC      PIC S9(4) COMP.

    02  FILLER      PIC X(50).

01  LDA-2.

    02  LDA-V2RC    PIC S9(4) COMP.

    02  FILLER      PIC X(10).

    02  LDA-RC      PIC S9(4) COMP.

    02  FILLER      PIC X(50).

01  HDA-1.

    02  FILLER      PIC X(256).

01  HDA-2.

    02  FILLER      PIC X(256).

77  USER-ID-1       PIC X(14)     VALUE
    "SCOTT/TIGER@T:KERVMS:ORA60".

77  USER-ID-2       PIC X(14)     VALUE
    "SYSTEM/MANAGER@T:WRVMS:V60TSTD".

77  USER-ID-1-L     PIC S9(9) VALUE 26 COMP.

77  USER-ID-2-L     PIC S9(9) VALUE 30 COMP.

77  PASSWORD        PIC X(1).

...

PROCEDURE DIVISION.

...

CALL "ORLON" USING LDA-1, HDA-1, USER-ID-1, USER-ID-1-L,

     OMITTED, ZERO, ZERO.

IF LDA-RC IN LDA-1 NOT = 0

   PERFORM ERRRPT.

*  And later...

CALL "ORLON" USING LDA-2, HDA-2, USER-ID-2, USER-ID-2-L,

     OMITTED, ZERO, ZERO.

IF LDA-RC IN LDA-2 NOT = 0

   PERFORM ERRRPT.

When the program has issued an ORLON call, a subsequent OLOGOF call commits all outstanding transactions. If a program fails to log off or terminates abnormally, all outstanding transactions are rolled back.

The LDA return code field indicates the result of the ORLON call. A zero return code indicates a successful logon.

You should also refer to the section on SQL*Net in the Oracle installation or user's guide for your operating system for any particular notes or restrictions that apply to your operating system.

Parameters

Parameter Name Type Mode
LDA (Address) IN/OUT
HDA PIC X(256) OUT
UID PIC X(n) IN
UIDL PIC S9(9) COMP IN
PSWD PIC X(n) IN
PSWDL PIC S9(9) COMP IN
AUDIT PIC S9(9) COMP 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@D:NODENAME:DBNAME

NAME/PASSWORD@D:NODENAME:DBNAME

where D is the network identifier, NODENAME is the name of the remote server, and DBNAME is the name of the database instance. The following string is not a correct example of the USERID parameter:

NAME@D:NODENAME:DBNAME/PASSWORD

UIDL The length of the UID string.

PSWD A string containing the password. If the password is specified as part of the UID string, this parameter can be omitted.

PSWDL The length of the PSWD parameter.

AUDIT This parameter is not used. Pass it as 0 or -1.

See Also

OLOG.

OSQL3

Purpose

OSQL3 parses a SQL statement and associates it with a cursor. The OSQL3 routine also executes Data Definition Language statements. Use OPARSE in place of OSQL3 in new Oracle7 OCI programs.

Syntax

CALL "OSQL3" USING CURSOR, SQLSTM, SQLL.

Comments

OSQL3 passes the SQL statement to Oracle for parsing. The parsed representation of the SQL statement is stored in the shared SQL statement cache in the Oracle server, and state information about the cursor is stored in the private SQL area. Subsequent OCI calls reference the SQL statement using the cursor name. An open cursor may be reused by subsequent OSQL3 calls within a program, or the program may define multiple concurrent cursors when it is necessary to maintain multiple active SQL statements.

The SQL statement may be any valid SQL query, data manipulation, data definition, or data control statement. Oracle parses the statement and selects an optimal access path to perform the requested routine. Data Definition Language statements are executed when OSQL3 is called. Data Manipulation Language statements and queries are not executed until the OEXEC, OEXN, or OEXFET routine is called.

See the description of the OFETCH routine for an example of the OSQL3 routine.

SQL syntax error codes are returned in the cursor data area's return code field and parse error offset field. Parse error offset indicates the location of the error in the SQL statement text. See the description of the cursor data area for a list of the information fields available in the cursor data area after an OSQL3 call.

Parameters

Parameter Name Type Status
CURSOR Address IN/OUT
SQLSTM PIC X(n) IN
SQLL PIC S9(9) COMP IN

CURSOR The cursor data area specified in the associated OOPEN call.

SQLSTM A character string containing a SQL statement. The SQL statement may contain placeholders in any place where a constant is permitted. Placeholders are indicated by placing a colon (:) immediately before the placeholder name. Placeholders are bound to program variables using the OBNDRV or OBNDRN routines.

SQLL Specifies the length of the SQL statement.

See Also

OPARSE.

Prev Next

Library

Product

Contents

Index

*Parameter Name*	*Type*	*Status*
CURSOR	Address	IN/OUT
POS	PIC S9(9) COMP	IN
DBSIZE	PIC S9(4) COMP	IN
FSIZE	PIC S9(4) COMP	IN 1
RCODE	PIC S9(4) COMP	IN 1
DBTYPE	PIC S9(4) COMP	OUT
CBUF	PIC X(n)	OUT
CBUFL	PIC S9(4) COMP	IN/OUT
DSIZE	PIC S9(4) COMP	OUT

*Parameter Name*	*Type*	*Mode*
LDA	(Address)	IN/OUT
HDA	PIC X(256)	OUT
UID	PIC X(n)	IN
UIDL	PIC S9(9) COMP	IN
PSWD	PIC X(n)	IN
PSWDL	PIC S9(9) COMP	IN
AUDIT	PIC S9(9) COMP	IN