OCI returning - Register

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:

• their names are the provided bind names to the DML statement (by example, ':out1'). So any call to the functions OCI_GetXXX2() should be aware of it
• The columns detailed SQL attributes might be not all set or accurate. By example, the scale and precision are not set, the SQL type is the one chosen by OCILIB regarding the OCILIB object datatype and might be slightly different from the real one.

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;
}