OCILIB (C Driver for Oracle) 3.9.2
Functions
Oracle Returning feature

Detailed Description

OCILIB supports the Oracle feature 'Returning into' for DML statements.

Let's Oracle talk about this features:

'Using the RETURNING clause with a DML statement allows you to essentially combine two SQL statements into one, possibly saving you a server round-trip. This is accomplished by adding an extra clause to the traditional UPDATE, INSERT, and DELETE statements. The extra clause effectively adds a query to the DML statement. In the OCI, the values are returned to the application through the use of OUT bind variables.'

OCILIB implements this features by providing a set of functions that allows to register output placeholders for the returned values. Once the DML is executed with OCI_Execute(), the output returned data is available through a regular resultset object that can be fetched.

Note:
Array binding interface is also supported with 'returning into' DML statement. Every iteration (or row of given arrays) generates an resultset object. Once a resultset is fetched, the next on can be retrieved with OCI_GetNextResultset()
Note:
OCI_Long are not supported for 'returning into' clause .This is a limitation imposed by Oracle.
OCI_Column objects retrieved from output OCI_Resultset have the following particularities:
Example
#include "ocilib.h"

int main(void)
{
    OCI_Connection *cn;
    OCI_Statement  *st;
    OCI_Resultset  *rs;

    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
        return EXIT_FAILURE;

    cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
    st = OCI_StatementCreate(cn);

    OCI_Prepare(st, "update products set code = code+10 returning code into :i");
    OCI_RegisterInt(st, ":i");
    OCI_Execute(st);
  
    rs = OCI_GetResultset(st);

    while (OCI_FetchNext(rs))
        printf("%i\n", OCI_GetInt(rs, 1));
 
    printf("count : %i\n", OCI_GetRowCount(rs));
  
    OCI_Commit(cn);
    OCI_Cleanup();

    return EXIT_SUCCESS;
}

Functions

OCI_EXPORT OCI_Resultset *OCI_API OCI_GetNextResultset (OCI_Statement *stmt)
 Retrieve the next resultset from an executed DML statement using a 'SQL returning' clause.
OCI_EXPORT boolean OCI_API OCI_RegisterShort (OCI_Statement *stmt, const mtext *name)
 Register a short output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterUnsignedShort (OCI_Statement *stmt, const mtext *name)
 Register an unsigned short output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterInt (OCI_Statement *stmt, const mtext *name)
 Register an integer output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterUnsignedInt (OCI_Statement *stmt, const mtext *name)
 Register an unsigned integer output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterBigInt (OCI_Statement *stmt, const mtext *name)
 Register a big integer output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterUnsignedBigInt (OCI_Statement *stmt, const mtext *name)
 Register an unsigned big integer output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterString (OCI_Statement *stmt, const mtext *name, unsigned int len)
 Register a string output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterRaw (OCI_Statement *stmt, const mtext *name, unsigned int len)
 Register an raw output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterDouble (OCI_Statement *stmt, const mtext *name)
 Register a double output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterDate (OCI_Statement *stmt, const mtext *name)
 Register a date output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterTimestamp (OCI_Statement *stmt, const mtext *name, unsigned int type)
 Register a timestamp output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterInterval (OCI_Statement *stmt, const mtext *name, unsigned int type)
 Register an interval output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterObject (OCI_Statement *stmt, const mtext *name, OCI_TypeInfo *typinf)
 Register an object output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterLob (OCI_Statement *stmt, const mtext *name, unsigned int type)
 Register a lob output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterFile (OCI_Statement *stmt, const mtext *name, unsigned int type)
 Register a file output bind placeholder.
OCI_EXPORT boolean OCI_API OCI_RegisterRef (OCI_Statement *stmt, const mtext *name, OCI_TypeInfo *typinf)
 Register a Ref output bind placeholder.

Function Documentation

OCI_EXPORT OCI_Resultset* OCI_API OCI_GetNextResultset ( OCI_Statement stmt)

Retrieve the next resultset from an executed DML statement using a 'SQL returning' clause.

