Ora8StmtExecute Function

Action

Executes a SQL statement or PL/SQL block associated with a statement handle. Besides executing a statement, this function can also fetch data after the execution if the statement is a SQL query.

Before calling this function, you have to prepare the SQL statement using the Ora8StmtPrepare function. Array binding can be used to process multiple rows of data in a single execute operation. If the SQL statement contains place holders, it is necessary to call the Ora8Bind function for each place holder first.

Include file

Ora8.bdh

Syntax

Ora8StmtExecute( in  hSvcCtx     : number,
                 in  hStmt       : number,
                 in  nIterations : number optional,
                 in  nRows       : number optional,
                 in  nCancel     : number optional,
                 in  nExact      : number optional,
                 in  nMode       : number optional,
                 out nFetched    : number optional ) : boolean;

Return value

  • true if successful

  • false otherwise

Parameter Description
hSvcCtx Service context handle.
hStmt Statement handle.
nIterations Number of iterations (optional). This parameter specifies whether to perform multiple execute operations in order to process array parameters.
nRows Numbers of rows to fetch (optional). This parameter specifies whether to fetch data after a SQL query.
nCancel Reserved. (optional)
nExact Reserved. (optional)
nMode

Specifies the various modes of operation (optional).

The modes are:

  • OCI_DEFAULT (default)

  • OCI_BATCH_ERRORS

  • OCI_COMMIT_ON_SUCCESS

  • OCI_DESCRIBE_ONLY

  • OCI_EXACT_FETCH

  • OCI_PARSE_ONLY

  • OCI_STMT_SCROLLABLE_READ_ONLY

nFetched Variable receiving the actual number of rows fetched (optional)

Example

var
  ghEnv0        : number;
  ghError0      : number;
  ghStmt0       : number;
  ghSvcCtx0     : number;

dcltrans
  transaction TMain
  const
    PUBLIC_KEY := "\h59b20094...";
  var
    nFetched, i, nAge : number;
    sName             : string;
  begin
    Ora8Init(ghEnv0, OCI_DEFAULT);
    Ora8HandleAlloc(ghEnv0, ghError0, OCI_HTYPE_ERROR);
    Ora8Logon(ghEnv0, ghSvcCtx0, "user", "password", "orclnet2");

    Ora8HandleAlloc(ghEnv0, ghStmt0, OCI_HTYPE_STMT);
    Ora8StmtPrepare(ghStmt0, sqlInsert, OCI_NTV_SYNTAX);

    // insert two persons: Jimmy and Ronald
    Ora8Bind(ghStmt0, ":name", SQLT_CHR, 32, 2);
    Ora8Bind(ghStmt0, ":age", SQLT_INT, 0, 2);

    Ora8SetString(ghStmt0, ":name", "Jimmy", 1);
    Ora8SetInt(ghStmt0, ":age", 55, 1);
    Ora8SetString(ghStmt0, ":name", "Ronald", 2);
    Ora8SetInt(ghStmt0, ":age", 18, 2);

    Ora8StmtExecute(ghSvcCtx0, ghStmt0, 2);

    // select all persons
    Ora8StmtPrepare(ghStmt0, sqlSelect, OCI_NTV_SYNTAX);

    Ora8Define(ghStmt0, 1, SQLT_CHR, 32);
    Ora8Define(ghStmt0, 2, SQLT_INT);
    Ora8StmtExecute(ghSvcCtx0, ghStmt0, 1, 50, 0, 0, 0, nFetched);

    for i := 1 to nFetched do
      sName := Ora8GetString(ghStmt0, "1", i);
      nAge  := Ora8GetInt(ghStmt0, "2", i);
      write(sName, 32); write(nAge, 5); writeln;
    end;

    Ora8HandleFree(ghStmt0, OCI_HTYPE_STMT);
    Ora8Logoff(ghSvcCtx0);
    Ora8HandleFree(ghError0, OCI_HTYPE_ERROR);
    Ora8HandleFree(ghEnv0, OCI_HTYPE_ENV);
  end TMain;

dclsql
  sqlInsert:
    INSERT INTO persons (name, age) VALUES (:name, :age);

  sqlSelect:
    SELECT * FROM persons;

Output

Howard 33Michael 44Bobby 61Sara 38Jimmy 55Ronald 18

See also

Oracle's Programmer's Guide to Oracle Call Interface for Wrapped Oracle function: OCIStmtExecute