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:

• 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 data type and might be slightly different from the real one.

Example

#include "ocilib.h"

/* requires script demo/products.sql */

void err_handler(OCI_Error *err)
{
    printf("%s\n", OCI_ErrorGetString(err));
}

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

    if (!OCI_Initialize(err_handler, 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 name = name || ' updated' returning code into :code");
    OCI_RegisterInt(st, ":code");
    OCI_Execute(st);

    rs = OCI_GetResultset(st);
    while (OCI_FetchNext(rs))
    {
        printf("element with code %d has been updated\n", OCI_GetInt(rs, 1));
    }

    printf("Number of affected rows : %d\n", OCI_GetAffectedRows(st));

    OCI_ExecuteStmt(st, "select count(*) from products where name like '%updated'");
    rs = OCI_GetResultset(st);
    OCI_FetchNext(rs);
    printf("%i elements updated in DB\n", OCI_GetInt(rs, 1));

    OCI_Commit(cn);

    OCI_StatementFree(st);
    OCI_ConnectionFree(cn);
    OCI_Cleanup();

    return EXIT_SUCCESS;
}

Functions
OCI_EXPORT OCI_Resultset *OCI_API	OCI_GetNextResultset (OCI_Statement *stmt)
	Retrieve the next available resultset. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterNumber (OCI_Statement *stmt, const otext *name)
	Register a register output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterShort (OCI_Statement *stmt, const otext *name)
	Register a short output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterUnsignedShort (OCI_Statement *stmt, const otext *name)
	Register an unsigned short output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterInt (OCI_Statement *stmt, const otext *name)
	Register an integer output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterUnsignedInt (OCI_Statement *stmt, const otext *name)
	Register an unsigned integer output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterBigInt (OCI_Statement *stmt, const otext *name)
	Register a big integer output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterUnsignedBigInt (OCI_Statement *stmt, const otext *name)
	Register an unsigned big integer output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterString (OCI_Statement *stmt, const otext *name, unsigned int len)
	Register a string output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterRaw (OCI_Statement *stmt, const otext *name, unsigned int len)
	Register an raw output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterDouble (OCI_Statement *stmt, const otext *name)
	Register a double output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterFloat (OCI_Statement *stmt, const otext *name)
	Register a float output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterDate (OCI_Statement *stmt, const otext *name)
	Register a date output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterTimestamp (OCI_Statement *stmt, const otext *name, unsigned int type)
	Register a timestamp output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterInterval (OCI_Statement *stmt, const otext *name, unsigned int type)
	Register an interval output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterObject (OCI_Statement *stmt, const otext *name, OCI_TypeInfo *typinf)
	Register an object output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterLob (OCI_Statement *stmt, const otext *name, unsigned int type)
	Register a lob output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterFile (OCI_Statement *stmt, const otext *name, unsigned int type)
	Register a file output bind placeholder. More...
OCI_EXPORT boolean OCI_API	OCI_RegisterRef (OCI_Statement *stmt, const otext *name, OCI_TypeInfo *typinf)
	Register a Ref output bind placeholder. More...

Function Documentation

OCI_GetNextResultset()

OCI_EXPORT OCI_Resultset* OCI_API OCI_GetNextResultset

(

OCI_Statement *

stmt

)

#include <ocilib.h>

Retrieve the next available resultset.

Parameters

stmt	- Statement handle

Note

it is only valid for the following statements:

• Statements executing SQL UPDATE/DELETE using a RETURNING INTO clause
• Statements implicitly returned from PL/SQL procedure or blocks (new feature in Oracle 12cR1) using DBMS_SQL.RETURN_RESULT()

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.

Starting withOracle 12cR1, PL/SQ procedure and blocks ca return multiple implicit resultsets Refer to Oracle documentation for more information.

Returns

A resultset handle on success otherwise NULL

Referenced by ocilib::Statement::GetNextResultset().

OCI_RegisterNumber()

OCI_EXPORT boolean OCI_API OCI_RegisterNumber	(	OCI_Statement *	stmt,
		const otext *	name
	)

#include <ocilib.h>

Register a register output bind placeholder.

Parameters

stmt	- Statement handle
name	- Output bind name

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Statement::Bind().

OCI_RegisterShort()

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

#include <ocilib.h>

Register a short output bind placeholder.

Parameters

stmt	- Statement handle
name	- Output bind name

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Statement::Bind().

OCI_RegisterUnsignedShort()

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

#include <ocilib.h>

Register an unsigned short output bind placeholder.

Parameters

stmt	- Statement handle
name	- Output bind name

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Statement::Bind().

OCI_RegisterInt()

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

#include <ocilib.h>

Register an integer output bind placeholder.

Parameters

stmt	- Statement handle
name	- Output bind name

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Statement::Bind().

OCI_RegisterUnsignedInt()

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

#include <ocilib.h>

Register an unsigned integer output bind placeholder.

Parameters

stmt	- Statement handle
name	- Output bind name

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Statement::Bind().

OCI_RegisterBigInt()

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

#include <ocilib.h>

Register a big integer output bind placeholder.

Parameters

stmt	- Statement handle
name	- Output bind name

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Statement::Bind().

OCI_RegisterUnsignedBigInt()

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

#include <ocilib.h>

Register an unsigned big integer output bind placeholder.

Parameters

stmt	- Statement handle
name	- Output bind name

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Statement::Bind().

OCI_RegisterString()

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

#include <ocilib.h>

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

Referenced by ocilib::Statement::Bind().

OCI_RegisterRaw()

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

#include <ocilib.h>

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

Referenced by ocilib::Statement::Bind().

OCI_RegisterDouble()

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

#include <ocilib.h>

Register a double output bind placeholder.

Parameters

stmt	- Statement handle
name	- Output bind name

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Statement::Bind().

OCI_RegisterFloat()

OCI_EXPORT boolean OCI_API OCI_RegisterFloat	(	OCI_Statement *	stmt,
		const otext *	name
	)

#include <ocilib.h>

Register a float output bind placeholder.

Parameters

stmt	- Statement handle
name	- Output bind name

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Statement::Bind().

OCI_RegisterDate()

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

#include <ocilib.h>

Register a date output bind placeholder.

Parameters

stmt	- Statement handle
name	- Output bind name

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Statement::Bind().

OCI_RegisterTimestamp()

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

#include <ocilib.h>

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

Referenced by ocilib::Statement::Bind().

OCI_RegisterInterval()

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

#include <ocilib.h>

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

Referenced by ocilib::Statement::Bind().

OCI_RegisterObject()

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

#include <ocilib.h>

Register an object output bind placeholder.

Parameters

stmt	- Statement handle
name	- Output bind name
typinf	- Type info handle

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Statement::Bind().

OCI_RegisterLob()

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

#include <ocilib.h>

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

Referenced by ocilib::Statement::Bind().

OCI_RegisterFile()

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

#include <ocilib.h>

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

Referenced by ocilib::Statement::Bind().

OCI_RegisterRef()

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

#include <ocilib.h>

Register a Ref output bind placeholder.

Parameters

stmt	- Statement handle
name	- Output bind name
typinf	- Type info handle

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Statement::Bind().