Executing statements

Detailed Description

OcilibCApiTransactions

Executing SQL statements or PL/SQL blocks is really simple with OCILIB.

First, call OCI_StatementCreate() to allocate a statement handle. Then :

• Prepare the SQL with OCI_Prepare()
• Parse and execute it with OCI_Execute()

These two steps can be done together by calling OCI_ExecuteStmt() that prepares and executes in one go.

To find out if the statement has affected any rows, call OCI_GetAffectedRows()

Finally, release the statement and its resources with OCI_StatementFree()

Note

A statement can be prepared once and executed as many times as needed (see Binding variables section)

An OCI_Statement can be used to prepare and/or execute different SQL and PL/SQL statements as many times as needed. For example, if the SQL processing of an application is sequential, only one statement handle is required

OCILIB supports nested levels of SQL statement processing. An application can loop through the resultset of the statement handle A, executing statement B and fetching statement C at every loop, and so on ...

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;
 
    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);
 
    /* prepare and execute in 2 steps */
 
    OCI_Prepare(st, "delete from products where code = 1");
    OCI_Execute(st);
    printf("%d row deleted\n", OCI_GetAffectedRows(st));
 
    /* prepare/execute in 1 step */
 
    OCI_ExecuteStmt(st, "delete from products where code = 2");
    printf("%d row deleted\n", OCI_GetAffectedRows(st));
 
    OCI_Commit(cn);
    OCI_StatementFree(st);
    OCI_ConnectionFree(cn);
    OCI_Cleanup();
 
    return EXIT_SUCCESS;
}

Functions
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_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_ExecuteStmt (OCI_Statement *stmt, const otext *sql)
	Prepare and Execute a SQL statement or PL/SQL block.
OCI_SYM_PUBLIC boolean OCI_API	OCI_Parse (OCI_Statement *stmt, const otext *sql)
	Parse a SQL statement or PL/SQL block.
OCI_SYM_PUBLIC boolean OCI_API	OCI_Describe (OCI_Statement *stmt, const otext *sql)
	Describe the select list of a SQL select statement.
OCI_SYM_PUBLIC const otext *OCI_API	OCI_GetSql (OCI_Statement *stmt)
	Return the last SQL or PL/SQL statement prepared or executed by the statement.
OCI_SYM_PUBLIC const otext *OCI_API	OCI_GetSqlIdentifier (OCI_Statement *stmt)
	Returns the statement SQL_ID from the server.
OCI_SYM_PUBLIC unsigned int OCI_API	OCI_GetSqlErrorPos (OCI_Statement *stmt)
	Return the error position (in terms of characters) in the SQL statement where the error occurred in case of SQL parsing error.
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 unsigned int OCI_API	OCI_GetSQLCommand (OCI_Statement *stmt)
	Return the Oracle SQL code the command held by the statement handle.
OCI_SYM_PUBLIC const otext *OCI_API	OCI_GetSQLVerb (OCI_Statement *stmt)
	Return the verb of the SQL command held by the statement handle.

Function Documentation

OCI_StatementCreate()

OCI_SYM_PUBLIC OCI_Statement *OCI_API OCI_StatementCreate

(

OCI_Connection *

con

)

#include <api.h>

Create a statement object and return its handle.

Parameters

con	- Connection handle

Returns

A statement handle on success otherwise NULL

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

OCI_StatementFree()

OCI_SYM_PUBLIC boolean OCI_API OCI_StatementFree

(

OCI_Statement *

stmt

)

#include <api.h>

Free a statement and all resources associated to it (resultsets ...)

Parameters

stmt	- Connection handle

Returns

TRUE on success otherwise FALSE

OCI_Prepare()

OCI_SYM_PUBLIC boolean OCI_API OCI_Prepare	(	OCI_Statement *	stmt,
		const otext *	sql
	)

#include <api.h>

Prepare a SQL statement or PL/SQL block.

Parameters

stmt	- Statement handle
sql	- SQL order or PL/SQL block

Note

Do not call this function for fetched statements (REF cursors)

Returns

TRUE on success otherwise FALSE

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

OCI_Execute()

OCI_SYM_PUBLIC boolean OCI_API OCI_Execute

(

OCI_Statement *

stmt

)

#include <api.h>

Execute a prepared SQL statement or PL/SQL block.

Parameters

stmt	- Statement handle

Returns

TRUE on success otherwise FALSE

Warning

If a SQL warning occurs:

• the function returns TRUE
• the SQL warning triggers the global error handler with an OCI_Error having its OCI_ErrorGetType() attribute set to OCI_ERR_WARNING
• If OCILIB is initialized with the OCI_ENV_CONTEXT mode, OCI_GetLastError() will return the OCI_Error object corresponding to the warning

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

OCI_ExecuteStmt()

OCI_SYM_PUBLIC boolean OCI_API OCI_ExecuteStmt	(	OCI_Statement *	stmt,
		const otext *	sql
	)

