OCL function reference v15.2.0.1

The following tables list the functions supported by the EDB OCL connector. Any and all header files must be supplied by the user. EDB Postgres Advanced Server doesn't supply any such files.

Connect, authorize, and initialize functions

FunctionDescription
OCIBreakAbort the specified OCL function.
OCIEnvCreateCreate an OCL environment.
OCIEnvInitInitialize an OCL environment handle.
OCIInitializeInitialize the OCL environment.
OCILogoffRelease a session.
OCILogonCreate a logon connection.
OCILogon2Create a logon session in various modes.
OCIResetReset the current operation/protocol.
OCIServerAttachEstablish an access path to a data source.
OCIServerDetachRemove access to a data source.
OCISessionBeginCreate a user session.
OCISessionEndEnd a user session.
OCISessionGetGet session from session pool.
OCISessionReleaseRelease a session.
OCITerminateDetach from shared memory subsystem.

Using the tnsnames.ora file

The OCIServerAttach and OCILogon methods use NET_SERVICE_NAME as a 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 your home directory for a file named .tnsnames.ora. If OCL doesn't find the .tnsnames.ora file in the home directory, it searches for tnsnames.ora on the path specified in TNS_ADMIN environment variable.

You can specify multiple descriptors (NET_SERVICE_NAME) in the tnsnames.ora file.

The sample tnsnames.ora file contains:

EDBX =
(DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 5444))
 (CONNECT_DATA = (SERVER = DEDICATED)(SID = edb))
)

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 localhost on port 5444.

A C program call to OCIServerAttach that uses the tnsnames.ora file looks 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.

Note

Multiple descriptors are also supported in tnsnames.ora.

Handle and descriptor functions

FunctionDescription
OCIAttrGetGet handle attributes. EDB Postgres 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
OCIAttrSetSet handle attributes. EDB Postgres 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
OCIDescriptorAllocAllocate and initialize a descriptor.
OCIDescriptorFreeFree an allocated descriptor.
OCIHandleAllocAllocate and initialize a handle.
OCIHandleFreeFree an allocated handle.
OCIParamGetGet a parameter descriptor.
OCIParamSetSet a parameter descriptor.

EDB_ATTR_EMPTY_STRINGS

By default, EDB Postgres Advanced Server treats 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.

ValueDescription
OCI_DEFAULTTreat an empty string as a NULL value.
EDB_EMPTY_STRINGS_NULLTreat an empty string as a NULL value.
EDB_EMPTY_STRINGS_EMPTYTreat an empty string as a string of zero length.

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

EDB_ATTR_HOLDABLE

EDB Postgres Advanced Server supports statements that execute as WITH HOLD cursors. The EDB_ATTR_HOLDABLE attribute specifies the statements that execute as WITH HOLD cursors. You can set the EDB_ATTR_HOLDABLE attribute to any of the following 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 EDB Postgres 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, EDB Postgres 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 cursor.
  • EDB_CURSOR_WITHOUT_XACT_BLK Don't begin a new transaction chain.
  • OCI_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 isn't 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 roll back. 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 can continue to fetch from that cursor. However, the database server doesn't persist open cursors when you roll back a transaction. If you try to fetch from a cursor after a ROLLBACK, the database server reports 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:

ub4 action = EDB_COMMIT_AFTER_CURSOR;

OCIAttrSet(stmt,
           OCI_HTYPE_STMT,
           &action,
           sizeof(action),
           EDB_ATTR_HOLD_CURSOR_ACTION,
           err);

OCIStmtExecute(...);
Note

Using EDB_COMMIT_AFTER_CURSOR commits any pending changes.

EDB_CURSOR_WITHOUT_XACT_BLK

If your application doesn't run properly with the extra commits added by EDB_COMMIT_AFTER_CURSOR, you can try setting EDB_ATTR_HOLD_CURSOR_ACTION to EDB_CURSOR_WITHOUT_XACT_BLK. With this action, the OCL doesn't begin a new transaction chain. If you create a WITH HOLD cursor immediately after committing or rolling back a transaction, the cursor is created in its own transaction, the database server commits that transaction, and the cursor persists.

You might still experience errors if the cursor declaration is not the first statement in a transaction. If you execute some other statement before declaring the cursor, the WITH HOLD cursor is created in a transaction block and can be rolled back if an error occurs (or if your application calls OCITransRollback()).

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 rolls back the current transaction whenever the server reports an error. You can override the automatic ROLLBACK with the edb_stmt_level_tx parameter, which preserves modifications in a transaction, even if one (or several) statements raise an error in 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

FunctionDescription
OCIBindByNameBind by name.
OCIBindByPosBind by position.
OCIBindDynamicSet additional attributes after bind.
OCIBindArrayOfStructBind an array of structures for bulk operations.
OCIDefineArrayOfStructSpecify the attributes of an array.
OCIDefineByPosDefine an output variable association.
OCIDefineDynamicSet additional attributes for define.
OCIDescribeAnyDescribe existing schema objects.
OCIStmtGetBindInfoGet bind and indicator variable names and handle.
OCIUserCallbackRegisterDefine a user-defined callback.

