Sunday, November 30, 2008

Use of bind variables in Sql*plus

Bind variables are variables that are declared inside PL/SQL or a variable in a SQL statement that must be replaced with a valid value.

In the SQL*Plus you can also create bind variable. If you create a bind variable in SQL*Plus, you can use the variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus.

Creating Bind Variables
In order to create bind variable in Sql*plus you have to use variable keyword. To create a bind variable named bind_var as number datatype use,
variable bind_var NUMBER

Displaying Bind Variables

To display all bind variables in the session just use variable keyword.
SQL> variable
variable bind_var
datatype NUMBER

In order to display the value of a particular bind variable use,
SQL> print bind_var
BIND_VAR
----------

Using Bind Variables
To use bind variable first use colon(:) and then give variable name. In order to change values of bind variable you must enter in a PL/SQL block. To change bind variable of bind_var to 10 use,
begin
:bind_var:=10;
end;
/

1 comment:

  1. This works all well and fine; but when tried in the real world, such as when the bind variable is needed to be passed to execute a SP it reverts back to NULL. My attempts to execute the SP within the block also failed.

    ReplyDelete