Programmer's Guide to the Oracle7 Server Call Interface

Library

Product

Contents

Index

Additional OCI Functions (C)

These older functions are still available in the current Oracle7 OCI library. However, they might not be available in future versions of the Oracle Call Interface.

odsc

odsc() describes select-list items for dynamic SQL queries. The odsc() function returns internal datatype and size information for a specified select-list item. Use odescr() in place of odsc() in Oracle7 or later OCI programs.

Syntax

odsc(struct cda_def *cursor, sword pos,
     sb2 *dbsize, [sb2 *fsize], [sb2 *rcode],
     [sb2 *dbtype], [text *cbuf],
     [sb2 *cbufl], [sb2 *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 function is used for dynamic SQL queries, that is, queries in which the number of select-list items and their datatypes and sizes may not be known until runtime.

The odsc() function 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 indicate success (zero) or failure (non-zero) of the odsc() call.

The odsc() function 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; ename's index is 1. The following example shows how you call odsc() to describe the two select-list items in this SQL statement:

struct cda_def *cursor;

sb2 ename_len, sal_len, ename_type, sal_type;

sb2 col_name_len, max_disp_len;

text col_name[240];

...

odsc(&cursor, 1, &ename_len, (sb2 *) -1, (sb2 *) -1,

     &ename_type, col_name, &col_name_len, &max_disp_len);

/* Make use of values returned. */

odsc(&cursor, 2, &sal_len, (sb2 *) -1, (sb2 *) -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 will 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 Table	Type	Status
cursor	struct cda_def *	IN/OUT
pos	sword	IN
dbsize	sb2 *	OUT
fsize	sb2 *	IN 1
rcode	sb2 *	IN 1
dbtype	sb2 *	OUT
cbuf	text *	OUT
cbuflen	sb2 *	IN/OUT
dsize	sb2 *	OUT

Note 1. These are OUT parameters for oexfet(), ofetch(), or ofen().

cursor A pointer to the cursor data area for the statement being described.

pos The position index of the select-list item in the SQL query. Each item is referenced by position index, starting at 1 for the first (or left-most) item. 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 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. For LONG and LONG RAW columns, zero is returned.

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 character data before storing the values in the database.

Oracle recommends using the rlen parameter of odefin() to obtain column return lengths. This is more efficient than calling odsc() after a fetch.

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 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 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 cbuf buffer receives the name of the select-list item (name of the column or wording of the expression). If cbuf is passed as NULL, the column or expression name is not returned.

cbufl Set cbufl to the length of cbuf on the call. If cbufl is not specified (that is, passed as NULL) or if the value contained in cbufl is zero, then the column name is not returned. The column name is truncated if it is longer than cbufl.

On return from odsc(), cbufl contains the length of the returned string.

dsize This parameter 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

odescr().

oermsg

oermsg() returns the Oracle error message text corresponding to the return code (rcode). Use oerhms() instead of oermsg() in new Oracle7 OCI programs, or wherever error messages longer than 70 bytes must be returned.

Syntax

oermsg(ub2 rcode, text *msg);

Comments

The oermsg() function 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 connections should not use the oermsg() function to obtain error message text. Use the oerhms() function instead. Also, precompiler programs that contain OCI calls must use oerhms() instead of oermsg().

The following example shows a function that is used to report Oracle errors:

void

errrpt(struct cda_def *cur)

{

  char msg_buf[71];

  printf("Oracle error: code is %d, op is %d\n",

         (int) cur->rc, (int) cur->fc);

  oermsg(cur->rc, msg_buf);

  printf("%s\n", (char *) msg_buf);

}

Parameters

Parameter Name	Type	Mode
rcode	ub2	IN
msg	text *	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 A pointer to a program buffer to receive the error message text. The returned message text is null terminated. The maximum message size that can be returned is 70 bytes. Longer messages are truncated.

See Also

oerhms().

olon

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() instead of olon() in new Oracle7 OCI programs.

Syntax

olon(struct lda_def *lda, text *uid, [sword uidl],
     [text *pswd], [sword pswdl], <sword 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 user ID using the same LDA, you must first log off using the ologof() function, then log on again using olon(), with the different user ID. For multiple concurrent connections using different LDAs, use the orlon() function.

olon() cannot be used in a program that mixes OCI calls and precompiler statements. See the description of sqllda() in Chapter 4 for more information.

OCI programs can use orlon(), with the hda parameter set to the null pointer (0), as a direct replacement for olon(). For example, to log on to Oracle:

Lda_Def lda;

text uid[32], pwd[32];

strcpy(uid, "system");

strcpy(pwd, "manager");

if (olon(&lda, uid, -1, pwd, -1, 0))

{

    printf("Cannot logon as %s\/%s\n", uid, pwd);

    exit(1);

}

Parameters

Parameter Name	Type	Mode
lda	struct lda_def *	IN/OUT
uid	text *	IN
uidl	sword	IN
pswd	text *	IN
pswdl	sword	IN
audit	sword	IN

lda A pointer to 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. A zero indicates a successful logon.

uid A pointer to 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 simply `/' 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 string pointed to by uid. This parameter can be omitted if the uid string is null terminated.

pswd A pointer to a string containing the password. If the password is specified as part of 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 string pointed to by pswd. This parameter can be omitted if the pswd string is null terminated.

audit This parameter is not currently used. It should be passed as -1.

See Also

olog().

oname

oname() retrieves the names of select-list items in a SQL query statement. Use odescr() instead of oname() in new Oracle7 OCI programs.

Syntax

oname(struct cda_def *cursor, sword pos,
    <sb1 *tbuf>, <sb2 *tbufl>,
    sb1 *cbuf, sb2 *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. Indices are numbered sequentially from left to right, beginning with 1. If you specify a position index 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:

struct cda_def *cursor;    /* cursor data area */

text name_buf[241];        /* output buffer */

sb2 i, nb_len;

...

osql3(&cursor, "SELECT * FROM EMP", -1);

for (nb_len = 240, i = 1;

  !oname(&cursor, i, NULL, (sb2 *) 0, name_buf, 

    &nb_len); 

  nb_len = 240, i++) 

    printf("%*.*s\n", (int) nb_len, (int) nb_len,
           (char *) name_buf);

Observe the printf() statement in this example carefully. The oname() function does not null terminate the returned string.

Note: oname() provides only a limited subset of the functionality of the odescr() function.

Parameters

Parameter Name	Type	Mode
cursor	struct cda_def *	IN/OUT
pos	sword	IN
tbuf	text *	IN
tbufl	sb2 *	IN
cbuf	text *	OUT
cbufl	sb2 *	IN/OUT

cursor A pointer to the 64-byte cursor data area for the query. The oname() function uses the cursor address to obtain the names from the parsed SQL statement in the Oracle shared SQL cache.

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

tbuf This parameter is included only for Oracle Version 2 compatibility. For later versions, it should be passed as NULL.

tbufl This parameter is included only for Oracle Version 2 compatibility. For later versions, it should be passed as (sb2 *) -1.

cbuf A pointer to a data buffer within the program that receives the name of the select-list item. If cbufl is passed as zero, the name is not stored. The name returned in cbuf is blank padded to the length specified in the cbufl input parameter. It is not null terminated.

cbufl A pointer to a short integer. Before calling oname(), this parameter must be set to the length of the cbuf buffer. After oname() completes, cbuflen contains the length of the name that was returned in cbuf. 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

odescr().

orlon

orlon() establishes concurrent communications between an OCI program and an Oracle database. Use olog() instead of orlon() in new Oracle7 OCI programs.

Syntax

orlon(struct lda_def *lda, ub1 *hda, text *uid,
      [sword uidl], [text *pswd], [sword pswdl],
      <sword audit>);

Comments

An OCI program can connect to one or more Oracle instances multiple times. Communication takes place using the logon data area and the host data area defined within the program. The orlon() function connects the LDA to Oracle.

A 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. Each concurrent connection requires one LDA-HDA pair.

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

After the orlon() call, the HDA and the LDA must remain at the same program address they occupied at the time orlon() was called.

For example:

#include <stdlib.h>

Lda_Def lda[2];        /* establish two logon data areas */

ub1 hda[2][256];                        /* and two HDA's */

text *uid1 = "SCOTT/TIGER@T:KERVMS:V7_R2";

text *uid2 = "SYSTEM@T:KR2VMS:V7_R3";

text *pwd = "MANAGER";

...

/* first connect as scott */

if (orlon(&lda[0], &hda[0], uid1, -1, (text *) 0, -1, 0))

{

  error_handler(&lda[0]);

  exit(EXIT_FAILURE);

}

...

/* and later as the system manager */ 

if (orlon(&lda[1], &hda[1], uid2, -1, pwd, -1, 0))

{

  error_handler(&lda[1]);

  exit(EXIT_FAILURE);

}

When the program has issued an orlon() call, a subsequent ologof() call using the same LDA commits all outstanding transactions for that connection. If a program fails to disconnect or terminates abnormally, then all outstanding transactions are rolled back.

The LDA return code field indicates the result of the orlon() call. A zero return code indicates a successful connection.

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

Parameters

Parameter Name	Type	Mode
lda	struct lda_def *	IN/OUT
hda	ub1 *	OUT
uid	text *	IN
uidl	sword	IN
pswd	text *	IN
pswdl	sword	IN
audit	sword	IN

lda A pointer to the LDA specified in the olog() call that was used to make this connection to Oracle.

hda A pointer to a host data area struct. See Chapter 2 for more information on host data areas.

uid Specifies a string containing the username, 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 username and separate it from the username with a '/'. Put the host machine identifier after the username or the password, preceded by the '@' sign.

If the password is not included in this parameter, it must be in the pswd parameter. Examples of valid uid parameters are

name

name/password

name@d:nodename:dbname

name/password@d:nodename:dbname

The following example is not a correct example of a uid:

name@d:nodename:dbname/password

uidl The length of the string pointed to by uid. If the string pointed to by uid is null terminated, this parameter should be passed as -1.

pswd A pointer to a string containing the password. If the password is specified as part of the string pointed to by uid, this parameter should be passed as 0.

pswdl The length of the password. If the string pointed to by pswd is null or null terminated, this parameter should be passed as -1.

audit This parameter is no longer supported; the only permissible values are 0 or -1.

See Also

olog().

osql3

osql3() parses a SQL statement or a PL/SQL block and associates it with a cursor. The osql3() function also executes Data Definition Language statements. Use oparse() in place of osql3() in new Oracle7 OCI programs.

Syntax

osql3(struct cda_def *cursor, text *sqlstm,
      [sword 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 function. Data Definition Language statements are executed when osql3() is called. Data Manipulation Language statements and queries are not executed until you call the oexec() function.

The following example opens a cursor and parses a SQL statement. The osql3() call associates the SQL statement with the cursor.

lda_def lda;

struct cda_def cursor;

text *sql_stmt =

    "DELETE FROM emp WHERE empno = :EMPLOYEE_NUMBER";

...

oopen(&lda, &cursor, (text *) 0, 0, 0, (text *) 0, 0);

osql3(&cursor, sql_stmt, -1);

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" for a list of the information fields available in the cursor data area after an osql3() call.

Parameters

Parameter Name	Type	Mode
cursor	struct cda_def *	IN/OUT
sqlstm	text *	IN
sqll	sword	IN

cursor A pointer to a cursor data area specified in the oopen() call.

sqlstm A pointer to a string containing a SQL statement.

sqll Specifies the length of the SQL statement. If the SQL statement string pointed to by sqlstm is null terminated, this parameter can be omitted.

See Also

oparse().

Prev Next

Copyright © 1996 Oracle Corporation. All Rights Reserved.

Library

Product

Contents

Index