Statement functions

FunctionDescription
OCIStmtExecuteExecute a prepared SQL statement.
OCIStmtFetchFetch rows of data (deprecated).
OCIStmtFetch2Fetch rows of data.
OCIStmtPreparePrepare a SQL statement.
OCIStmtPrepare2Prepare a SQL statement.
OCIStmtReleaseRelease a statement handle.

Transaction functions

FunctionDescription
OCITransCommitCommit a transaction.
OCITransRollbackRoll back a transaction.

XA functions

FunctionDescription
xaoEnvReturn OCL environment handle.
xaoSvcCtxReturn OCL service context.

xaoSvcCtx

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 EDB Postgres Advanced Server database.

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

SqlNet=dbname specifies the database name.

Acc=P/username/password specifies the database username and password. You can omit the password. To do so, specify the field as Acc=P/username/.

AppName=app_id specifies a number that identifies the application.

The following is an example of the connection string:

Oracle_XA+HostName=192.168.1.1+PortNumber=1533+SqlNet=XE+Acc=P/user/password+AppName=1234

Date and datetime functions

FunctionDescription
OCIDateAddDaysAdd or subtract a number of days.
OCIDateAddMonthsAdd or subtract a number of months.
OCIDateAssignAssign a date.
OCIDateCheckCheck if the given date is valid.
OCIDateCompareCompare two dates.
OCIDateDaysBetweenFind the number of days between two dates.
OCIDateFromTextConvert a string to a date.
OCIDateGetDateGet the date portion of a date.
OCIDateGetTimeGet the time portion of a date.
OCIDateLastDayGet the date of the last day of the month.
OCIDateNextDayGet the date of the next day.
OCIDateSetDateSet the date portion of a date.
OCIDateSetTimeSet the time portion of a date.
OCIDateSysDateGet the current system date and time.
OCIDateToTextConvert a date to a string.
OCIDateTimeAssignPerform datetime assignment.
OCIDateTimeCheckCheck if the date is valid.
OCIDateTimeCompareCompare two datetime values.
OCIDateTimeConstructConstruct a datetime descriptor.
OCIDateTimeConvertConvert one datetime type to another.
OCIDateTimeFromArrayConvert an array of size OCI_DT_ARRAYLEN to an OCIDateTime descriptor.
OCIDateTimeFromTextConvert the given string to Oracle datetime type in the OCIDateTime descriptor according to the specified format.
OCIDateTimeGetDateGet the date portion of a datetime value.
OCIDateTimeGetTimeGet the time portion of a datetime value.
OCIDateTimeGetTimeZoneNameGet the time zone name portion of a datetime value.
OCIDateTimeGetTimeZoneOffsetGet the time zone (hour, minute) portion of a datetime value.
OCIDateTimeSubtractTake two datetime values as input and return their difference as an interval.
OCIDateTimeSysTimeStampGet the system current date and time as a timestamp with time zone.
OCIDateTimeToArrayConvert an OCIDateTime descriptor to an array.
OCIDateTimeToTextConvert the given date to a string according to the specified format.

Interval functions

FunctionDescription
OCIIntervalAddAdd two interval values.
OCIIntervalAssignCopy one interval value into another interval value.
OCIIntervalCompareCompare two interval values.
OCIIntervalGetDaySecondExtract days, hours, minutes, seconds and fractional seconds from an interval.
OCIIntervalSetDaySecondModify days, hours, minutes, seconds and fractional seconds in an interval.
OCIIntervalGetYearMonthExtract year and month values from an interval.
OCIIntervalSetYearMonthModify year and month values in an interval.
OCIIntervalDivideDivide OCIInterval values by OCINumber values.
OCIIntervalMultiplyMultiply OCIInterval values by OCINumber values.
OCIIntervalSubtractSubtract one interval value from another interval value.
OCIIntervalToTextExtrapolate a character string from an interval.
OCIIntervalCheckVerify the validity of an interval value.
OCIIntervalToNumberConvert an OCIInterval value into a OCINumber value.
OCIIntervalFromNumberConvert a OCINumber value into an OCIInterval value.
OCIDateTimeIntervalAddAdd an OCIInterval value to an OCIDatetime value, resulting in an OCIDatetime value.
OCIDateTimeIntervalSubSubtract an OCIInterval value from an OCIDatetime value, resulting in an OCIDatetime value.
OCIIntervalFromTextConvert a text string into an interval.
OCIIntervalFromTZConvert a time zone specification into an interval value.

Number functions

