Ora8StmtFetch Function

Action

Performs a specified number of fetch operations in order to retrieve data from a SQL query. This function is used to fetch both single and multiple rows.

Afterwards, the following functions can be used to retrieve the fetched data:

  • Ora8GetString
  • Ora8GetInt
  • Ora8GetFloat
  • Ora8GetValue

Include file

Ora8.bdh

Syntax

Ora8StmtFetch( in  hStmt         : number,
               in  nIterations   : number optional,
               in  nBatchSize    : number optional,
               in  nOrientation  : number optional,
               in  nScrollOffset : number optional,
               in  nMode         : number optional,
               out nFetched      : number optional ) : boolean;

Return value

  • true if successful

  • false either if an error occurred during the fetch operation or if the number of rows actually fetched is not equal to the specified number of rows to fetch.

Parameter Description
hStmt Statement handle.
nIterations

Number of iterations to perform (optional). Predefined options are:

ORA_FETCH_ALL. Repeat the fetch operation until all rows have been fetched.

The default option is to perform a single iteration.

nBatchSize Number of rows to fetch per iteration (optional). The default option is to fetch a single row per iteration
nOrientation Orientation (optional).
nScrollOffset Scroll offset (optional).
nMode Mode. (optional)
nFetched Variable receiving the actual number of rows fetched (optional).

Example

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

dcltrans
  transaction TMain
  const
    MAX_ROWS := 1000; // maximum number of rows
  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, sqlSelect, OCI_NTV_SYNTAX);

    Ora8Define(ghStmt0, 1, SQLT_CHR, 32);
    Ora8Define(ghStmt0, 2, SQLT_INT);

    // first possibility to fetch all rows
    // in this case, nFetched must be smaller than MAX_ROWS
    Ora8StmtExecute(ghSvcCtx0, ghStmt0);
    Ora8StmtFetch(ghStmt0, 1, MAX_ROWS, 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;

    // second possibility to fetch all data
    Ora8StmtExecute(ghSvcCtx0, ghStmt0);

    while Ora8StmtFetch(ghStmt0, 1, 1) do
      sName := Ora8GetString(ghStmt0, "1");
      nAge  := Ora8GetInt(ghStmt0, "2");
      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
  sqlSelect:
    SELECT * FROM persons;

Output

Howard 33Michael 44Bobby 61Sara 38

See also

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