Bind, Define, and Describe Functions

This section describes the bind, define, and describe functions.

OCIBindArrayOfStruct()

Purpose

This call sets up the skip parameters for a static array bind.

Syntax

sword OCIBindArrayOfStruct ( OCIBind     *bindp,
                             OCIError    *errhp,
                             ub4         pvskip, 
                             ub4         indskip, 
                             ub4         alskip, 
                             ub4         rcskip );

Parameters

bindp (IN/OUT)

The handle to a bind structure.

errhp (IN/OUT)

An error handle you can pass to OCIErrorGet() for diagnostic information in the event of an error.

pvskip (IN)

Skip parameter for the next data value.

indskip (IN)

Skip parameter for the next indicator value or structure.

alskip (IN)

Skip parameter for the next actual length value.

rcskip (IN)

Skip parameter for the next column-level return code value.

Comments

This call sets up the skip parameters necessary for a static array bind. It follows a call to OCIBindByName() or OCIBindByPos(). The bind handle returned by that initial bind call is used as a parameter for the OCIBindArrayOfStruct() call.

Related Functions

OCIBindByName(), OCIBindByPos()

OCIBindByName()

Purpose

Creates an association between a program variable and a placeholder in a SQL statement or PL/SQL block.

Syntax

sword OCIBindByName ( OCIStmt       *stmtp, 
                      OCIBind       **bindpp,
                      OCIError      *errhp,
                      CONST text    *placeholder,
                      sb4           placeh_len,
                      dvoid         *valuep,
                      sb4           value_sz,
                      ub2           dty,
                      dvoid         *indp,
                      ub2           *alenp,
                      ub2           *rcodep,
                      ub4           maxarr_len,
                      ub4           *curelep, 
                      ub4           mode ); 

Parameters

stmtp (IN/OUT)

The statement handle to the SQL or PL/SQL statement being processed.

bindpp (IN/OUT)

A pointer to save the pointer of a bind handle which is implicitly allocated by this call. The bind handle maintains all the bind information for this particular input value. The default encoding for the call depends on the UTF-16 setting in stmtp unless the mode parameter has a different value. The handle is freed implicitly when the statement handle is deallocated. On input, the value of the pointer must be NULL or a valid bind handle.

errhp (IN/OUT)

An error handle you can pass to OCIErrorGet() for diagnostic information in the event of an error.

placeholder (IN)

The placeholder, specified by its name, which maps to a variable in the statement associated with the statement handle. The encoding of placeholder should always be consistent with that of the environment. That is, if the statement is prepared in UTF-16, so is the placeholder. As a string type parameter, it should be cast as (text *) and terminated with NULL.

placeh_len (IN)

The length of the name specified in placeholder, in number of bytes regardless of the encoding.

valuep (IN/OUT)

The pointer to a data value or an array of data values of the type specified in the dty parameter. This data could be a UTF-16 (formerly known as UCS-2) string, if an OCIAttrSet() function has been called to set OCI_ATTR_CHARSET_ID as OCI_UTF16ID or the deprecated OCI_UCS2ID. OCI_UTF16ID is the new designation for OCI_UCS2ID.

Furthermore, as pointed out for OCIStmtPrepare(), the default encoding for the string type valuep will be in the encoding specified by the charset parameter of a previous call to OCIEnvNlsCreate(), unless users call OCIAttrSet() to manually reset the character set for the bind handle.

See Also:

"Bind Handle Attributes".

An array of data values can be specified for mapping into a PL/SQL table or for providing data for SQL multiple-row operations. When an array of bind values is provided, this is called an array bind in OCI terms.

For SQLT_NTY or SQLT_REF binds, the valuep parameter is ignored. The pointers to OUT buffers are set in the pgvpp parameter initialized by OCIBindObject().

value_sz(IN)

The size in bytes of the data value pointed to by dvoid pointer valuep. Although the bind buffer valuep could be of string type, the length is measured in number of bytes because the pointer passed down is of (dvoid *) type. In the case of an array bind, this is the maximum size of any element possible with the actual sizes being specified in the alenp parameter.

For descriptors, locators, or REFs, whose size is unknown to client applications use the size of the structure you are passing in; sizeof(OCILobLocator *).

dty (IN)

The datatype of the value(s) being bound. Named datatypes (SQLT_NTY) and REFs (SQLT_REF) are valid only if the application has been initialized in object mode. For named datatypes, or REFs, additional calls must be made with the bind handle to set up the datatype-specific attributes.

indp (IN/OUT)

Pointer to an indicator variable or array. For all datatypes except SQLT_NTY, this is a pointer to sb2 or an array of sb2s.

For SQLT_NTY, this pointer is ignored and the actual pointer to the indicator structure or an array of indicator structures is initialized in a subsequent call to OCIBindObject(). This parameter is ignored for dynamic binds.

See Also:

"Indicator Variables"

alenp (IN/OUT)

Pointer to array of actual lengths of array elements. Each element in alenp is the length of the data in the corresponding element in the bind value array before and after the execute. The length should be in bytes for strings passed in as a text type. This parameter is ignored for dynamic binds.

rcodep (OUT)

Pointer to array of column level return codes. This parameter is ignored for dynamic binds.

maxarr_len (IN)

The maximum possible number of elements of type dty in a PL/SQL binds. This parameter is not required for non-PL/SQL binds. If maxarr_len is nonzero, then either OCIBindDynamic() or OCIBindArrayOfStruct() can be invoked to set up additional bind attributes.

curelep (IN/OUT)

A pointer to the actual number of elements. This parameter is only required for PL/SQL binds.