FunctionDescription
OCINumberAbsCompute the absolute value.
OCINumberAddAdds NUMBERs.
OCINumberArcCosCompute the arc cosine.
OCINumberArcSinCompute the arc sine.
OCINumberArcTanCompute the arc tangent.
OCINumberArcTan2Compute the arc tangent of two NUMBERs.
OCINumberAssignAssign one NUMBER to another.
OCINumberCeilCompute the ceiling of NUMBER.
OCINumberCmpCompare NUMBERs.
OCINumberCosCompute the cosine.
OCINumberDecDecrement a NUMBER.
OCINumberDivDivide two NUMBERs.
OCINumberExpRaise e to the specified NUMBER power.
OCINumberFloorCompute the floor of a NUMBER.
OCINumberFromIntConvert an integer to an Oracle NUMBER.
OCINumberFromRealConvert a real to an Oracle NUMBER.
OCINumberFromTextConvert a string to an Oracle NUMBER.
OCINumberHypCosCompute the hyperbolic cosine.
OCINumberHypSinCompute the hyperbolic sine.
OCINumberHypTanCompute the hyperbolic tangent.
OCINumberIncIncrement a NUMBER.
OCINumberIntPowerRaise a given base to an integer power.
OCINumberIsIntTest if a NUMBER is an integer.
OCINumberIsZeroTest if a NUMBER is zero.
OCINumberLnCompute the natural logarithm.
OCINumberLogCompute the logarithm to an arbitrary base.
OCINumberModModulo division.
OCINumberMulMultiply NUMBERs.
OCINumberNegNegate a NUMBER.
OCINumberPowerExponentiation to base e.
OCINumberPrecRound a NUMBER to a specified number of decimal places.
OCINumberRoundRound a NUMBER to a specified decimal place.
OCINumberSetPiInitialize a NUMBER to Pi.
OCINumberSetZeroInitialize a NUMBER to zero.
OCINumberShiftMultiply by 10, shifting specified number of decimal places.
OCINumberSignObtain the sign of a NUMBER.
OCINumberSinCompute the sine.
OCINumberSqrtCompute the square root of a NUMBER.
OCINumberSubSubtract NUMBERs.
OCINumberTanCompute the tangent.
OCINumberToIntConvert a NUMBER to an integer.
OCINumberToRealConvert a NUMBER to a real.
OCINumberToRealArrayConvert an array of NUMBER to a real array.
OCINumberToTextConverts a NUMBER to a string.
OCINumberTruncTruncate a NUMBER at a specified decimal place.

String functions

FunctionDescription
OCIStringAllocSizeGet allocated size of string memory in bytes.
OCIStringAssignAssign string to a string.
OCIStringAssignTextAssign text string to a string.
OCIStringPtrGet string pointer.
OCIStringResizeResize string memory.
OCIStringSizeGet string size.

Cartridge services and file I/O interface functions

FunctionDescription
OCIFileCloseClose an open file.
OCIFileExistsTest to see if the file exists.
OCIFileFlushWrite buffered data to a file.
OCIFileGetLengthGet the length of a file.
OCIFileInitInitialize the OCIFile package.
OCIFileOpenOpen a file.
OCIFileReadRead from a file into a buffer.
OCIFileSeekChange the current position in a file.
OCIFileTermTerminate the OCIFile package.
OCIFileWriteWrite buflen bytes into the file.

LOB functions

FunctionDescription
OCILobReadReturn a LOB value (or a portion of a LOB value).
OCILOBWriteAppendAdd data to a LOB value.
OCILobGetLengthReturn the length of a LOB value.
OCILobTrimTrim data from the end of a LOB value.
OCILobOpenOpen a LOB value for use by other LOB functions.
OCILobCloseClose a LOB value.

Miscellaneous functions

FunctionDescription
OCIClientVersionReturn client library version.
OCIErrorGetReturn error message.
OCIPGErrorGetReturn 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)
OCIPasswordChangeChange password.
OCIPingConfirm that the connection and server are active.
OCIServerVersionGet the Oracle version string.

Supported data types

FunctionDescription
ANSI_DATEANSI date
SQLT_AFCANSI fixed character
SQLT_AVCANSI variable character
SQLT_BDOUBLEBinary double
SQLT_BINBinary data
SQLT_BFLOATBinary float
SQLT_CHRCharacter string
SQLT_DATOracle date
SQLT_DATEANSI date
SQLT_FLTFloat
SQLT_INTInteger
SQLT_LBILong binary
SQLT_LNGLong
SQLT_LVBLonger long binary
SQLT_LVCLonger longs (character)
SQLT_NUMOracle numeric
SQLT_ODTOCL date type
SQLT_STRZero-terminated string
SQLT_TIMESTAMPTimestamp
SQLT_TIMESTAMP_TZTimestamp with time zone
SQLT_TIMESTAMP_LTZTimestamp with local time zone
SQLT_UINUnsigned integer
SQLT_VBIVCS format binary
SQLT_VCSVariable character
SQLT_VNUNumber with preceding length byte
SQLT_VSTOCL string type

export const _frontmatter = {"title":"OCL function reference"}