Use of Bind Variable in SQL Queries on SQLPLUS

Use of Bind Variable in SQLPLUS and SQL Queries of PL/SQL Block

Bind variable is used in SQLPLUS as replacement of literal values.
If you want to use bind variable in SQLPLUS first need to declare it as PL/SQL and then use in your statements or queries.

Following are the steps show you the example of using bind variables in SQLPLUS windows:

Create or Declare the Bind variable
Create the bind variable with VARIABLE Keyword and then specify its data type.

VARIABLE bind_var NUMBER

Refer or use of bind variable
use variables in PL/SQL by typing a colon (:) followed by variable name

:bind_var := 1;

Display assigned value of bind variable

PRINT bind_var

Example of bind variable as PL/SQL:

-- Assign value to bind variable
VARIABLE bind_var NUMBER;
Begin
:bind_var:= 1;
End;
/

--- Output
Print :bind_var

BIND_VAR
----------
1

Example of Bind Variable as SELECT query in PL/SQL:

-- Create table
SQL> create table test(id number,name varchar2(10));
Table created.

-- Insert data
SQL> insert into test values(1,'RAM');
1 row created.
SQL> Commit;

-- Use bind variable to fetch data from SQL Query
VARIABLE bind_var NUMBER;
VARIABLE bind_var2 varchar2(10);
Begin
:bind_var:= 1;
select name into :bind_var2 from test where id = :bind_var;
End;
/

-- Output
SQL> Print :bind_var2
BIND_VAR2
----------------
RAM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.