A script-enabled browser is required for this page to function properly.

EXEC_SQL.Bind_Variable

Description

Binds a given value to a named variable in a SQL statement.

Syntax


PROCEDURE EXEC_SQL.Bind_Variable
    ([Connid     IN CONNTYPE],
      Curs_Id    IN CURSTYPE,
      Name       IN VARCHAR2,
      Value      IN );

where  can be one of the following: 

      NUMBER
      DATE
      VARCHAR2

PROCEDURE EXEC_SQL.Bind_Variable
    ([Connid          IN CONNTYPE],
      Curs_Id         IN CURSTYPE,
      Name            IN VARCHAR2,
      Value           IN VARCHAR2,
      Out_Value_Size  IN PLS_INTEGER);

Parameters

Parameter Description
Connid Is the handle to the connection you want to use. If you do not specify a connection, EXEC_SQL.Default_Connection retrieves the primary Oracle Developer connection handle from the cache.
Curs_Id Is the cursor handle in which to bind the variable.
Name Is the name of the variable in the SQL statement.
Value For IN and IN/OUT variables, the value is the data you want to bind to the named variable. For OUT variables, the data is actually ignored but you must still use Bind_Variable to indicate the type of PL/SQL variable to be retrieved later by Variable_Value.
Out_Value_Size The maximum OUT value size in bytes expected for the VARCHAR2 OUT or IN/OUT variables. If no size is specified, the current length of the Value parameter is used.

Usage Notes

Use placeholders in SQL statements to mark where input data is to be supplied during runtime. You must also use placeholders for output values if the statement is a PL/SQL block or a call to a stored procedure with output parameters.

For each input placeholder, you must use EXEC_SQL.Bind_Variable to supply the value. For each output placeholder, you must also use EXEC_SQL.Bind_Variable to specify the type of variable to use for retrieving the value in subsequent EXEC_SQL.Variable_Value calls.

The input placeholder or bind variable in a SQL statement is identified by a name beginning with a colon. For example, the string ':X' is the bind variable in the following SQL statement:

SELECT ename FROM emp WHERE SAL >  :X;

The corresponding EXEC_SQL.Bind_Variable procedure is:

BIND_VARIABLE(connection_handle, cursor_handle, ':X', 3500);

Example


PROCEDURE getData(input_empno NUMBER) IS
  connection_id EXEC_SQL.CONNTYPE; 
  cursorID EXEC_SQL.CURSTYPE;
  sqlstr VARCHAR2(1000);

  ...

BEGIN
  connection_id := EXEC_SQL.OPEN_CONNECTION(connect_str);
  cursorID := EXEC_SQL.OPEN_CURSOR(connection_id);
  --
  -- the statement to be parsed contains a bind variable
  --
  sqlstr := 'select ename from emp where empno = :bn';
  --
  -- perform parsing
  --
  EXEC_SQL.PARSE(connection_id, cursorID, sqlstr, exec_sql.V7); 
  --
  -- the bind_variable procedure assigns the value of the input argument to the named 
  -- bind variable. Note the use of the semi-colon and the quotes to designate the 
  -- bind variable. The bind_variable procedure is called after the parse procedure.
  --
  EXEC_SQL.BIND_VARIABLE(connection_id, cursorID, ':bn', input_empno); 
  EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, 1, mynum);

  ...

END;