mode (IN)

To maintain coding consistency, theoretically, this parameter can take all three possible values used by OCIStmtPrepare(). Since the encoding of bind variables should always be same as that of the statement containing this variable, an error will be raised if the user specify an encoding other than that of the statement. So the recommended setting for mode is OCI_DEFAULT, which will make the bind variable have the same encoding as its statement.

The valid modes are:

• OCI_DEFAULT - The default mode. The statement handle stmtp uses whatever is specified by its parent environment handle.

• OCI_BIND_SOFT - Soft bind mode. This mode increases the performance of the call. If this is the first bind or some input value like dty or value_sz is changed from the previous bind, this mode is ignored. An error is returned if the statement is not executed. Unexpected behavior results if the bind handle passed is not valid.

• OCI_DATA_AT_EXEC - When this mode is selected, the value_sz parameter defines the maximum size of the data that can ever be provided at runtime. The application must be ready to provide the OCI library runtime IN data buffers at any time and any number of times. Runtime data is provided in one of these two ways:

  • Callbacks using a user-defined function which must be registered with a subsequent call to OCIBindDynamic().

  • A polling mechanism using calls supplied by the OCI. This mode is assumed if no callbacks are defined.

    See Also:

    For more information about using the OCI_DATA_AT_EXEC mode, "Runtime Data Allocation and Piecewise Operations in OCI".

  When mode is set to OCI_DATA_AT_EXEC, do not provide values for valuep, indp, alenp, and rcodep in the main call. Pass zeroes for indp and alenp. Provide the values through the callback function registered using OCIBindDynamic().

  When the allocated buffers are not required any more, they should be freed by the client.

Comments

This call is used to perform a basic bind operation. The bind creates an association between the address of a program variable and a placeholder in a SQL statement or PL/SQL block. The bind call also specifies the type of data which is being bound, and may also indicate the method by which data will be provided at runtime.

Encoding is determined by either the bind handle using the setting in the statement handle as default, or you can override the setting by specifying the mode parameter explicitly.

This function also implicitly allocates the bind handle indicated by the bindpp parameter. If a non-NULL pointer is passed in **bindpp, the OCI assumes that this points to a valid handle that has been previously allocated with a call to OCIHandleAlloc() or OCIBindByName().

Data in an OCI application can be bound to placeholders statically or dynamically. Binding is static when all the IN bind data and the OUT bind buffers are well-defined just before the execute. Binding is dynamic when the IN bind data and the OUT bind buffers are provided by the application on demand at execute time to the client library. Dynamic binding is indicated by setting the mode parameter of this call to OCI_DATA_AT_EXEC.

Both OCIBindByName() and OCIBindByPos() take as a parameter a bind handle, which is implicitly allocated by the bind call A separate bind handle is allocated for each placeholder the application is binding.

Additional bind calls may be required to specify particular attributes necessary when binding certain datatypes or handling input data in certain ways:

• If arrays of structures are being utilized, OCIBindArrayOfStruct() must be called to set up the necessary skip parameters.

• If data is being provided dynamically at runtime, and the application will be using user-defined callback functions, OCIBindDynamic() must be called to register the callbacks.

• If lengths in alenp greater than 64 Kbytes are required, use OCIBindDynamic().

• If a named datatype is being bound, OCIBindObject() must be called to specify additional necessary information.

• If a statement with RETURNING clause is used, a call to OCIBindDynamic() must follow this call.

Related Functions

OCIBindDynamic(), OCIBindObject(), OCIBindArrayOfStruct()

OCIBindByPos()

Purpose

Creates an association between a program variable and a placeholder in a SQL statement or PL/SQL block.

Syntax

sword OCIBindByPos ( OCIStmt      *stmtp, 
                     OCIBind      **bindpp,
                     OCIError     *errhp,
                     ub4          position,
                     dvoid        *valuep,
                     sb4          value_sz,
                     ub2          dty,
                     dvoid        *indp,
                     ub2          *alenp,
                     ub2          *rcodep,
                     ub4          maxarr_len,
                     ub4          *curelep, 
                     ub4          mode );

Parameters

stmtp (IN/OUT)

The statement handle to the SQL or PL/SQL statement being processed.

bindpp (IN/OUT)

An address of a bind handle which is implicitly allocated by this call. The bind handle maintains all the bind information for this particular input value. The handle is freed implicitly when the statement handle is deallocated. On input, the value of the pointer must be NULL or a valid bind handle.

errhp (IN/OUT)

An error handle you can pass to OCIErrorGet() for diagnostic information in the event of an error.

position (IN)

The placeholder attributes are specified by position if OCIBindByPos() is being called.

valuep (IN/OUT)

An address of a data value or an array of data values of the type specified in the dty parameter. An array of data values can be specified for mapping into a PL/SQL table or for providing data for SQL multiple-row operations. When an array of bind values is provided, this is called an array bind in OCI terms.

For a LOB, the buffer pointer must be a pointer to a LOB locator of type OCILobLocator. Give the address of the pointer.

For SQLT_NTY or SQLT_REF binds, the valuep parameter is ignored. The pointers to OUT buffers are set in the pgvpp parameter initialized by OCIBindObject().

If the OCI_ATTR_CHARSET_ID attribute is set to OCI_UTF16ID (replaces the deprecated OCI_UCS2ID, which is retained for backward compatibility), all data passed to and received with the corresponding bind call is assumed to be in UTF-16 encoding.

See Also:

Bind Handle Attributes.

value_sz (IN)

The size of a data value. In the case of an array bind, this is the maximum size of any element possible with the actual sizes being specified in the alenp parameter.

