Executing statements

Detailed Description

OcilibCApiTransactions

Executing SQL statements or PL/SQL blocks with OCILIB follows a simple workflow:

1. Call OCI_StatementCreate() to allocate a statement handle.
2. Prepare the SQL with OCI_Prepare().
3. Execute it with OCI_Execute().

Steps 2 and 3 can be combined into a single call using OCI_ExecuteStmt(), which prepares and executes in one operation.

To determine the number of rows affected by a DML statement, call OCI_GetAffectedRows().

When finished, release the statement and its resources with OCI_StatementFree().

Note

A statement can be prepared once and executed multiple times (see the Binding variables section for parameterized queries).

An OCI_Statement handle can be reused to prepare and execute different SQL and PL/SQL statements as many times as needed. For sequential SQL processing, a single statement handle is sufficient.

OCILIB supports nested levels of SQL statement processing. An application can loop through the result set of statement A while executing statement B and fetching from statement C at every iteration.

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 with it (result sets, bind variables, etc.)
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 in a single call.
OCI_SYM_PUBLIC boolean OCI_API	OCI_Parse (OCI_Statement *stmt, const otext *sql)
	Parse a SQL statement or PL/SQL block without executing it.
OCI_SYM_PUBLIC boolean OCI_API	OCI_Describe (OCI_Statement *stmt, const otext *sql)
	Describe the select list of a SQL SELECT statement without executing it.
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)
	Return the SQL_ID of the statement as assigned by the server.
OCI_SYM_PUBLIC unsigned int OCI_API	OCI_GetSqlErrorPos (OCI_Statement *stmt)
	Return the character position in the SQL statement where a parsing error occurred.
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 command code for the statement.
OCI_SYM_PUBLIC const otext *OCI_API	OCI_GetSQLVerb (OCI_Statement *stmt)
	Return the verb of the SQL command held by the statement handle.
OCI_SYM_PUBLIC boolean OCI_API	OCI_GetParseBindNames (OCI_Statement *stmt, unsigned int *count, const otext ***names)
	Return the list of parsed bind names after an OCI_Prepare() or OCI_Parse() call.

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, or NULL on failure.

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 with it (result sets, bind variables, etc.)

Parameters

stmt	- Statement 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 statement 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

Warning

If a SQL warning occurs:

• The function still returns TRUE.
• The warning triggers the global error handler with an OCI_Error whose OCI_ErrorGetType() returns OCI_ERR_WARNING.
• If OCILIB was initialized with OCI_ENV_CONTEXT, OCI_GetLastError() returns the corresponding OCI_Error object.

Returns

TRUE on success, otherwise FALSE.

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 in a single call.

Parameters

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

Warning

If a SQL warning occurs:

• The function still returns TRUE.
• The warning triggers the global error handler with an OCI_Error whose OCI_ErrorGetType() returns OCI_ERR_WARNING.
• If OCILIB was initialized with OCI_ENV_CONTEXT, OCI_GetLastError() returns the corresponding OCI_Error object.

Returns

TRUE on success, otherwise FALSE.

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 without executing it.

Parameters

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

Note

This function sends the SQL or PL/SQL command to the server for parsing only. The command is not executed. It is useful to validate whether a command is syntactically correct.

Internally, this function prepares the statement (via OCI_Prepare()) and then requests the Oracle server to parse it. Calling OCI_Execute() after OCI_Parse() will cause the server to re-parse the command.

Warning

Avoid using OCI_Parse() unless you are only interested in the parsing result, as the statement will be parsed again upon execution, resulting in unnecessary server round-trips and reduced 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 without executing it.

Parameters

stmt	- Statement handle
sql	- SQL SELECT statement

Note

This function sends the SELECT statement to the server to retrieve the description of the select list only. The command is not executed. Call OCI_GetResultset() after OCI_Describe() to access the column information of the SELECT list.

Internally, this function prepares the statement (via OCI_Prepare()) and then requests the Oracle server to describe the output SELECT list. Calling OCI_Execute() after OCI_Describe() will cause the server to parse and describe the SQL statement again.

Warning

Avoid using OCI_Describe() unless you are only interested in the result set metadata, as the statement will be parsed again upon execution, resulting in unnecessary server round-trips and reduced 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

Returns

The SQL text string, or NULL if no statement has been prepared.

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

OCI_GetSqlIdentifier()

OCI_SYM_PUBLIC const otext *OCI_API OCI_GetSqlIdentifier

(

OCI_Statement *

stmt

)

#include <api.h>

Return the SQL_ID of the statement as assigned by the server.

Parameters

stmt	- Statement handle

Note

The statement must be executed before calling this function.

Warning

Requires Oracle 12cR2 or later (both client and server). For earlier versions, the function returns NULL.

Returns

The SQL_ID string, or NULL if not available.

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

OCI_GetSqlErrorPos()

OCI_SYM_PUBLIC unsigned int OCI_API OCI_GetSqlErrorPos

(

OCI_Statement *

stmt

)

#include <api.h>

Return the character position in the SQL statement where a parsing error occurred.

Parameters

stmt	- Statement handle

Note

Positions are 1-based.

Returns

The 1-based character position of the error, or 0 if no parsing error occurred.

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

Note

The returned value depends on the statement type:

• UPDATE : Number of rows updated.
• INSERT : Number of rows inserted.
• DELETE : Number of rows deleted.

For SELECT statements, use OCI_GetRowCount() instead.

Returns

The number of affected rows.

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 command code for the statement.

Parameters

stmt	- Statement handle

Warning

This function must be called after the statement has been executed, as the SQL command code is computed by the server.

Returns

The SQL command code of the statement, or OCI_UNKNOWN if not available.

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

This function must be called after the statement has been executed, as the SQL verb is computed by the server.

Note

The list of SQL verbs is available in Oracle documentation.

Returns

The SQL command verb string (e.g., "SELECT", "INSERT", "UPDATE"), or NULL if not available.

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

OCI_GetParseBindNames()

OCI_SYM_PUBLIC boolean OCI_API OCI_GetParseBindNames	(	OCI_Statement *	stmt,
		unsigned int *	count,
		const otext ***	names
	)

#include <api.h>

Return the list of parsed bind names after an OCI_Prepare() or OCI_Parse() call.

Parameters

stmt	- Statement handle
count	- Pointer to receive the bind count
names	- Pointer to receive the array of bind names

Warning

Returned names are uppercase and do not contain the leading ':' character.

OCI_GetParseBindNames() returns information computed by the OCI client library, not by OCILIB. The result is independent of any bind operation performed by the application.

Returns

TRUE on success, otherwise FALSE (including if count or names are NULL).

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