OCL Function Reference

The following tables list the functions supported by the OCL connector. Note that any and all header files must be supplied by the user. Advanced Server does not supply any such files.

Connect, Authorize and Initialize Functions

Function Description
OCIBreak Aborts the specified OCL function.
OCIEnvCreate Creates an OCL environment.
OCIEnvInit Initializes an OCL environment handle.
OCIInitialize Initializes the OCL environment.
OCILogoff Releases a session.
OCILogon Creates a logon connection.
OCILogon2 Creates a logon session in various modes.
OCIReset Resets the current operation/protocol.
OCIServerAttach Establishes an access path to a data source.
OCIServerDetach Removes access to a data source.
OCISessionBegin Creates a user session.
OCISessionEnd Ends a user session.
OCISessionGet Gets session from session pool.
OCISessionRelease Releases a session.
OCITerminate Detaches from shared memory subsystem.

Using the tnsnames.ora File

The OCIServerAttach and OCILogon method uses NET_SERVICE_NAME as connection descriptor specified in the dblink parameter of the tnsnames.ora file. Use the tnsnames.ora file (compatible with Oracle databases), to specify database connection details. OCL searches the user’s home directory for a file named .tnsnames.ora; if OCL doesn’t find the .tnsnames.ora file in the user’s home directory, it searches the tnsnames.ora on path specified in TNS_ADMIN environment variable.

Multiple descriptors (NET_SERVICE_NAME) can be specified in tnsnames.ora file.

The sample tnsnames.ora file contains:

  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 5444))

Any parameters not included in the files are ignored by the Open Client Library. In the example, SID refers to the database named edb, in the cluster running on the localhost on port 5444.

A C program call to OCIServerAttach that uses the tnsnames.ora file will look like:

static text *username = (text *) "enterprisedb";
static text *password = (text *) "edb";
static text *attach_str = "EDBX";
OCIServerAttach(srvhp, errhp, attach_str, strlen(attach_str), 0);

If you don’t have a tnsnames.ora file, supply the connection string in the form //localhost:5444/edbx.


Multiple Descriptors are also supported in tnsnames.ora.

Handle and Descriptor Functions

Function Description
OCIDescriptorAlloc Allocate and initialize a descriptor.
OCIDescriptorFree Free an allocated descriptor.
OCIHandleAlloc Allocate and initialize a handle.
OCIHandleFree Free an allocated handle.
OCIParamGet Get a parameter descriptor.
OCIParamSet Set a parameter descriptor.


By default, Advanced Server will treat an empty string as a NULL value. You can use the EDB_ATTR_EMPTY_STRINGS environment attribute to control the behavior of the OCL connector when mapping empty strings. To modify the mapping behavior, use the OCIAttrSet() function to set EDB_ATTR_EMPTY_STRINGS to one of the following:

Value Description
OCI_DEFAULT Treat an empty string as a NULL value.
EDB_EMPTY_STRINGS_NULL Treat an empty string as a NULL value.
EDB_EMPTY_STRINGS_EMPTY Treat an empty string as a string of zero length.

To find the value of EDB_ATTR_EMPTY_STRINGS, query OCIAttrGet().


Advanced Server supports statements that execute as WITH HOLD cursors. The EDB_ATTR_HOLDABLE attribute specifies which statements execute as WITH HOLD cursors. The EDB_ATTR_HOLDABLE attribute can be set to any of the following three values:

  • EDB_WITH_HOLD - execute as a WITH HOLD cursor
  • EDB_WITHOUT_HOLD - execute using a protocol-level prepared statement
  • OCI_DEFAULT - see the definition that follows

You can set the attribute in an OCIStmt handle or an OCIServer handle. When you create an OCIServer handle or an OCIStmt handle, the EDB_ATTR_HOLDABLE attribute for that handle is set to OCI_DEFAULT.

You can change the EDB_ATTR_HOLDABLE attribute for a handle by calling OCIAttrSet() and retrieve the attribute by calling OCIAttrGet().

When Advanced Server executes a SELECT statement, it examines the EDB_ATTR_HOLDABLE attribute in the OCIServer handle. If that attribute is set to EDB_WITH_HOLD, the query is executed as a WITH HOLD cursor.

If the EDB_ATTR_HOLDABLE attribute in the OCIServer handle is set to EDB_WITHOUT_HOLD, the query is executed as a normal prepared statement.

If the EDB_ATTR_HOLDABLE attribute in the OCIServer handle is set to OCI_DEFAULT, Advanced Server uses the value of the EDB_ATTR_HOLDABLE attribute in the OCIServer handle (if the EDB_ATTR_HOLDABLE attribute in the OCIServer is set to EDB_WITH_HOLD, the query executes as a WITH HOLD cursor, otherwise, the query executes as a protocol-prepared statement).