For descriptors, locators, or REFs, whose size is unknown to client applications, use the size of the structure you are passing: for example, sizeof (OCILobLocator *).

For a PL/SQL block, a value_sz greater than the width of a CHAR column will cause an error, because of how PL/SQL processes the CHAR dataype.

dty (IN)

The datatype of the value(s) being bound. Named datatypes (SQLT_NTY) and REFs (SQLT_REF) are valid only if the application has been initialized in object mode. For named datatypes, or REFs, additional calls must be made with the bind handle to set up the datatype-specific attributes.

indp (IN/OUT)

Pointer to an indicator variable or array. For all datatypes, this is a pointer to sb2 or an array of sb2 values. The only exception is SQLT_NTY, when this pointer is ignored and the actual pointer to the indicator structure or an array of indicator structures is initialized by OCIBindObject(). indp is ignored for dynamic binds. If valuep is an OUT parameter, then you must set indp to point to OCI_IND_NULL.

See Also:

"Indicator Variables"

alenp (IN/OUT)

Pointer to array of actual lengths of array elements. Each element in alenp is the length (in bytes, unless the data in valuep is in Unicode, when it is in codepoints) of the data in the corresponding element in the bind value array before and after the execute. This parameter is ignored for dynamic binds. If valuep is an OUT parameter, then you must set alenp to point to 0.

Note:

If alenp is greater than value_sz, data will be skipped.

rcodep (OUT)

Pointer to an array of column level return codes. This parameter is ignored for dynamic binds.

maxarr_len (IN)

The maximum possible number of elements of type dty in a PL/SQL binds. This parameter is not required for non-PL/SQL binds. If maxarr_len is nonzero, then either OCIBindDynamic() or OCIBindArrayOfStruct() can be invoked to set up additional bind attributes.

curelep (IN/OUT)

A pointer to the actual number of elements. This parameter is only required for PL/SQL binds.

mode (IN)

The valid modes for this parameter are:

• OCI_DEFAULT - This is default mode.

• OCI_BIND_SOFT - Soft bind mode. This mode increases the performance of the call. If this is the first bind or some input value like dty or value_sz is changed from the previous bind, this mode is ignored. An error is returned if the statement is not executed. Unexpected behavior results if the bind handle passed is not valid.

• OCI_DATA_AT_EXEC - When this mode is selected, the value_sz parameter defines the maximum size of the data that can ever be provided at runtime. The application must be ready to provide the OCI library runtime IN data buffers at any time and any number of times. Runtime data is provided in one of the two ways:

  • Callbacks using a user-defined function which must be registered with a subsequent call to OCIBindDynamic().

  • A polling mechanism using calls supplied by the OCI. This mode is assumed if no callbacks are define.

    See Also:

    For more information about using the OCI_DATA_AT_EXEC mode, see the section "Runtime Data Allocation and Piecewise Operations in OCI".

  When mode is set to OCI_DATA_AT_EXEC, do not provide values for valuep, indp, alenp, and rcodep in the main call. Pass zeroes for indp and alenp. Provide the values through the callback function registered using OCIBindDynamic().

  When the allocated buffers are not required any more, they should be freed by the client.

Comments

This call is used to perform a basic bind operation. The bind creates an association between the address of a program variable and a placeholder in a SQL statement or PL/SQL block. The bind call also specifies the type of data which is being bound, and may also indicate the method by which data will be provided at runtime.

This function also implicitly allocates the bind handle indicated by the bindpp parameter. If a non-NULL pointer is passed in **bindpp, the OCI assumes that this points to a valid handle that has been previously allocated with a call to OCIHandleAlloc() or OCIBindByPos().

Data in an OCI application can be bound to placeholders statically or dynamically. Binding is static when all the IN bind data and the OUT bind buffers are well-defined just before the execute. Binding is dynamic when the IN bind data and the OUT bind buffers are provided by the application on demand at execute time to the client library. Dynamic binding is indicated by setting the mode parameter of this call to OCI_DATA_AT_EXEC.

Both OCIBindByName() and OCIBindByPos() take as a parameter a bind handle, which is implicitly allocated by the bind call A separate bind handle is allocated for each placeholder the application is binding.

Additional bind calls may be required to specify particular attributes necessary when binding certain datatypes or handling input data in certain ways:

• If arrays of structures are being utilized, OCIBindArrayOfStruct() must be called to set up the necessary skip parameters.

• If data is being provided dynamically at runtime, and the application will be using user-defined callback functions, OCIBindDynamic() must be called to register the callbacks.

• If lengths in alenp greater than 64Kbytes are required, use OCIBindDynamic().

• If a named datatype is being bound, OCIBindObject() must be called to specify additional necessary information.

• If a statement with RETURNING clause is used, a call to OCIBindDynamic() must follow this call.

Related Functions

OCIBindDynamic(), OCIBindObject(), OCIBindArrayOfStruct()

OCIBindDynamic()

Purpose

This call is used to register user callbacks for dynamic data allocation.

Syntax

sword OCIBindDynamic ( OCIBind     *bindp,
                       OCIError    *errhp,
                       dvoid       *ictxp, 
                       OCICallbackInBind         (icbfp)(/*_
                                dvoid            *ictxp,
                                OCIBind          *bindp,
                                ub4              iter, 
                                ub4              index, 
                                dvoid            **bufpp,
                                ub4              *alenp,
                                ub1              *piecep, 
                                dvoid            **indpp */),
                                dvoid            *octxp,
                       OCICallbackOutBind        (ocbfp)(/*_
                                dvoid            *octxp,
                                OCIBind          *bindp,
                                ub4              iter, 
                                ub4              index, 
                                dvoid            **bufpp, 
                                ub4              **alenpp,
                                ub1              *piecep,
                                dvoid            **indpp, 
                                ub2              **rcodepp _*/)   );

