OCL Function Reference v13
The following tables list the functions supported by the EDB 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:
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:
If you don't have a tnsnames.ora
file, supply the connection string in the form //localhost:5444/edbx
.
Note
Multiple Descriptors are also supported in tnsnames.ora
.
Handle and Descriptor Functions
Function | Description |
---|---|
OCIAttrGet | Get handle attributes. Advanced server supports the following handle attributes: OCI_ATTR_USERNAME, OCI_ATTR_PASSWORD, OCI_ATTR_SERVER, OCI_ATTR_ENV, OCI_ATTR_SESSION, OCI_ATTR_ROW_COUNT, OCI_ATTR_CHARSET_FORM, OCI_ATTR_CHARSET_ID, EDB_ATTR_STMT_LEVEL_TX, OCI_ATTR_MODULE |
OCIAttrSet | Set handle attributes. Advanced server supports the following handle attributes: OCI_ATTR_USERNAME, OCI_ATTR_PASSWORD, OCI_ATTR_SERVER, OCI_ATTR_ENV, OCI_ATTR_SESSION, OCI_ATTR_ROW_COUNT, OCI_ATTR_CHARSET_FORM, OCI_ATTR_CHARSET_ID, EDB_ATTR_STMT_LEVEL_TX, OCI_ATTR_MODULE, OCI_ATTR_PREFETCH_ROWS |
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. |
EDB_ATTR_EMPTY_STRINGS
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()
.
EDB_ATTR_HOLDABLE
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 aWITH HOLD
cursorEDB_WITHOUT_HOLD
- execute using a protocol-level prepared statementOCI_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).
EDB_HOLD_CURSOR_ACTION
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 cursorEDB_CURSOR_WITHOUT_XACT_BLK
– do not begin a new transaction chainOCI_DEFAULT
- see the definition that follows
The following describes the attribute values.
OCI_DEFAULT
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.
EDB_COMMIT_AFTER_CURSOR
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:
It is important to understand that using EDB_COMMIT_AFTER_CURSOR
will commit any pending changes.
EDB_CURSOR_WITHOUT_XACT_BLK
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.
EDB_ATTR_STMT_LVL_TX
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()
:
To disable edb_stmt_level_tx
:
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. |
xaoSvcCtx
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. |
OCIPGErrorGet | 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 |
---|---|
ANSI_DATE | ANSI date |
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_DATE | ANSI date |
SQLT_FLT | Float |
SQLT_INT | Integer |
SQLT_LBI | Long binary |
SQLT_LNG | Long |
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 | Timestamp |
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 |
- On this page
- Connect, Authorize and Initialize Functions
- Handle and Descriptor Functions
- Bind, Define and Describe Functions
- Statement Functions
- Transaction Functions
- XA Functions
- Date and Datetime Functions
- Interval Functions
- Number Functions
- String Functions
- Cartridge Services and File I/O Interface Functions
- LOB Functions
- Miscellaneous Functions
- Supported Data Types