Parameters:
stmt- Statement handle
Note:
SQL statements with a 'returning' clause can return multiple resultsets. When arrays of program variables are binded to the statement, Oracle will execute the statement for every row (iteration). Each iteration generates a resultset that can be fetched like regular ones.
Returns:
A resultset handle on success otherwise NULL

Definition at line 789 of file resultset.c.

OCI_EXPORT boolean OCI_API OCI_RegisterShort ( OCI_Statement stmt,
const mtext *  name 
)

Register a short output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
Returns:
TRUE on success otherwise FALSE

Definition at line 3414 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterUnsignedShort ( OCI_Statement stmt,
const mtext *  name 
)

Register an unsigned short output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
Returns:
TRUE on success otherwise FALSE

Definition at line 3430 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterInt ( OCI_Statement stmt,
const mtext *  name 
)

Register an integer output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
Returns:
TRUE on success otherwise FALSE

Definition at line 3446 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterUnsignedInt ( OCI_Statement stmt,
const mtext *  name 
)

Register an unsigned integer output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
Returns:
TRUE on success otherwise FALSE

Definition at line 3462 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterBigInt ( OCI_Statement stmt,
const mtext *  name 
)

Register a big integer output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
Returns:
TRUE on success otherwise FALSE

Definition at line 3478 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterUnsignedBigInt ( OCI_Statement stmt,
const mtext *  name 
)

Register an unsigned big integer output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
Returns:
TRUE on success otherwise FALSE

Definition at line 3494 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterString ( OCI_Statement stmt,
const mtext *  name,
unsigned int  len 
)

Register a string output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
len- Max length of single string (in characters)
Returns:
TRUE on success otherwise FALSE

Definition at line 3510 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterRaw ( OCI_Statement stmt,
const mtext *  name,
unsigned int  len 
)

Register an raw output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
len- Max length of the buffer (in bytes)
Returns:
TRUE on success otherwise FALSE

Definition at line 3529 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterDouble ( OCI_Statement stmt,
const mtext *  name 
)

Register a double output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
Returns:
TRUE on success otherwise FALSE

Definition at line 3548 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterDate ( OCI_Statement stmt,
const mtext *  name 
)

Register a date output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
Returns:
TRUE on success otherwise FALSE

Definition at line 3564 of file statement.c.

References OCI_GetVersionConnection().

OCI_EXPORT boolean OCI_API OCI_RegisterTimestamp ( OCI_Statement stmt,
const mtext *  name,
unsigned int  type 
)

Register a timestamp output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
type- Timestamp type
Note:
See OCI_TimestampCreate() for possible values of parameter 'type'
Returns:
TRUE on success otherwise FALSE

Definition at line 3593 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterInterval ( OCI_Statement stmt,
const mtext *  name,
unsigned int  type 
)

Register an interval output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
type- Interval type
Note:
See OCI_IntervalCreate() for possible values of parameter 'type'
Returns:
TRUE on success otherwise FALSE

Definition at line 3642 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterObject ( OCI_Statement stmt,
const mtext *  name,
OCI_TypeInfo typinf 
)

Register an object output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
typinf- Type info handle
Returns:
TRUE on success otherwise FALSE

Definition at line 3687 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterLob ( OCI_Statement stmt,
const mtext *  name,
unsigned int  type 
)

Register a lob output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
type- Lob type
Note:
See OCI_LobCreate() for possible values of parameter 'type'
Returns:
TRUE on success otherwise FALSE

Definition at line 3706 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterFile ( OCI_Statement stmt,
const mtext *  name,
unsigned int  type 
)

Register a file output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
type- File type
Note:
See OCI_FileCreate() for possible values of parameter 'type'
Returns:
TRUE on success otherwise FALSE

Definition at line 3736 of file statement.c.

OCI_EXPORT boolean OCI_API OCI_RegisterRef ( OCI_Statement stmt,
const mtext *  name,
OCI_TypeInfo typinf 
)

Register a Ref output bind placeholder.

Parameters:
stmt- Statement handle
name- Output bind name
typinf- Type info handle
Returns:
TRUE on success otherwise FALSE

Definition at line 3766 of file statement.c.