Parameters

bindp (IN/OUT)

A bind handle returned by a call to OCIBindByName() or OCIBindByPos().

errhp (IN/OUT)

An error handle you can pass to OCIErrorGet() for diagnostic information in the event of an error.

ictxp (IN)

The context pointer required by the call back function icbfp.

icbfp (IN)

The callback function which returns a pointer to the IN bind value or piece at run time. The callback takes in the following parameters:

ictxp (IN/OUT)

The context pointer for this callback function.

bindp (IN)

The bind handle passed in to uniquely identify this bind variable.

iter (IN)

0-based execute iteration value.

index (IN)

Index of the current array, for an array bind in PL/SQL. For SQL it is the row index. The value is 0-based and not greater than

™p parameter of the bind call.

bufpp (OUT)

The pointer to the buffer or storage. For descriptors, *bufpp contains a pointer to the descriptor. For example if you define

OCILobLocator    *lobp;

then you set *bufpp to lobp, not *lobp.

For REFs, pass the address of the ref; that is, pass &my_ref for *bufpp.

If the OCI_ATTR_CHARSET_ID attribute is set to OCI_UTF16ID (replaces the deprecated OCI_UCS2ID, which is retained for backward compatibility), all data passed to and received with the corresponding bind call is assumed to be in UTF-16 encoding.

See Also:

"Bind Handle Attributes".

alenp (OUT)

A pointer to a storage for OCI to fill in the size of the bind value/piece after it has been read. For descriptors, pass the size of the pointer to the descriptor; for example, sizeof(OCILobLocator *).

piecep (OUT)

Which piece of the bind value. This can be one of the following values OCI_ONE_PIECE, OCI_FIRST_PIECE, OCI_NEXT_PIECE and OCI_LAST_PIECE. For datatypes that do not support piecewise operations, you must pass OCI_ONE_PIECE or an error will be generated.

indp (OUT)

Contains the indicator value. This is a pointer to either an sb2 value or a pointer to an indicator structure for binding named datatypes.

octxp (IN)

The context pointer required by the callback function ocbfp.

ocbfp (IN)

The callback function which returns a pointer to the OUT bind value or piece at run time. The callback takes in the following parameters:

octxp (IN/OUT)

The context pointer for this call back function.

bindp (IN)

The bind handle passed in to uniquely identify this bind variable.

iter (IN)

0-based execute iteration value.

index (IN)

For PL/SQL index of the current array, for an array bind. For SQL, the index is the row number in the current iteration. It is 0-based, and must not be greater than curelep parameter of the bind call.

bufpp (OUT)

A pointer to a buffer to write the bind value/piece.

If the OCI_ATTR_CHARSET_ID attribute is set to OCI_UTF16ID (replaces the deprecated OCI_UCS2ID, which is retained for backward compatibility), all data passed to and received with the corresponding bind call is assumed to be in UTF-16 encoding. For more information, refer to "Bind Handle Attributes".

alenpp (IN/OUT)

A pointer to a storage for OCI to fill in the size of the bind value/piece after it has been read. It is in bytes except for Unicode encoding (if the OCI_ATTR_CHARSET_ID attribute is set to OCI_UTF16ID), when it is in codepoints.

piecep (IN/OUT)

Returns a piece value from the callback (application) to Oracle, as follows:

• IN - The value can be OCI_ONE_PIECE or OCI_NEXT_PIECE.

• OUT - Depends on the IN value:

If IN value is OCI_ONE_PIECE, then OUT value can be OCI_ONE_PIECE or OCI_FIRST_PIECE

If IN value is OCI_NEXT_PIECE then OUT value can be OCI_NEXT_PIECE or OCI_LAST_PIECE.

indpp (OUT)

Returns a pointer to contain the indicator value which either an sb2 value or a pointer to an indicator structure for named datatypes.

rcodepp (OUT)

Returns a pointer to contains the return code.

Comments

This call is used to register user-defined callback functions for providing or receiving data if OCI_DATA_AT_EXEC mode was specified in a previous call to OCIBindByName() or OCIBindByPos().

The callback function pointers must return OCI_CONTINUE if it the call is successful. Any return code other than OCI_CONTINUE signals that the client wishes to terminate processing immediately.

When passing the address of a storage area, make sure that the storage area will exist even after the application returns from the callback. This means that you should not allocate such storage on the stack.

Related Functions

OCIBindByName(), OCIBindByPos()

OCIBindObject()

Purpose

This function sets up additional attributes which are required for a named datatype (object) bind.

Syntax

sword OCIBindObject ( OCIBind          *bindp,
                      OCIError         *errhp, 
                      CONST OCIType    *type,
                      dvoid            **pgvpp, 
                      ub4              *pvszsp, 
                      dvoid            **indpp, 
                      ub4              *indszp, );

Parameters

bindp (IN/OUT)

The bind handle returned by the call to OCIBindByName() or OCIBindByPos().

errhp (IN/OUT)

An error handle you can pass to OCIErrorGet() for diagnostic information in the event of an error.

type (IN)

Points to the TDO which describes the type of the program variable being bound. Retrieved by calling OCITypeByName(). Optional for REFs in SQL, but required for REFs in PL/SQL.

pgvpp (IN/OUT)

