OraExec Function

Action

Executes a SQL statement or PL/SQL block associated with a cursor. 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 parse the SQL statement using the OraParse 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 OraBind function for each place holder first.

Include file

Ora.bdh

Syntax

OraExec( in  cCursor     : cursor,
         in  nIterations : number optional,
         in  nRows       : number optional,
         in  nCancel     : number optional,
         in  nExact      : number optional,
         out nFetched    : number optional ): boolean;

Return value

  • true if successful

  • false otherwise. In this case, you can use the OraOciError function to retrieve the Oracle OCI error code

Parameter Description
cCursor Cursor associated with a database connection.
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

Specifies whether to cancel the cursor afterwards (optional).

  • If this parameter is set to 1, the cursor is canceled after the fetch operation. See OraCancel function for detailed information

  • If this parameter is set to 0, the cursor is not canceled (default)

nExact

Specifies whether to report an error if the actual number of fetched rows is greater than the specified number of rows to fetch (nRows parameter)

  • If this parameter is set to 1, Silk Performer terminates the simulation if the actual number of fetched rows is greater than the specified number of rows to fetch

  • If this parameter is set to 0, no error message is displayed (default)

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

Example

var
  hConnection : number;
  cCursor : cursor;

dcltrans
  transaction TMain
  var
    sName : string;
    nFetched, nAge, i : number;
  begin
    OraLogon(hConnection, "user", "password", "orclnet2");
    OraOpen(cCursor, hConnection);
    // insert two persons: Jimmy and Ronald
    OraParse(cCursor, sqlInsert);
    OraBind(cCursor, ":name", SQLT_CHR, 32, 2);
    OraBind(cCursor, ":age", SQLT_INT, 0, 2);
    OraSetString(cCursor, ":name", "Jimmy", 1);
    OraSetInt(cCursor, ":age", 55, 1);
    OraSetString(cCursor, ":name", "Ronald", 2);
    OraSetInt(cCursor, ":age", 18, 2);
    OraExec(cCursor, 2);
    // select all persons
    OraParse(cCursor, sqlSelect);
    OraDefine(cCursor, 1, SQLT_CHR, 32);
    OraDefine(cCursor, 2, SQLT_INT);
    OraExec(cCursor, 1, 50, 0, 0, nFetched);

    for i := 1 to nFetched do
      sName := OraGetString(cCursor, "1", i);
      nAge := OraGetInt(cCursor, "2", i);
      write(sName, 32); write(nAge, 5); writeln;
    end;

    OraClose(cCursor);
    OraLogoff(hConnection);
  end TMain;

dclsql
  sqlInsert:
  INSERT INTO persons (name, age) VALUES (:name, :age);
  sqlSelect:
    SELECT * FROM persons;

Output

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

Sample scripts

OraArrayFetch.bdf, OraSample.bdf, OraLoadPers.bdf

See also

Wrapped Oracle function: oexec, oexn, oexfet