#include <api.h>

Prepare and Execute a SQL statement or PL/SQL block.

Parameters

stmt	- Statement handle
sql	- SQL order - PL/SQL block

Returns

TRUE on success otherwise FALSE

Warning

If a SQL warning occurs:

• the function returns TRUE
• the SQL warning triggers the global error handler with an OCI_Error having its OCI_ErrorGetType() attribute set to OCI_ERR_WARNING
• If OCILIB is initialized with the OCI_ENV_CONTEXT mode, OCI_GetLastError() will return the OCI_Error object corresponding to the warning

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

OCI_Parse()

OCI_SYM_PUBLIC boolean OCI_API OCI_Parse	(	OCI_Statement *	stmt,
		const otext *	sql
	)

#include <api.h>

Parse a SQL statement or PL/SQL block.

Parameters

stmt	- Statement handle
sql	- SQL order - PL/SQL block

Note

This call sends the SQL or PL/SQL command to the server for parsing only. The command is not executed. This call is only useful to check is a command is valid or not.

This call prepares the statement (internal call to OCI_Prepare()) and ask the Oracle server to parse its SQL or PL/SQL command. OCI_Execute() can be call after OCI_Parse() in order to execute the statement, which means that the server will re-parse again the command.

Warning

Do not use OCI_Parse() unless you're only interested in the parsing result because the statement will be parsed again when executed and thus leading to unnecessary server round-trips and less performance

Returns

TRUE on success otherwise FALSE

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

OCI_Describe()

OCI_SYM_PUBLIC boolean OCI_API OCI_Describe	(	OCI_Statement *	stmt,
		const otext *	sql
	)

#include <api.h>

Describe the select list of a SQL select statement.

Parameters

stmt	- Statement handle
sql	- SELECT sql statement

Note

This call sends the SELECT SQL order to the server for retrieving the description of the select order only. The command is not executed. This call is only useful to retrieve information on the associated resultset Call OCI_GetResultet() after OCI_Describe() to access to SELECT list information

This call prepares the statement (internal call to OCI_Prepare()) and ask the Oracle server to describe the output SELECT list. OCI_Execute() can be called after OCI_Describe() in order to execute the statement, which means that the server will parse, and describe again the SQL order.

Warning

Do not use OCI_Describe() unless you're only interested in the resultset information because the statement will be parsed again when executed and thus leading to unnecessary server round-trips and less performance

Returns

TRUE on success otherwise FALSE

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

OCI_GetSql()

OCI_SYM_PUBLIC const otext *OCI_API OCI_GetSql

(

OCI_Statement *

stmt

)

#include <api.h>

Return the last SQL or PL/SQL statement prepared or executed by the statement.

Parameters

stmt	- Statement handle

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

OCI_GetSqlIdentifier()

OCI_SYM_PUBLIC const otext *OCI_API OCI_GetSqlIdentifier

(

OCI_Statement *

stmt

)

#include <api.h>

Returns the statement SQL_ID from the server.

Parameters

stmt	- Statement handle

Note

The statement must be executed first

Warning

Requires Oracle 12cR2 (both client and server side), otherwise it returns NULL

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

OCI_GetSqlErrorPos()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_GetSqlErrorPos

(

OCI_Statement *

stmt

)

#include <api.h>

Return the error position (in terms of characters) in the SQL statement where the error occurred in case of SQL parsing error.

Parameters

stmt	- Statement handle

Note

Positions start at 1.

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

OCI_GetAffectedRows()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_GetAffectedRows

(

OCI_Statement *

stmt

)

#include <api.h>

Return the number of rows affected by the SQL statement.

Parameters

stmt	- Statement handle

The returned value is :

• For UPDATEs : number of rows updated
• For INSERTs : number of rows inserted
• For DELETEs : number of rows deleted

Note

For SELECTs statements, use OCI_GetRowCount() instead

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

OCI_GetSQLCommand()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_GetSQLCommand

(

OCI_Statement *

stmt

)

#include <api.h>

Return the Oracle SQL code the command held by the statement handle.

Parameters

stmt	- Statement handle

Warning

OCI_GetSQLCommand() must be called after the statement has be executed because that's the server engine that computes the SQL command code

Returns

The SQL command code of the statement otherwise OCI_UNKOWN

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

OCI_GetSQLVerb()

OCI_SYM_PUBLIC const otext *OCI_API OCI_GetSQLVerb

(

OCI_Statement *

stmt

)

#include <api.h>

Return the verb of the SQL command held by the statement handle.

Parameters

stmt	- Statement handle

Warning

OCI_GetSQLVerb() must be called after the statement has been executed because that's the server engine that computes the SQL verb

Note

The SQL verb list is available in Oracle documentations and guides

Returns

The SQL command verb of the statement otherwise NULL

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