Address of the program variable buffer. For an array, pgvpp points to an array of addresses. When the bind variable is also an OUT variable, the OUT Named Datatype value or REF is allocated in the Object Cache, and a REF is returned.

pgvpp is ignored if the OCI_DATA_AT_EXEC mode is set. Then the Named Datatype buffers are requested at runtime. For static array binds, skip factors may be specified using the OCIBindArrayOfStruct() call. The skip factors are used to compute the address of the next pointer to the value, the indicator structure and their sizes.

pvszsp (OUT) [optional]

Points to the size of the program variable. The size of the named datatype is not required on input. For an array, pvszsp is an array of ub4s. On return, for OUT bind variables, this points to size(s) of the Named Datatypes and REFs received. pvszsp is ignored if the OCI_DATA_AT_EXEC mode is set. Then the size of the buffer is taken at runtime.

indpp (IN/OUT) [optional]

Address of the program variable buffer containing the parallel indicator structure. For an array, points to an array of pointers. When the bind variable is also an OUT bind variable, memory is allocated in the object cache, to store the OUT indicator values. At the end of the execute when all OUT values have been received, indpp points to the pointers to these newly allocated indicator structures. Required only for SQLT_NTY binds. indpp is ignored if the OCI_DATA_AT_EXEC mode is set. Then the indicator is requested at runtime.

indszp (IN/OUT)

Points to the size of the IN indicator structure program variable. For an array, it is an array of sb2s. On return for OUT bind variables, this points to sizes of the received OUT indicator structures. indszp is ignored if the OCI_DATA_AT_EXEC mode is set. Then the indicator size is requested at runtime.

Comments

This function sets up additional attributes which binding a named datatype or a REF. An error will be returned if this function is called when the OCI environment has been initialized in non-object mode.

This call takes as a parameter a type descriptor object (TDO) of datatype OCIType for the named datatype being defined. The TDO can be retrieved with a call to OCITypeByName().

If the OCI_DATA_AT_EXEC mode was specified in OCIBindByName() or OCIBindByPos(), the pointers to the IN buffers are obtained either using the callback icbfp registered in the OCIBindDynamic() call or by the OCIStmtSetPieceInfo() call.

The buffers are dynamically allocated for the OUT data. The pointers to these buffers are returned either by

• calling ocbfp() registered by the OCIBindDynamic()

• or, by setting the pointer to the buffer in the buffer passed in by OCIStmtSetPieceInfo() called when OCIStmtExecute() returned OCI_NEED_DATA.

The memory of these client library-allocated buffers must be freed when not in use anymore by using the OCIObjectFree() call.

Related Functions

OCIBindByName(), OCIBindByPos()

OCIDefineArrayOfStruct()

Purpose

This call specifies additional attributes necessary for a static array define, used in an array of structures (multi-row, multi-column) fetch.

Syntax

sword OCIDefineArrayOfStruct ( OCIDefine   *defnp,
                               OCIError    *errhp,
                               ub4         pvskip, 
                               ub4         indskip, 
                               ub4         rlskip,
                               ub4         rcskip );

Parameters

defnp (IN/OUT)

The handle to the define structure which was returned by a call to OCIDefineByPos().

errhp (IN/OUT)

An error handle you can pass to OCIErrorGet() for diagnostic information in the event of an error.

pvskip (IN)

Skip parameter for the next data value.

indskip (IN)

Skip parameter for the next indicator location.

rlskip (IN)

Skip parameter for the next return length value.

rcskip (IN)

Skip parameter for the next return code.

Comments

This call follows a call to OCIDefineByPos(). If the application is binding an array of structures involving objects, it must call OCIDefineObject() first, and then call OCIDefineArrayOfStruct().

Related Functions

OCIDefineByPos(), OCIDefineObject()

OCIDefineByPos()

Purpose

Associates an item in a select-list with the type and output data buffer.

Syntax

sword OCIDefineByPos ( OCIStmt     *stmtp, 
                       OCIDefine   **defnpp,
                       OCIError    *errhp,
                       ub4         position,
                       dvoid       *valuep,
                       sb4         value_sz,
                       ub2         dty,
                       dvoid       *indp,
                       ub2         *rlenp,
                       ub2         *rcodep,
                       ub4         mode );

Parameters

stmtp (IN/OUT)

A handle to the requested SQL query operation.

defnpp (IN/OUT)

A pointer to a pointer to a define handle. If this parameter is passed as NULL, this call implicitly allocates the define handle. In the case of a redefine, a non-NULL handle can be passed in this parameter. This handle is used to store the define information for this column.

Note:

The user must keep track of this pointer. If a second call to OCIDefineByPos() is made for the same column position, there is no guarantee that the same pointer is returned.

errhp (IN/OUT)

An error handle you can pass to OCIErrorGet() for diagnostic information in the event of an error.

position (IN)

The position of this value in the select list. Positions are 1-based and are numbered from left to right.

valuep (IN/OUT)

A pointer to a buffer or an array of buffers of the type specified in the dty parameter. A number of buffers can be specified when results for more than one row are desired in a single fetch call.

For a LOB, the buffer pointer must be a pointer to a LOB locator of type OCILobLocator. Give the address of the pointer.

value_sz (IN)

The size of each valuep buffer in bytes. If the data is stored internally in VARCHAR2 format, the number of characters desired, if different from the buffer size in bytes, may be additionally specified by using OCIAttrSet().

In a multibyte conversion environment, a truncation error will be generated if the number of bytes specified is insufficient to handle the number of characters desired.

