| Programmer's Guide to the Oracle7 Server Call Interface |  Library |
 Product |
 Contents |
 Index |
| Programmer's Guide to the Oracle7 Server Call Interface | Library |
Product |
Contents |
Index |
These older routines are still available in Oracle7. However, they might not be available in future versions of the Oracle Call Interface.
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.
SyntaxCALL ODSC(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:
INTEGER*2 CURSOR(32)
INTEGER*2 ENAME_LEN, SAL_LEN, ENAME_TYPE, SAL_TYPE
INTEGER*2 COL_NAME_LEN, MAX_DISP_LEN
C The following two variables are for unused optional parameters
INTEGER*2 FSIZE, RCODE
CHARACTER*240 COL_NAME
..
. CALL ODSC(CURSOR, 1, ENAME_LEN, FSIZE, RCODE,
1 ENAME_TYPE, COL_NAME, COL_NAME_LEN, MAX_DISP_LEN)
C Make use of the values returned..
. CALL ODSC(CURSOR, 2, SAL_LEN, FSIZE, RCODE,
1 SAL_TYPE, COL_NAME, COL_NAME_LEN, MAX_DISP_LEN)
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 | INTEGER*2(32) | IN/OUT |
| POS | INTEGER*4 | IN |
| DBSIZE | INTEGER*2 | OUT |
| FSIZE | INTEGER*2 | IN |
| RCODE | INTEGER*2 | IN 1 |
| DBTYPE | INTEGER*2 | OUT |
| CBUF | CHARACTER*n | OUT |
| CBUFL | INTEGER*2 | IN/OUT |
| DSIZE | INTEGER*2 | OUT |
CURSOR The cursor data area for the SQL statement being described.
POS The position index of the select-list item in the SQL query. Each item is referenced by position as if 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 in Chapter 6.
DBTYPE DBTYPE receives the internal datatype code of the select-list item. See page 3 - 3 for a list of Oracle internal datatype codes and the possible external conversions for each of them.
CBUF 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 A short integer that 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 returned 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 SQL functions, like SUBSTR or TO_CHAR, are used to modify the representation of a column.
See Also
OERMSG returns the Oracle error message text corresponding to the return code parameter RCODE. Use OERHMS in place of OERMSG in V7 OCI programs or when longer error messages must be returned.
SyntaxCALL OERMSG(RCODE, MSG)
Comments
The OERMSG routine is similar to OERHMS, because 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 connections cannot use OERMSG to obtain error message text. Use OERHMS instead. Pro*FORTRAN Precompiler programs that contain OCI calls also must use OERHMS.
The following example is a subroutine that reports Oracle errors:
SUBROUTINE ERRRPT(CURSOR, N)
INTEGER*2 CURSOR(32), N
CHARACTER*70 MSG_BUF
WRITE (*, 100) N, CURSOR(7), CURSOR(6)
100 FORMAT(1X, 'Oracle error on cursor', I3,
1 ': Code is ', I5, ', OP is ', I5)
CALL OERMSG(CURSOR(1), MSG_BUF)
WRITE (*, 200) MSG_BUF
200 FORMAT(1X, A70)
RETURN
END
Parameters
| Parameter Name | Type | Status |
| RCODE | INTEGER*2 | IN |
| MSG | CHARACTER*70 | OUT |
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, it is truncated.
See Also
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 OLON in new Oracle7 OCI programs.
SyntaxCALL OLON(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 make 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 6 for more information.
For example, to log on to Oracle:
INTEGER*2 LDA(32)
CHARACTER*20 USERID, PASSWD
..
. USERID = 'SCOTT'
PASSWD = 'TIGER'
CALL OLON(LDA, USERID, 5, PASSWD, 5, 0)
Parameters
| Parameter Name | Type | Status |
| LDA | INTEGER*2(32) | IN/OUT |
| USID | CHARACTER*n | IN |
| UIDL | INTEGER*4 | IN |
| PSWD | CHARACTER*n | IN |
| PSWDL | INTEGER*4 | IN |
| AUDIT | INTEGER*4 | IN |
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 the UID, this parameter can be omitted.
If the UID parameter does not include a password and the PSWD parameter is invalid, null, or omitted, the logon will fail with an error code in the return code field of the LDA.
PSWDL The length of the PSWD array.
AUDIT This parameter is not currently used. It should be passed as 0 or -1.
See Also
OLOG.
ONAME retrieves the names of select-list items in a SQL query statement. Use ODESCR in place of ONAME in new Oracle7 OCI programs.
SyntaxCALL ONAME(CURSOR, POS, TBUF, TBUFL, CBUF, CBUFL)
Comments
ONAME can be called only after the SQL statement has been parsed using OPARSE or 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. They 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 uses ONAME to obtain column names:
INTEGER*2 CURSOR(32), NBUFL, TBUFL
INTEGER SQLL
CHARACTER*240 NBUF
CHARACTER*(*) SQLSTM, TBUF
..
. PARAMETER(SQLSTM = 'SELECT * FROM EMP')
SQLL = LEN(SQLSTM)
* Parse the statement
. CALL OSQL3(CURSOR, SQLSTM, SQLL)
* Get the names of each items in the select list
DO 10 I = 1, 1000000
NBUFL = 240
CALL ONAME(CURSOR, I, TBUF, TBUFL, NBUF, NBUFL)
* Check for error or "no more data"
IF (CURSOR(7) .NE. 0) GOTO 20
WRITE (*, 9000) NBUF
9000 FORMAT(1X, A<NBUFL>)
10 CONTINUE
20 ...
Observe the WRITE statement in this example carefully. The ONAME routine blank-pads the returned string, but the actual length of the returned name is in the CBUFL parameter on return.
Note: ONAME provides a subset of the functionality of the ODESCR routine.
Parameters
| Parameter Name | Type | Status |
| CURSOR | INTEGER*2(32) | IN/OUT |
| POS | INTEGER*4 | IN |
| TBUF | CHARACTER*n | IN |
| TBUFL | INTEGER*2 | IN |
| CBUF | CHARACTER*n | OUT |
| CBUFL | INTEGER*2 | IN/OUT |
POS The position index of the select-list item in the SQL query. The left-most select-list item is position 1.
TBUF 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.
CBUF A character buffer within the program that receives 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, set CBUFL to the length of the CBUF buffer. 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 zero, no name is returned in CBUF. If the select-list item name is longer than the value pointed to by CBUFL, the returned name is truncated.
See Also
ORLON establishes communication between a user's program and Oracle. Use OLOG in place of ORLON in new Oracle7 OCI programs.
SyntaxCALL ORLON(LDA, HDA, UID, UIDL, [PSWD], [PSWDL], <AUDIT>)
Comments
An OCI program can connect 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 OCI program.
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:
* Establish two logon data areas and host data areas
INTEGER*2 LDA(32,2), HDA(128,2)
* Declare user ID arrays.
* (DPWD is a "dummy" for an unused parameter)
CHARACTER*14 UID1, UID2, DPWD, PWD2
INTEGER*4 UID1L, UID2L, PWD2L
..
. UID1 = 'SCOTT/TIGER@T:KERVMS:ORA60'
UID1L = 26
UID2 = 'SYSTEM@T:WRVMS:V60TSTD'
UID2L = 22
PWD2 = 'MANAGER'
PWD2L = 7
..
. CALL ORLON(LDA(1,1), HDA(1,1), UID1, UID1L, DPWD, -1, 0))
IF (LDA(7,1) .NE. 0) THEN
CALL ERRRPT(LDA(1,1))
...
* and later ..
. CALL ORLON(LDA(1,2), HDA(1,2), UID2, UID2L, PWD2, PWD2L, 0)
IF (LDA(7,2) .NE. 0) THEN
CALL ERRRPT(LDA(1,2))
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 system for any particular notes or restrictions that apply to your operating system.
Parameters
| Parameter Name | Type | Mode |
| LDA | INTEGER*2(32) | IN/OUT |
| HDA | INTEGER*2(128) | OUT |
| UID | CHARACTER*n | IN |
| UIDL | INTEGER*4 | IN |
| PSWD | CHARACTER*n | IN |
| PSWDL | INTEGER*4 | IN |
| AUDIT | INTEGER*4 | IN |
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 (for DECNet, in this case), 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 The string containing the password. If the password is specified as part of the string pointed to by UID, this parameter can be omitted.
PSWDL The length of the password.
AUDIT This parameter is not currently used. Pass it as zero or -1.
See Also
OLOG.
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.
SyntaxCALL OSQL3(CURSOR, SQLSTM, SQLL)
Comments
OSQL3 passes the SQL statement to Oracle for parsing. The parsed representation of the SQL statement is stored in the Oracle shared SQL statement cache, and state information about the cursor is stored in the private SQL area of the server. Subsequent OCI calls reference the SQL statement using the cursor name. A 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. Data Definition Language statements are executed immediately by OSQL3. For Data Manipulation Language statements, the operation is not executed until the OEXEC, OEXN, or OEXFET routine is called.
The following example shows how to call OSQL3:
INTEGER*2 CURSOR(32)
CHARACTER*(*) SQL_STATEMENT
INTEGER*4 SQL_LEN
...
C After connecting and opening the cursor..
. PARAMETER(SQL_STATEMENT = 'SELECT sname, grade FROM
1 student_table WHERE sid = :1')
CALL OSQL3(CURSOR, SQL_STATEMENT, SQL_LEN)
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 section "Cursor Data Area" ![[*]](jump.gif)
for a list of the information fields available in the cursor data area after an OSQL3 call.
Parameters
| Parameter Name | Type | Status |
| CURSOR | INTEGER*2(32) | IN/OUT |
| SQLSTM | CHARACTER*n | IN |
| SQLL | INTEGER*4 | IN |
SQLSTM A character string containing a SQL statement. The SQL statement may contain placeholders in any place where a constant is permitted. Placeholders, such as ":Employee_number", are indicated by placing a colon (:) immediately before the placeholder name. Placeholders are bound to program variables using the OBNDRV or OBNDRN routine.
SQLL Specifies the length of the SQL statement.
See Also
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |