OraBind Function

Action

Binds a place holder in a SQL statement or PL/SQL block to a program variable. The variable is accessible through the OraSet and OraGet functions. The place holder 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 place holder:

OraSetBinary
OraSetFloat
OraSetInt
OraSetNull
OraSetPieces
OraSetString

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

Include file

Ora.bdh

Syntax

OraBind( in cCursor     : cursor,
         in sSqlVar     : string,
         in nDatatype   : number optional,
         in nSize       : number optional,
         in nArraySize  : number optional,
         in nBindOption : 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
sSqlVar	Name of the place holder in the SQL statement or PL/SQL block. This parameter must include the preceding colon identifying it as a place holder
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 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 OraExec 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). For string data types (SQLT_STR, SQLT_CHR, SQLT_LNG, SQLT_AFC, SQLT_AVC) omitting this parameter is possible when omitting the nArraySize parameter, too. When performing piecewise binding of the variable to the place holder, 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 place holder, this parameter specifies the maximum column size
nBindOption	Specifies how to bind the place holder (optional). The following are the possible options for binding a place holder: ORA_DEFAULT. This is the default option. A place holder is bound to a scalar or to an array program variable. ORA_PIECEWISE. The program variable is bound to the place holder in multiple pieces. In this case, nSize specifies the piece size, and nArraySize the maximum column size. You have to call the OraSetPieces 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.

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

SELECT name, ageFROM personsWHERE age >= :min_age AND age <= :max_age;

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

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

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

Example: scalar binding

var
  hConnection : number;
  cCursor : cursor;
dcltrans
  transaction TMain
  var
    nAge : number;
    sName : string;
  begin
    OraLogon(hConnection, "user", "password", "orclnet2");
    OraOpen(cCursor, hConnection);
    OraParse(cCursor, sqlSelect);
    OraBind(cCursor, ":1", SQLT_INT);
    OraSetInt(cCursor, ":1", 25);
    OraDefine(cCursor, 1, SQLT_CHR, 32);
    OraDefine(cCursor, 2, SQLT_INT);
    OraExec(cCursor);
    while OraFetch(cCursor) do
      sName := OraGetString(cCursor, "1");
      nAge:= OraGetInt(cCursor, "2");
      write(sName, 32); write(nAge, 5); writeln;
    end;
    OraClose(cCursor);
    OraLogoff(hConnection);
  end TMain;
dclsql
  sqlSelect:
    SELECT * FROM persons WHERE age > :1;

Output

Howard 33Michael 44Bobby 61Sara 38

Example: array binding

var
  hConnection : number;
  cCursor : cursor;
dcltrans
  transaction TMain
  begin
    OraLogon(hConnection, "user", "password", "orclnet2");
    OraOpen(cCursor, hConnection);
    OraParse(cCursor, sqlInsert);
    // bind array containing two elements
    OraBind(cCursor, ":name", SQLT_CHR, 32, 2);
    OraBind(cCursor, ":age", SQLT_INT, 0, 2);
    // set values
    OraSetString(cCursor, ":name", "Bob", 1);
    OraSetInt(cCursor, ":age", 25, 1);
    OraSetString(cCursor, ":name", "Marcy", 2);
    OraSetInt(cCursor, ":age", 33, 2);
    OraExec(cCursor, 2);
    OraClose(cCursor);
    OraLogoff(hConnection);
  end TMain;
dclsql
  sqlInsert:
    INSERT INTO persons (name, age) VALUES (:name, :age);

Example: piecewise binding

var
  hConnection : number;
  cCursor : cursor;
dcltrans
  transaction TMain
  begin
    OraLogon(hConnection, "user", "password", "orclnet2");
    OraOpen(cCursor, hConnection);
    OraParse(cCursor, sqlInsert);
    OraBind(cCursor, ":name", SQLT_STR, 32);
    OraBind(cCursor, ":data", SQLT_LBI, 4096, 64000, ORA_PIECEWISE);
    OraSetString(cCursor, ":name", "Sunset");
    OraSetPieces(cCursor, ":data", "TestData.dat");
    OraExec(cCursor);
    OraClose(cCursor);
    OraLogoff(hConnection);
  end TMain;
dclsql
  sqlInsert:
    INSERT INTO images (name, data) VALUES (:name, :data);

Example: PL/SQL table binding

var
  hConnection : number;
  cCursor : number;
dcltrans
  transaction TMain
  begin
    OraLogon(hConnection, "user", "password", "orclnet2");
    OraOpen(cCursor, hConnection);
    OraParse(cCursor, sqlProcedureWithPLSQLTable);
    OraBind(cCursor, ":num", SQLT_INT);
    OraBind(cCursor, ":description", SQLT_STR, 20, 3, ORA_PLSQLTABLE_INOUT);
    OraSetInt(cCursor, ":num", 5);
    OraSetString(cCursor, ":description", "Frammis_1", 1);
    OraSetString(cCursor, ":description", "Frammis_2", 2);
    OraSetString(cCursor, ":description", "Frammis_3", 3);
    OraExec(cCursor);
    OraClose(cCursor);
    OraLogoff(hConnection);
  end TMain;
dclsql
  sqlProcedureWithPLSQLTable:
  [BEGIN
    update_parts.add_parts(:num, :description);
  END;]

In this example, the parameter nBindOption specifies the option for binding the place holder. 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.

Sample scripts

OraArrayFetch.bdf, OraSample.bdf, OraLoadPers.bdf