If the OCI_ATTR_CHARSET_ID attribute is set to OCI_UTF16ID (replaces the deprecated OCI_UCS2ID, which is retained for backward compatibility), all data passed to and received with the corresponding define call is assumed to be in UTF-16 encoding.

See Also:

"Bind Handle Attributes"

dty (IN)

The datatype. Named datatype (SQLT_NTY) and REF (SQLT_REF) are valid only if the environment has been initialized in object mode.

SQLT_CHAR and SQLT_LNG can be specified for CLOB columns, and SQLT_BIN sand SQLT_LBI for BLOB columns.

See Also:

For a listing of datatype codes and values, refer to Chapter 3, "Datatypes"

indp (IN)

pointer to an indicator variable or array. For scalar datatypes, pointer to sb2 or an array of sb2s. Ignored for SQLT_NTY defines. For SQLT_NTY defines, a pointer to a named datatype indicator structure or an array of named datatype indicator structures is associated by a subsequent OCIDefineObject() call.

See Also:

"Indicator Variables"

rlenp (IN/OUT)

Pointer to array of length of data fetched. Each element in rlenp is the length of the data (in bytes, unless the data in valuep is in Unicode, when it is in codepoints) in the corresponding element in the row after the fetch.

rcodep (OUT)

Pointer to array of column-level return codes

mode (IN)

The valid modes are:

• OCI_DEFAULT - This is the default mode.

• OCI_DEFINE_SOFT - Soft define mode. This mode increases the performance of the call. If this is the first define or some input parameter like dty or value_sz is changed from the previous define, this mode is ignored. Unexpected behavior results if a non-valid define handle is passed. An error is returned if the statement is not executed.

• OCI_DYNAMIC_FETCH - For applications requiring dynamically allocated data at the time of fetch, this mode must be used. You can define a callback using the OCIDefineDynamic() call. The value_sz parameter defines the maximum size of the data that will be provided at runtime. When the client library needs a buffer to return the fetched data, the callback will be invoked to provide a run-time buffer into which a piece or the whole data will be returned.

Comments

This call defines an output buffer which will receive data retrieved from Oracle. The define is a local step which is necessary when a SELECT statement returns data to your OCI application.

This call also implicitly allocates the define handle for the select-list item. If a non-NULL pointer is passed in *defnpp, the OCI assumes that this points to a valid handle that has been previously allocated with a call to OCIHandleAlloc() or OCIDefineByPos(). This would be true in the case of an application which is redefining a handle to a different addresses so it can reuse the same define handle for multiple fetches.

Defining attributes of a column for a fetch is done in one or more calls. The first call is to OCIDefineByPos(), which defines the minimal attributes required to specify the fetch.

Following the call to OCIDefineByPos() additional define calls may be necessary for certain datatypes or fetch modes:

• A call to OCIDefineArrayOfStruct() is necessary to set up skip parameters for an array fetch of multiple columns.

• A call to OCIDefineObject() is necessary to set up the appropriate attributes of a named datatype (that is, object or collection) or REF fetch. In this case the data buffer pointer in OCIDefineByPos() is ignored.

• Both OCIDefineArrayOfStruct() and OCIDefineObject() must be called after OCIDefineByPos() in order to fetch multiple rows with a column of named datatypes.

For a LOB define, the buffer pointer must be a pointer to a LOB locator of type OCILobLocator, allocated by the OCIDescriptorAlloc() call. LOB locators, and not LOB values, are always returned for a LOB column. LOB values can then be fetched using OCI LOB calls on the fetched locator. This same mechanism is true for all descriptor datatypes.

For NCHAR (fixed and varying length), the buffer pointer must point to an array of bytes sufficient for holding the required NCHAR characters.

Nested table columns are defined and fetched like any other named datatype.

When defining an array of descriptors or locators, you should pass in an array of pointers to descriptors or locators.

When doing an array define for character columns, you should pass in an array of character buffers.

If the mode parameter is this call is set to OCI_DYNAMIC_FETCH, the client application can fetch data dynamically at runtime. Runtime data can be provided in one of two ways:

• callbacks using a user-defined function which must be registered with a subsequent call to OCIDefineDynamic(). When the client library needs a buffer to return the fetched data, the callback will be invoked and the runtime buffers provided will return a piece or the whole data.

  • a polling mechanism using calls supplied by the OCI. This mode is assumed if no callbacks are defined. In this case, the fetch call returns the OCI_NEED_DATA error code, and a piecewise polling method is used to provide the data.

    See Also:

    • For more information about using the OCI_DYNAMIC_FETCH mode, see the section "Runtime Data Allocation and Piecewise Operations in OCI".

    • For more information about defines, see "Overview of Defining in OCI".

Related Functions

OCIDefineArrayOfStruct(), OCIDefineDynamic(), OCIDefineObject()

OCIDefineDynamic()

Purpose

This call is used to set the additional attributes required if the OCI_DYNAMIC_FETCH mode was selected in OCIDefineByPos().

Syntax

sword OCIDefineDynamic ( OCIDefine   *defnp,
                         OCIError    *errhp,
                         dvoid       *octxp, 
                         OCICallbackDefine       (ocbfp)(/*_
                                  dvoid          *octxp,
                                  OCIDefine      *defnp,
                                  ub4            iter, 
                                  dvoid          **bufpp,
                                  ub4            **alenpp,
                                  ub1            *piecep,
                                  dvoid          **indpp,
                                  ub2            **rcodep _*/)  );

Parameters

defnp (IN/OUT)

The handle to a define structure returned by a call to OCIDefineByPos().

errhp (IN/OUT)

An error handle you can pass to OCIErrorGet() for diagnostic information in the event of an error.

