Functions

Executing statements


Detailed Description

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

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

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"

int main(void)
{
    OCI_Connection *cn;
    OCI_Statement  *st;
  
    if (!OCI_Initialize(NULL, 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 test_fetch where code > 10");
    OCI_Execute(st);

    /* prepare/execute in 1 step */

    OCI_ExecuteStmt(st, "delete from test_fetch where code > 1");

    printf("%d row deleted", OCI_GetAffectedRows(st));

    OCI_Commit(cn);

    OCI_Cleanup();
 
    return EXIT_SUCCESS;
}

Functions

OCI_EXPORT OCI_Statement *OCI_API OCI_StatementCreate (OCI_Connection *con)
 Create a statement object and return its handle.
OCI_EXPORT boolean OCI_API OCI_StatementFree (OCI_Statement *stmt)
 Free a statement and all resources associated to it (resultsets ...)
OCI_EXPORT boolean OCI_API OCI_Prepare (OCI_Statement *stmt, const mtext *sql)
 Prepare a SQL statement or PL/SQL block.
OCI_EXPORT boolean OCI_API OCI_Execute (OCI_Statement *stmt)
 Execute a prepared SQL statement or PL/SQL block.
OCI_EXPORT boolean OCI_API OCI_ExecuteStmt (OCI_Statement *stmt, const mtext *sql)
 Execute a SQL statement or PL/SQL block.
OCI_EXPORT boolean OCI_API OCI_Parse (OCI_Statement *stmt, const mtext *sql)
 Parse a SQL statement or PL/SQL block.
OCI_EXPORT const mtext *OCI_API OCI_GetSql (OCI_Statement *stmt)
 Return the last SQL or PL/SQL statement prepared or executed by the statement.
OCI_EXPORT 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_EXPORT unsigned int OCI_API OCI_GetAffectedRows (OCI_Statement *stmt)
 Return the number of rows affected by the SQL statement.
OCI_EXPORT unsigned int OCI_API OCI_GetSQLCommand (OCI_Statement *stmt)
 Return the Oracle SQL code the command held by the statement handle.
OCI_EXPORT const mtext *OCI_API OCI_GetSQLVerb (OCI_Statement *stmt)
 Return the verb of the SQL command held by the statement handle.

Function Documentation

OCI_EXPORT OCI_Statement* OCI_API OCI_StatementCreate ( OCI_Connection con )

Create a statement object and return its handle.

Parameters:
con- Connection handle
Returns:
A statement handle on success otherwise NULL

Definition at line 1743 of file statement.c.

Referenced by OCI_DatabaseShutdown(), OCI_DatabaseStartup(), OCI_Immediate(), OCI_ImmediateFmt(), OCI_QueueAlter(), OCI_QueueCreate(), OCI_QueueDrop(), OCI_QueueStart(), OCI_QueueStop(), OCI_QueueTableAlter(), OCI_QueueTableCreate(), OCI_QueueTableDrop(), OCI_QueueTableMigrate(), OCI_QueueTablePurge(), and OCI_ServerEnableOutput().

OCI_EXPORT boolean OCI_API OCI_StatementFree ( OCI_Statement stmt )

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

Parameters:
stmt- Connection handle
Returns:
TRUE on success otherwise FALSE

Definition at line 1771 of file statement.c.

Referenced by OCI_DatabaseShutdown(), OCI_DatabaseStartup(), OCI_Immediate(), OCI_ImmediateFmt(), OCI_QueueAlter(), OCI_QueueCreate(), OCI_QueueDrop(), OCI_QueueStart(), OCI_QueueStop(), OCI_QueueTableAlter(), OCI_QueueTableCreate(), OCI_QueueTableDrop(), OCI_QueueTableMigrate(), OCI_QueueTablePurge(), and OCI_ServerDisableOutput().

OCI_EXPORT boolean OCI_API OCI_Prepare ( OCI_Statement stmt,
const mtext *  sql 
)

Prepare a SQL statement or PL/SQL block.

Parameters:
stmt- Statement handle
sql- SQL order or PL/SQL block
Note:
With version 1.3.0 and above, do not call this function for fetched statements (REF cursors)
Returns:
TRUE on success otherwise FALSE

Definition at line 1832 of file statement.c.

Referenced by OCI_ExecuteStmt(), OCI_ExecuteStmtFmt(), OCI_ImmediateFmt(), OCI_Parse(), OCI_ParseFmt(), OCI_PrepareFmt(), OCI_QueueAlter(), OCI_QueueCreate(), OCI_QueueDrop(), OCI_QueueStart(), OCI_QueueStop(), OCI_QueueTableAlter(), OCI_QueueTableCreate(), OCI_QueueTableDrop(), OCI_QueueTableMigrate(), OCI_QueueTablePurge(), and OCI_ServerEnableOutput().

OCI_EXPORT boolean OCI_API OCI_Execute ( OCI_Statement stmt )

Execute a prepared SQL statement or PL/SQL block.

Parameters:
stmt- Statement handle
Returns:
TRUE on success otherwise FALSE

Definition at line 1895 of file statement.c.

Referenced by OCI_QueueAlter(), OCI_QueueCreate(), OCI_QueueDrop(), OCI_QueueStart(), OCI_QueueStop(), OCI_QueueTableAlter(), OCI_QueueTableCreate(), OCI_QueueTableDrop(), OCI_QueueTableMigrate(), OCI_QueueTablePurge(), OCI_ServerEnableOutput(), OCI_ServerGetOutput(), and OCI_SubscriptionAddStatement().

OCI_EXPORT boolean OCI_API OCI_ExecuteStmt ( OCI_Statement stmt,
const mtext *  sql 
)

Execute a SQL statement or PL/SQL block.

Parameters:
stmt- Statement handle
sql- SQL order - PL/SQL block
Returns:
TRUE on success otherwise FALSE

Definition at line 1907 of file statement.c.

References OCI_Prepare().

Referenced by OCI_DatabaseShutdown(), OCI_DatabaseStartup(), OCI_Immediate(), and OCI_ServerDisableOutput().

OCI_EXPORT boolean OCI_API OCI_Parse ( OCI_Statement stmt,
const mtext *  sql 
)

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 reparse 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 roundtrips and less performance
Returns:
TRUE on success otherwise FALSE

Definition at line 1920 of file statement.c.

References OCI_Prepare().

OCI_EXPORT const mtext* OCI_API OCI_GetSql ( OCI_Statement stmt )

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

Parameters:
stmt- Statement handle

Definition at line 3824 of file statement.c.

OCI_EXPORT 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.

Parameters:
stmt- Statement handle
Note:
Positions start at 1.

Definition at line 3840 of file statement.c.

OCI_EXPORT unsigned int OCI_API OCI_GetAffectedRows ( OCI_Statement stmt )

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

Definition at line 3856 of file statement.c.

OCI_EXPORT unsigned int OCI_API OCI_GetSQLCommand ( OCI_Statement stmt )

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

Definition at line 3944 of file statement.c.

Referenced by OCI_GetSQLVerb().

OCI_EXPORT const mtext* OCI_API OCI_GetSQLVerb ( OCI_Statement stmt )

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 be executed because that's the server engine that computes the SQL command code
Note:
The SQL command verb list is available in Oracle documentations and guides
Returns:
The SQL command verb of the statement otherwise NULL

Definition at line 3972 of file statement.c.

References OCI_GetSQLCommand().