Ora8Define Function

Action

Defines an output variable for a specified select-list item in a SQL query. In a BDL script, it is necessary to call this function for each select-list item of a SQL query. After the Ora8StmtExecute function (and Ora8StmtFetch function) has been called, the following functions can be used to retrieve the value of the program variable that is defined for the specified select-list item:

Ora8GetString
Ora8GetInt
Ora8GetFloat
Ora8GetValue

Include file

Ora8.bdh

Syntax

Ora8Define( in  hStmt          : number,
            in  nPosition      : number,
            in  nDatatype      : number optional,
            in  nSize          : number optional,
            in  nMaxColSize    : number optional,
            in  nDefineType    : number optional,
            in  nPieces        : number optional,
            out hDefineHandle  : number optional ): boolean;

Return value

true if successful
false otherwise; In this case, you can use the Ora8ApiError function to retrieve the Oracle OCI error code.

Parameter	Description
hStmt	Statement handle.
nPosition	Index of the select-list item in the SQL query. Position indices start at 1 for the first (leftmost) select-list item.
nDatatype	Oracle external data type of the select-list item (optional). See Oracle 8 data types for a list of Oracle data types. The default value is SQLT_STR (null-terminated string).
nSize	Length of the select-list item in bytes (optional). This parameter should be omitted or set to 0 for data types of fixed length (SQLT_NUM, SQLT_INT, SQLT_FLT, SQLT_VNU, SQLT_RID, SQLT_DAT, SQLT_UIN, SQLT_CUR).
nMaxColSize	Maximum possible size of the select-list item in bytes (optional). This parameter has to be specified whenever the select-list item is retrieved in multiple pieces.
nDefineType	Special select-list option (optional). Possible options are: ORA_PIECEWISE ORA_FETCHLONG
nPieces	Number of pieces (optional). Whenever nDefineType is set to ORA_FETCHLONG, this parameter can be used to specify the number of pieces.
hDefineHandle	Variable receiving the define handle (optional).

Example

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

dcltrans
  transaction TMain
  var
    sName             : string;
    nFetched, nAge, i : number;
  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);
    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
  sqlSelect:
    SELECT * FROM persons;

Output

Howard 33Michael 44Bobby 61Sara 38