octxp (IN)

Points to a context for the callback function.

ocbfp (IN)

Points to a callback function. This is invoked at runtime to get a pointer to the buffer into which the fetched data or a piece of it will be retrieved. The callback also specifies the indicator, the return code and the lengths of the data piece and indicator.

Caution:

When working with callback parameters, it is important to keep in mind what is meant by IN and OUT for the parameter mode. Normally, in an OCI function, an IN parameter refers to data being passed to Oracle, and an OUT parameter refers to data coming back from Oracle. In the case of callbacks, this is reversed. IN means data is coming from Oracle into the callback, and OUT means data is coming out of the callback and going to Oracle.

The callback parameters are listed next:

octxp (IN/OUT)

A context pointer passed as an argument to all the callback functions.

defnp (IN)

The define handle.

iter (IN)

Which row of this current fetch; 0-based.

bufpp (OUT)

Returns a pointer to a buffer to store the column value, that is, *bufpp points to some appropriate storage for the column value.

alenpp (IN/OUT)

Used by the application to set the size of the storage it is providing in *bufpp. After data is fetched into the buffer, alenpp indicates the actual size of the data in bytes.

piecep (IN/OUT)

Returns a piece value from the callback (application) to Oracle, as follows:

• IN - The value can be OCI_ONE_PIECE or OCI_NEXT_PIECE.

• OUT - Depends on the IN value:

If IN value is OCI_ONE_PIECE, then OUT value can be OCI_ONE_PIECE or OCI_FIRST_PIECE

If IN value is OCI_NEXT_PIECE then OUT value can be OCI_NEXT_PIECE or OCI_LAST_PIECE

indpp (IN)

Indicator variable pointer

rcodep (IN)

Return code variable pointer

Comments

This call is used to set the additional attributes required if the OCI_DYNAMIC_FETCH mode has been selected in a call to OCIDefineByPos(). If OCI_DYNAMIC_FETCH mode was selected, and the call to OCIDefineDynamic() is skipped, then the application can fetch data piecewise using OCI calls (OCIStmtGetPieceInfo() and OCIStmtSetPieceInfo()). For more information about OCI_DYNAMIC_FETCH mode, see the section "Runtime Data Allocation and Piecewise Operations in OCI".

Related Functions

OCIDefineByPos()

OCIDefineObject()

Purpose

Sets up additional attributes necessary for a named datatype or REF define.

Syntax

sword OCIDefineObject ( OCIDefine       *defnp,
                        OCIError        *errhp,
                        CONST OCIType   *type,
                        dvoid           **pgvpp, 
                        ub4             *pvszsp, 
                        dvoid           **indpp, 
                        ub4             *indszp );

Parameters

defnp (IN/OUT)

A define handle previously allocated in a call to OCIDefineByPos().

errhp (IN/OUT)

An error handle you can pass to OCIErrorGet() for diagnostic information in the event of an error.

type (IN) [optional]

Points to the Type Descriptor Object (TDO) which describes the type of the program variable. Only used for program variables of type SQLT_NTY. This parameter is optional, and may be passed as NULL if it is not being used.

pgvpp (IN/OUT)

Points to a pointer to a program variable buffer. For an array, pgvpp points to an array of pointers. Memory for the fetched named datatype instance(s) is dynamically allocated in the object cache. At the end of the fetch when all the values have been received, pgvpp points to the pointer(s) to these newly allocated named datatype instance(s). The application must call OCIObjectFree() to deallocate the named datatype instance(s) when they are no longer needed.

Note:

If the application wants the buffer to be implicitly allocated in the cache, *pgvpp should be passed in as NULL.

pvszsp (IN/OUT)

Points to the size of the program variable. For an array, it is an array of ub4s.

indpp (IN/OUT)

Points to a pointer to the program variable buffer containing the parallel indicator structure. For an array, points to an array of pointers. Memory is allocated to store the indicator structures in the object cache. At the end of the fetch when all values have been received, indpp points to the pointer(s) to these newly allocated indicator structure(s).

indszp (IN/OUT)

Points to the size(s) of the indicator structure program variable. For an array, it is an array of ub4s.

Comments

This function follows a call to OCIDefineByPos() to set initial define information. This call sets up additional attributes necessary for a Named Datatype define. An error will be returned if this function is called when the OCI environment has been initialized in non-Object mode.

This call takes as a parameter a type descriptor object (TDO) of datatype OCIType for the named datatype being defined. The TDO can be retrieved with a call to OCIDescribeAny().

Related Functions

OCIDefineByPos()

OCIDescribeAny()

Purpose

Describes existing schema and subschema objects.

Syntax

sword OCIDescribeAny ( OCISvcCtx       *svchp,
                       OCIError        *errhp,
                       dvoid           *objptr,
                       ub4             objptr_len,
                       ub1             objptr_typ,
                       ub1             info_level,
                       ub1             objtyp,
                       OCIDescribe     *dschp );

Parameters

svchp (IN)

A service context handle.

errhp (IN/OUT)

An error handle you can pass to OCIErrorGet() for diagnostic information in the event of an error.

objptr (IN)

This parameter can be:

1. A string containing the name of the object to be described. Must be in the encoding specified by the charset parameter of a previous call to OCIEnvNlsCreate().

2. A pointer to a REF to the TDO (for a type).

3. A pointer to a TDO (for a type).

These cases are distinguished by passing the appropriate value for objptr_typ. This parameter must be non-NULL.