The EDB_HOLD_CURSOR_ACTION attribute alters the way WITH HOLD cursors are created using the OCL interface. You can set this attribute to any of the following values:

  • EDB_COMMIT_AFTER_CURSOR – commit the transaction after creating the cursor
  • EDB_CURSOR_WITHOUT_XACT_BLK – do not begin a new transaction chain
  • OCI_DEFAULT - see the definition that follows

The following describes the attribute values.


Each time you execute a statement, the OCL examines the transaction state on the database server. If a transaction is not already in progress, the OCL executes a BEGIN statement to create a new transaction block, and then executes the statement that you provide. The transaction block remains open until you call OCITransCommit() or OCITransRollback().

By default, the database server closes any open cursors when you commit or rollback. If you (or the OCL) declare a cursor that includes the WITH HOLD clause, the cursor result set is persisted on the database server, and you may continue to fetch from that cursor. However, the database server will not persist open cursors when you roll back a transaction. If you try to fetch from a cursor after a ROLLBACK, the database server will report an error.


If your application must read from a WITH HOLD cursor after rolling back a transaction, you can arrange for the OCL to commit the transaction immediately after creating the cursor by setting EDB_HOLD_CURSOR_ACTION to EDB_COMMIT_AFTER_CURSOR prior to creating such a cursor. For example:

ub4        action = EDB_COMMIT_AFTER_CURSOR;

