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.
Note:
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 678 of file resultset.c.

References OCI_Statement::cur_rs, OCI_Statement::nb_rs, and OCI_Statement::rsts.

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 2563 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 2629 of file statement.c.

References OCI_Statement::con, and OCI_GetVersionConnection().

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 2617 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 2767 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 2539 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 2693 of file statement.c.

References OCI_Statement::con.

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 2745 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 2730 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 2602 of file statement.c.

References OCI_Statement::con.

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 2790 of file statement.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 2515 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 2587 of file statement.c.

References OCI_Statement::con.

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 2654 of file statement.c.

References OCI_Statement::con.

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 2575 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 2551 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 2527 of file statement.c.


Generated on Thu Jul 30 17:41:54 2009 for OCILIB (C Driver for Oracle) by  doxygen 1.5.4