In case 1, the string containing the object name should be in the format name1[.name2 ...][@linkname], such as hr.employees.employee_id@mydb. Database links are only allowed to Oracle8i or later databases. The object name is interpreted by the following SQL rules:

• If only name1 is entered and objtyp is equal to OCI_PTYPE_SCHEMA, then the name refers to the named schema. The Oracle database must be release 8.1 or later.

• If only name1 is entered and objtyp is equal to OCI_PTYPE_DATABASE, then the name refers to the named database. When describing a remote database with database_name@db_link_name, the remote Oracle database must be release 8.1 or later.

• If only name1 is entered and objtyp is not equal to OCI_PTYPE_SCHEMA or OCI_PTYPE_DATABASE, then the name refers to the named object (of type table, view, procedure, function, package, type, synonym, sequence) in the current schema of the current user. When connected to an Oracle7 Server, the only valid types are procedure and function.

• If name1.name2.name3 ... is entered, the object name refers to a schema or subschema object in the schema named name1. For example, in the string scott.emp.deptno, scott is the name of the schema, emp is the name of a table in the schema, and deptno is the name of a column in the table.

objnm_len (IN)

The length of the name string pointed to by objptr. Must be nonzero if a name is passed. Can be zero if objptr is a pointer to a TDO or its REF.

objptr_typ (IN)

The type of object passed in objptr. Valid values are:

• OCI_OTYPE_NAME, if objptr points to the name of a schema object

• OCI_OTYPE_REF, if objptr is a pointer to a REF to a TDO

• OCI_OTYPE_PTR, if objptr is a pointer to a TDO

info_level (IN)

Reserved for future extensions. Pass OCI_DEFAULT.

objtyp (IN)

The type of schema object being described. Valid values are:

• OCI_PTYPE_TABLE, for tables

• OCI_PTYPE_VIEW, for views

• OCI_PTYPE_PROC, for procedures

• OCI_PTYPE_FUNC, for functions

• OCI_PTYPE_PKG, for packages

• OCI_PTYPE_TYPE, for types

• OCI_PTYPE_SYN, for synonyms

• OCI_PTYPE_SEQ, for sequences

• OCI_PTYPE_SCHEMA, for schemas

• OCI_PTYPE_DATABASE, for databases

• OCI_PTYPE_UNK, for unknown schema objects

dschp (IN/OUT)

A describe handle that is populated with describe information about the object after the call. Must be non-NULL.

Comments

This is a generic describe call that describes existing schema objects: tables, views, synonyms, procedures, functions, packages, sequences, types, schemas, and databases. This call also describes subschema objects, such as a column in a table. This call populates the describe handle with the object-specific attributes which can be obtained through an OCIAttrGet() call.

An OCIParamGet() on the describe handle returns a parameter descriptor for a specified position. Parameter positions begin with 1. Calling OCIAttrGet() on the parameter descriptor returns the specific attributes of a stored procedure or function parameter, or a table column descriptor. These subsequent calls do not need an extra round trip to the server because the entire schema object description is cached on the client side by OCIDescribeAny(). Calling OCIAttrGet() on the describe handle also returns the total number of positions.

If the OCI_ATTR_DESC_PUBLIC attribute is set on the describe handle, then the object named is looked up as a public synonym when the object does not exist in the current schema and only name1 is specified.

Related Functions

OCIAttrGet(), OCIParamGet()

OCIStmtGetBindInfo()

Purpose

Gets the bind and indicator variable names.

Syntax

sword OCIStmtGetBindInfo ( OCIStmt      *stmtp,
                           OCIError     *errhp,
                           ub4          size,
                           ub4          startloc,
                           sb4          *found,
                           text         *bvnp[],
                           ub1          bvnl[],
                           text         *invp[],
                           ub1          inpl[],
                           ub1          dupl[],
                           OCIBind      *hndl[] );

Parameters

stmtp (IN)

The statement handle prepared by OCIStmtPrepare().

errhp (IN)

An error handle you can pass to OCIErrorGet() for diagnostic information in the event of an error.

size (IN)

The number of elements in each array.

startloc (IN)

Position of the bind variable at which to start getting bind information.

found (IN)

abs(found) gives the total number of bind variables in the statement irrespective of the start position. Positive value if the number of bind variables returned is less than the size provided, otherwise negative.

bvnp (OUT)

Array of pointers to hold bind variable names. Will be in the encoding specified by the charset parameter of a previous call to OCIEnvNlsCreate().

bvnl (OUT)

Array to hold the length of the each bvnp element. The length is in bytes.

invp (OUT)

Array of pointers to hold indicator variable names. Must be in the encoding specified by the charset parameter of a previous call to OCIEnvNlsCreate().

inpl (OUT)

Array of pointers to hold the length of the each invp element. In number of bytes.

dupl (OUT)

An array whose element value is 0 or 1 depending on whether the bind position is duplicate of another.

hndl (OUT)

An array which returns the bind handle if binds have been done for the bind position. No handle is returned for duplicates.

Comments

This call returns information about bind variables after a statement has been prepared. This includes bind names, indicator names, and whether or not binds are duplicate binds. This call also returns an associated bind handle if there is one. The call sets the found parameter to the total number of bind variables and not just the number of distinct bind variables.

OCI_NO_DATA will be returned if the statement has no bind variables or if the starting bind position specified by the you in the invocation does not exist in the statement.

This function does not include SELECT INTO list variables, because they are not considered to be binds.

The statement must have been prepared with a call to OCIStmtPrepare() prior to this call. The encoding setting in the statement handle will determine whether Unicode strings will be retrieved.

This call is processed locally.

Related Functions

OCIStmtPrepare()