Ora8Bind Function

Action

Binds a placeholder in a SQL statement or PL/SQL block to a program variable. The variable is accessible through the Ora8Set and Ora8Get functions. The placeholder is identified by the name or the number declared in the SQL statement or PL/SQL block. The functions listed below are used to assign values to the program variable that is bound to the specified placeholder:

Ora8SetString
Ora8SetBinary
Ora8SetInt
Ora8SetFloat
Ora8SetNull

This function serves to bind placeholders in SQL statements or PL/SQL blocks to both scalar and array variables. In addition, it can be used to bind placeholders to be used in piecewise operations. The function must be called after preparing the SQL statement or PL/SQL block and before calling the Ora8Set function.

Include file

Ora8.bdh

Syntax

Ora8Bind( in  hStmt        : number,
          in  sSqlVar      : string,
          in  nDatatype    : number optional,
          in  nSize        : number optional,
          in  nArraySize   : number optional,
          in  nBindOption  : number optional,
          out hBindHandle  : number optional): boolean;

Return value

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

Parameter	Description
hStmt	Statement handle.
sSqlVar	Name of the placeholder in the SQL statement or PL/SQL block. This parameter must include the preceding colon identifying it as a placeholder.
nDatatype	Oracle external data type of the program variable (optional). Oracle converts the program variable from external to internal format when the variable is sent to the database and vice versa when it is retrieved from the database. See Oracle 8 data types for a list of data types The default data type is SQLT_STR (null-terminated string)
nSize	Size of the program variable or the array element in bytes (optional). Since this function needs to be called only once for many different values on successive Ora8StmtExecute function calls, this parameter must contain the maximum size of the element to bind. This parameter should be omitted for data types of fixed length (SQLT_NUM, SQLT_INT, SQLT_FLT, SQLT_VNU, SQLT_RID, SQLT_DAT, SQLT_UIN, SQLT_CUR). If specified, the parameter is ignored and set to the fixed length of the data type. This parameter should not be omitted for the following data types: SQLT_CHR, SQLT_LNG, SQLT_BIN, SQLT_LBI, SQLT_AFC, SQLT_AVC, SQLT_VCS, SQLT_LVC, SQLT_VBI, SQLT_LVB When performing piecewise binding of the variable to the placeholder, this parameter specifies the piece size.
nArraySize	Maximum number of array elements to bind (optional). The default option is scalar binding. When performing piecewise binding of the variable to the placeholder, this parameter specifies the maximum column size.
nBindOption	Specifies how to bind the placeholder (optional). The following are the possible options for binding a placeholder: ORA_DEFAULT. This is the default option. A placeholder is bound to a scalar or to an array program variable. ORA_PIECEWISE. The program variable is bound to the placeholder in multiple pieces. In this case, nSize specifies the piece size, and nArraySize the maximum column size. You have to call the Ora8SetPieces function in order to set the values for the pieces. ORA_PLSQLTABLE_IN. The program variable is bound to a PL/SQL table that is used as an in parameter within a stored procedure. ORA_PLSQLTABLE_OUT. The program variable is bound to a PL/SQL table that is used as an out parameter in a stored procedure. ORA_PLSQLTABLE_INOUT: The program variable is bound to a PL/SQL table that is used as an inout parameter in a stored procedure. ORA_PLSQL_IN. The program variable is bound to a PL/SQL scalar parameter that is used as an in parameter within a stored procedure. ORA_PLSQL_OUT. The program variable is bound to a PL/SQL scalar parameter that is used as an out parameter in a stored procedure. ORA_PLSQL_INOUT. The program variable is bound to a PL/SQL scalar parameter that is used as an inout parameter in a stored procedure.
hBindHandle	Variable receiving the bind handle (optional).

A placeholder in a SQL statement consists of a colon (:) followed by a SQL identifier. The placeholder is not a test script variable. For example, the following SQL statement contains two placeholders, :min_age and :max_age.

SELECT name, age
FROM persons
WHERE age >= :min_age AND age <= :max_age;

It is also possible to define placeholders that consist of a colon followed by a literal integer between 1 and 255.

The following SQL statement contains two placeholders, :1 and :2.

SELECT name, age
FROM persons
WHERE age >= :1 AND age <= :2;

Example: scalar binding

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

dcltrans
  transaction TMain
  var
    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);

    Ora8Bind(ghStmt0, ":1", SQLT_INT);
    Ora8SetInt(ghStmt0, ":1", 25);
    Ora8Define(ghStmt0, 1, SQLT_CHR, 32);
    Ora8Define(ghStmt0, 2, SQLT_INT);
    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 WHERE age > :1;

Output

Howard 33Michael 44Bobby 61Sara 38

Example: array binding

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

dcltrans
  transaction TMain
  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);

    // bind array containing two elements
    Ora8Bind(ghStmt0, ":name", SQLT_CHR, 32, 2);
    Ora8Bind(ghStmt0, ":age", SQLT_INT, 0, 2);
    // set values
    Ora8SetString(ghStmt0, ":name", "Bob", 1);
    Ora8SetInt(ghStmt0, ":age", 25, 1);
    Ora8SetString(ghStmt0, ":name", "Marcy", 2);
    Ora8SetInt(ghStmt0, ":age", 33, 2);
    Ora8StmtExecute(ghSvcCtx0, ghStmt0, 2);

    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);

Example: PL/SQL table binding

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

dcltrans
  transaction TMain
  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, sqlProcedureWithPLSQLTable, OCI_NTV_SYNTAX);

    Ora8Bind(ghStmt0, ":num", SQLT_INT);
    Ora8Bind(ghStmt0, ":description", SQLT_STR, 20, 3, ORA_PLSQLTABLE_INOUT);
    Ora8SetInt(ghStmt0, ":num", 5);
    Ora8SetString(ghStmt0, ":description", "Frammis_1", 1);
    Ora8SetString(ghStmt0, ":description", "Frammis_2", 2);
    Ora8SetString(ghStmt0, ":description", "Frammis_3", 3);
    Ora8StmtExecute(ghSvcCtx0, ghStmt0);

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

dclsql
  sqlProcedureWithPLSQLTable:
  [BEGIN
    update_parts.add_parts(:num, :description);
  END;]

In this example, the parameter nBindOption specifies the option for binding the placeholder. ORA_PLSQLTABLE_INOUT binds a PL/SQL table used as a parameter in a stored procedure with inout direction. The maximum length of a bind element is set to 20, the maximum number of array elements that can be sent/retrieved is set to 3.