OCIAttrSet(stmt, OCI_HTYPE_STMT, &action, sizeof(action),

OCIStmtExecute( ... );

It is important to understand that using EDB_COMMIT_AFTER_CURSOR will commit any pending changes.


If your application will not run properly with the extra commits added by EDB_COMMIT_AFTER_CURSOR, you may try setting EDB_ATTR_HOLD_CURSOR_ACTION to EDB_CURSOR_WITHOUT_XACT_BLK. With this action, the OCL will not begin a new transaction chain. If you create a WITH HOLD cursor immediately after committing or rolling back a transaction, the cursor will be created in its own transaction, the database server will commit that transaction, and the cursor will persist.

It is important to understand that you may still experience errors if the cursor declaration is not the first statement within a transaction – if you execute some other statement before declaring the cursor, the WITH HOLD cursor will be created in a transaction block and may be rolled back if an error occurs (or if your application calls OCITransRollback()).

Please note that you can set the EDB_HOLD_CURSOR_ACTION on the server level (OCIServer) or for each statement handle (OCIStmt). If the statement attribute is set to a value other than OCI_DEFAULT, the value is derived from the statement handle, otherwise (if the statement attribute is set to OCI_DEFAULT), the value is taken from the server handle. So you can define a server-wide default action by setting the attribute in the server handle, and leaving the attribute set to OCI_DEFAULT in the statement handles. You can use different values for each statement handle (or server handle) as you see fit.


Unless otherwise instructed, the OCL connector will ROLLBACK the current transaction whenever the server reports an error. If you choose, you can override the automatic ROLLBACK with the edb_stmt_level_tx parameter, which preserves modifications within a transaction, even if one (or several) statements raise an error within the transaction.

You can use the OCIServer attribute with OCIAttrSet() and OCIAttrGet() to enable or disable EDB_ATTR_STMT_LEVEL_TX. By default, edb_stmt_level_tx is disabled. To enable edb_stmt_level_tx, the client application must call OCIAttrSet():

OCIServer *server  = myServer;
ub1        enabled = 1;

OCIAttrSet(server, OCI_HTYPE_SERVER, &enabled,
  sizeof(enabled), EDB_ATTR_STMT_LEVEL_TX, err);

To disable edb_stmt_level_tx:

OCIServer *server  = myServer;
ub1        enabled = 0;

OCIAttrSet(server, OCI_HTYPE_SERVER, &enabled,
  sizeof(enabled), EDB_ATTR_STMT_LEVEL_TX, err);

Bind, Define and Describe Functions

Function Description
OCIBindByName Bind by name.
OCIBindByPos Bind by position.
OCIBindDynamic Set additional attributes after bind.
OCIBindArrayOfStruct Bind an array of structures for bulk operations.
OCIDefineArrayOfStruct Specify the attributes of an array.
OCIDefineByPos Define an output variable association.
OCIDefineDynamic Set additional attributes for define.
OCIDescribeAny Describe existing schema objects.
OCIStmtGetBindInfo Get bind and indicator variable names and handle.
OCIUserCallbackRegister Define a user-defined callback.

Statement Functions

Function Description
OCIStmtExecute Execute a prepared SQL statement.
OCIStmtFetch Fetch rows of data (deprecated).
OCIStmtFetch2 Fetch rows of data.
OCIStmtPrepare Prepare a SQL statement.
OCIStmtPrepare2 Prepare a SQL statement.
OCIStmtRelease Release a statement handle.

Transaction Functions

Function Description
OCITransCommit Commit a transaction.
OCITransRollback Roll back a transaction.

XA Functions

Function Description
xaoEnv Returns OCL environment handle.
xaoSvcCtx Returns OCL service context.


In order to use the xaoSvcCtx function, extensions in the xaoSvcCtx or xa_open connection string format must be provided as follows:

Oracle_XA{+<required_fields> ...}

Where required_fields are the following:

HostName=host_ip_address specifies the IP address of the Advanced Server database.

PortNumber=host_port_number specifies the port number on which Advanced Server is running.

SqlNet=dbname specifies the database name.

Acc=P/username/password specifies the database username and password. password may be omitted in which case the field is specified as Acc=P/username/.

AppName=app_id specifies a number that identifies the application.

The following is an example of the connection string:


Date and Datetime Functions

Function Description
OCIDateAddDays Add or subtract a number of days.
OCIDateAddMonths Add or subtract a number of months.
OCIDateAssign Assign a date.
OCIDateCheck Check if the given date is valid.
OCIDateCompare Compare two dates.
OCIDateDaysBetween Find the number of days between two dates.
OCIDateFromText Convert a string to a date.
OCIDateGetDate Get the date portion of a date.
OCIDateGetTime Get the time portion of a date.
OCIDateLastDay Get the date of the last day of the month.
OCIDateNextDay Get the date of the next day.
OCIDateSetDate Set the date portion of a date.
OCIDateSetTime Set the time portion of a date.
OCIDateSysDate Get the current system date and time.
OCIDateToText Convert a date to a string.
OCIDateTimeAssign Perform datetime assignment.
OCIDateTimeCheck Check if the date is valid.
OCIDateTimeCompare Compare two datetime values.
OCIDateTimeConstruct Construct a datetime descriptor.
OCIDateTimeConvert Convert one datetime type to another.
OCIDateTimeFromArray Convert an array of size OCI_DT_ARRAYLEN to an OCIDateTime descriptor.
OCIDateTimeFromText Convert the given string to Oracle datetime type in the OCIDateTime descriptor according to the specified format.
OCIDateTimeGetDate Get the date portion of a datetime value.
OCIDateTimeGetTime Get the time portion of a datetime value.
OCIDateTimeGetTimeZoneName Get the time zone name portion of a datetime value.
OCIDateTimeGetTimeZoneOffset Get the time zone (hour, minute) portion of a datetime value.
OCIDateTimeSubtract Take two datetime values as input and return their difference as an interval.
OCIDateTimeSysTimeStamp Get the system current date and time as a timestamp with time zone.
OCIDateTimeToArray Convert an OCIDateTime descriptor to an array.
OCIDateTimeToText Convert the given date to a string according to the specified format.

Interval Functions

Function Description
OCIIntervalAdd Adds two interval values.
OCIIntervalAssign Copies one interval value into another interval value.
OCIIntervalCompare Compares two interval values.
OCIIntervalGetDaySecond Extracts days, hours, minutes, seconds and fractional seconds from an interval.
OCIIntervalSetDaySecond Modifies days, hours, minutes, seconds and fractional seconds in an interval.
OCIIntervalGetYearMonth Extracts year and month values from an interval.
OCIIntervalSetYearMonth Modifies year and month values in an interval.
OCIIntervalDivide Implements division of OCIInterval values by OCINumber values.
OCIIntervalMultiply Implements multiplication of OCIInterval values by OCINumber values.
OCIIntervalSubtract Subtracts one interval value from another interval value.
OCIIntervalToText Extrapolates a character string from an interval.
OCIIntervalCheck Verifies the validity of an interval value.
OCIIntervalToNumber Converts an OCIInterval value into a OCINumber value.
OCIIntervalFromNumber Converts a OCINumber value into an OCIInterval value.
OCIDateTimeIntervalAdd Adds an OCIInterval value to an OCIDatetime value, resulting in an OCIDatetime value.
OCIDateTimeIntervalSub Subtracts an OCIInterval value from an OCIDatetime value, resulting in an OCIDatetime value.
OCIIntervalFromText Converts a text string into an interval.
OCIIntervalFromTZ Converts a time zone specification into an interval value.

Number Functions

Function Description
OCINumberAbs Compute the absolute value.
OCINumberAdd Adds NUMBERs.
OCINumberArcCos Compute the arc cosine.
OCINumberArcSin Compute the arc sine.
OCINumberArcTan Compute the arc tangent.
OCINumberArcTan2 Compute the arc tangent of two NUMBERs.
OCINumberAssign Assign one NUMBER to another.
OCINumberCeil Compute the ceiling of NUMBER.
OCINumberCmp Compare NUMBERs.
OCINumberCos Compute the cosine.
OCINumberDec Decrement a NUMBER.
OCINumberDiv Divide two NUMBERs.
OCINumberExp Raise e to the specified NUMBER power.
OCINumberFloor Compute the floor of a NUMBER.
OCINumberFromInt Convert an integer to an Oracle NUMBER.
OCINumberFromReal Convert a real to an Oracle NUMBER.
OCINumberFromText Convert a string to an Oracle NUMBER.
OCINumberHypCos Compute the hyperbolic cosine.
OCINumberHypSin Compute the hyperbolic sine.
OCINumberHypTan Compute the hyperbolic tangent.
OCINumberInc Increments a NUMBER.
OCINumberIntPower Raise a given base to an integer power.
OCINumberIsInt Test if a NUMBER is an integer.
OCINumberIsZero Test if a NUMBER is zero.
OCINumberLn Compute the natural logarithm.
OCINumberLog Compute the logarithm to an arbitrary base.
OCINumberMod Modulo division.
OCINumberMul Multiply NUMBERs.
OCINumberNeg Negate a NUMBER.
OCINumberPower Exponentiation to base e.
OCINumberPrec Round a NUMBER to a specified number of decimal places.
OCINumberRound Round a NUMBER to a specified decimal place.
OCINumberSetPi Initialize a NUMBER to Pi.
OCINumberSetZero Initialize a NUMBER to zero.
OCINumberShift Multiply by 10, shifting specified number of decimal places.
OCINumberSign Obtain the sign of a NUMBER.
OCINumberSin Compute the sine.
OCINumberSqrt Compute the square root of a NUMBER.
OCINumberSub Subtract NUMBERs.
OCINumberTan Compute the tangent.
OCINumberToInt Convert a NUMBER to an integer.
OCINumberToReal Convert a NUMBER to a real.
OCINumberToRealArray Convert an array of NUMBER to a real array.
OCINumberToText Converts a NUMBER to a string.
OCINumberTrunc Truncate a NUMBER at a specified decimal place.

String Functions

Function Description
OCIStringAllocSize Get allocated size of string memory in bytes.
OCIStringAssign Assign string to a string.
OCIStringAssignText Assign text string to a string.
OCIStringPtr Get string pointer.
OCIStringResize Resize string memory.
OCIStringSize Get string size.

Cartridge Services and File I/O Interface Functions

Function Description
OCIFileClose Close an open file.
OCIFileExists Test to see if the file exists.
OCIFileFlush Write buffered data to a file.
OCIFileGetLength Get the length of a file.
OCIFileInit Initialize the OCIFile package.
OCIFileOpen Open a file.
OCIFileRead Read from a file into a buffer.
OCIFileSeek Change the current position in a file.
OCIFileTerm Terminate the OCIFile package.
OCIFileWrite Write buflen bytes into the file.

LOB Functions

Function Description
OCILobRead Returns a LOB value (or a portion of a LOB value).
OCILOBWriteAppend Adds data to a LOB value.
OCILobGetLength Returns the length of a LOB value.
OCILobTrim Trims data from the end of a LOB value.
OCILobOpen Opens a LOB value for use by other LOB functions.
OCILobClose Closes a LOB value.

Miscellaneous Functions

Function Description
OCIClientVersion Return client library version.
OCIErrorGet Return error message.

Return native error messages reported by libpq or the server. The signature is:

sword OCIPGErrorGet(dvoid *hndlp, ub4 recordno, OraText *errcodep,ub4 errbufsiz, OraText *bufp, ub4 bufsiz, ub4 type)

OCIPasswordChange Change password.
OCIPing Confirm that the connection and server are active.
OCIServerVersion Get the Oracle version string.

Supported Data Types

Function Description
SQLT_AFC ANSI fixed character
SQLT_AVC ANSI variable character
SQLT_BDOUBLE Binary double
SQLT_BIN Binary data
SQLT_BFLOAT Binary float
SQLT_CHR Character string
SQLT_DAT Oracle date
SQLT_INT Integer
SQLT_LBI Long binary
SQLT_LVB Longer long binary
SQLT_LVC Longer longs (character)
SQLT_NUM Oracle numeric
SQLT_ODT OCL date type
SQLT_STR Zero-terminated string
SQLT_TIMESTAMP_TZ Timestamp with time zone
SQLT_TIMESTAMP_LTZ Timestamp with local time zone
SQLT_UIN Unsigned integer
SQLT_VBI VCS format binary
SQLT_VCS Variable character
SQLT_VNU Number with preceding length byte
SQLT_VST OCL string type