EXEC_SQL.BIND_VARIABLE
built-in procedureThis procedure binds a given value to a named variable in a SQL statement.
PROCEDURE EXEC_SQL.BIND_VARIABLE
ConnidIN CONNTYPE],
Curs_Id IN CURSTYPE,
Name IN VARCHAR2,
Value IN <datatype>);
where <datatype>
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);
Parameter |
Description |
|
Is the handle to the connection you want to use. If you do not specify
a connection, |
|
Is the cursor handle in which to bind the variable. |
|
Is the name of the variable in the SQL statement. |
|
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. |
|
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. |
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);
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;
About the EXEC_SQL
built-in package
Copyright © 1984, 2005, Oracle. All rights reserved.