OCILIB (C and C++ Driver for Oracle)  4.7.5
Open source and cross platform Oracle Driver delivering efficient access to Oracle databases.
Loading...
Searching...
No Matches
Oracle Returning feature

Detailed Description

OcilibCApiCollections

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"
/* requires script demo/products.sql */
void err_handler(OCI_Error *err)
{
printf("%s\n", OCI_ErrorGetString(err));
}
int main(void)
{
if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT))
{
return EXIT_FAILURE;
}
cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
OCI_Prepare(st, "update products set name = name || ' updated' returning code into :code");
OCI_RegisterInt(st, ":code");
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);
printf("%i elements updated in DB\n", OCI_GetInt(rs, 1));
return EXIT_SUCCESS;
}
OCI_SYM_PUBLIC boolean OCI_API OCI_ConnectionFree(OCI_Connection *con)
Close a physical connection to an Oracle database server.
OCI_SYM_PUBLIC OCI_Connection *OCI_API OCI_ConnectionCreate(const otext *db, const otext *user, const otext *pwd, unsigned int mode)
Create a physical connection to an Oracle database server.
struct OCI_Connection OCI_Connection
Oracle physical connection.
Definition: types.h:124
struct OCI_Statement OCI_Statement
Oracle SQL or PL/SQL statement.
Definition: types.h:136
struct OCI_Error OCI_Error
Encapsulates an Oracle or OCILIB exception.
Definition: types.h:390
struct OCI_Resultset OCI_Resultset
Collection of output columns from a select statement.
Definition: types.h:163
OCI_SYM_PUBLIC const otext *OCI_API OCI_ErrorGetString(OCI_Error *err)
Retrieve error message from error handle.
OCI_SYM_PUBLIC boolean OCI_API OCI_RegisterInt(OCI_Statement *stmt, const otext *name)
Register an integer output bind placeholder.
OCI_SYM_PUBLIC boolean OCI_API OCI_FetchNext(OCI_Resultset *rs)
Fetch the next row of the resultset.
OCI_SYM_PUBLIC OCI_Resultset *OCI_API OCI_GetResultset(OCI_Statement *stmt)
Retrieve the resultset handle from an executed statement.
OCI_SYM_PUBLIC int OCI_API OCI_GetInt(OCI_Resultset *rs, unsigned int index)
Return the current integer value of the column at the given index in the resultset.
OCI_SYM_PUBLIC boolean OCI_API OCI_Cleanup(void)
Clean up all resources allocated by the library.
OCI_SYM_PUBLIC boolean OCI_API OCI_Initialize(POCI_ERROR err_handler, const otext *lib_path, unsigned int mode)
Initialize the library.
OCI_SYM_PUBLIC unsigned int OCI_API OCI_GetAffectedRows(OCI_Statement *stmt)
Return the number of rows affected by the SQL statement.
OCI_SYM_PUBLIC OCI_Statement *OCI_API OCI_StatementCreate(OCI_Connection *con)
Create a statement object and return its handle.
OCI_SYM_PUBLIC boolean OCI_API OCI_ExecuteStmt(OCI_Statement *stmt, const otext *sql)
Prepare and Execute a SQL statement or PL/SQL block.
OCI_SYM_PUBLIC boolean OCI_API OCI_StatementFree(OCI_Statement *stmt)
Free a statement and all resources associated to it (resultsets ...)
OCI_SYM_PUBLIC boolean OCI_API OCI_Prepare(OCI_Statement *stmt, const otext *sql)
Prepare a SQL statement or PL/SQL block.
OCI_SYM_PUBLIC boolean OCI_API OCI_Execute(OCI_Statement *stmt)
Execute a prepared SQL statement or PL/SQL block.
OCI_SYM_PUBLIC boolean OCI_API OCI_Commit(OCI_Connection *con)
Commit current pending changes.

Functions

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

Function Documentation

◆ OCI_GetNextResultset()

OCI_SYM_PUBLIC OCI_Resultset *OCI_API OCI_GetNextResultset ( OCI_Statement stmt)

#include <api.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_SYM_PUBLIC boolean OCI_API OCI_RegisterNumber ( OCI_Statement stmt,
const otext *  name 
)

#include <api.h>

Register a register output bind placeholder.

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

◆ OCI_RegisterShort()

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

#include <api.h>

Register a short output bind placeholder.

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

◆ OCI_RegisterUnsignedShort()

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

#include <api.h>

Register an unsigned short output bind placeholder.

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

◆ OCI_RegisterInt()

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

#include <api.h>

Register an integer output bind placeholder.

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

◆ OCI_RegisterUnsignedInt()

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

#include <api.h>

Register an unsigned integer output bind placeholder.

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

◆ OCI_RegisterBigInt()

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

#include <api.h>

Register a big integer output bind placeholder.

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

◆ OCI_RegisterUnsignedBigInt()

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

#include <api.h>

Register an unsigned big integer output bind placeholder.

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

◆ OCI_RegisterString()

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

#include <api.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

◆ OCI_RegisterRaw()

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

#include <api.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

◆ OCI_RegisterDouble()

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

#include <api.h>

Register a double output bind placeholder.

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

◆ OCI_RegisterFloat()

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

#include <api.h>

Register a float output bind placeholder.

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

◆ OCI_RegisterDate()

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

#include <api.h>

Register a date output bind placeholder.

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

◆ OCI_RegisterTimestamp()

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

#include <api.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

◆ OCI_RegisterInterval()

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

#include <api.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

◆ OCI_RegisterObject()

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

#include <api.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

◆ OCI_RegisterLob()

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

#include <api.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

◆ OCI_RegisterFile()

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

#include <api.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

◆ OCI_RegisterRef()

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

#include <api.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