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