How To Create, Declare, Initialize and Display Bind Variables in PL/SQL
There are two types of variables in Oracle database.
- User variables. Discussed in PL/SQL Tutorial 2
- Bind variables a.k.a Host variables.
Unlike user variables which can only be declared inside the declaration section of PL/SQL block you can declare bind variable anywhere in the host environment and that is the reason why we also refer bind variables as host variable.
Definition
Bind variables in Oracle database can be defined as the variables that we create in SQL* PLUS and then reference in PL/SQL. ~ Oracle Docs
How To Declare a Bind Variable (Variable command)
Let’s see how to create or say declare a bind variable. We can declare a bind variable using VARIABLE command. Variable command declares the bind variable which you can refer in PL/SQL. Also as I said earlier in this tutorial that in order to declare bind variables we do not need to write any PL/SQL block or section.
Let’s do an example and declare our first bind variable
VARIABLE v_bind1 VARCHAR2 (10);
See how easy it is to declare a bind variable in oracle database! You simply have to write a command which starts with keyword VARIABLE followed by the name of your bind variable which is completely user defined along with the data type and data width. That’s how we declare a bind variable in Oracle database.
Did you notice that I didn’t write any PL/SQL block or section here to declare this bind variable which is very unlike the user variable.
Other Uses of Variable Command.
Declaring the bind variable is the first use of this variable command there are few other uses of it also. Let’s see what those are:
List all the bind variables declared in the session.
Yes using Variable command you can display the list of all the bind variables you have declared in the session. To display the list of all the bind variables you simply have to write the keyword variable and execute. Doing so will return list of all the bind variables.
Let’s see.
VARIABLE;
Execute the above command and that will show you the list of all the bind variables that you have declared in your session.
See the definition of bind variable.
Variable command can also show you the definition of any bind variable created in the session. By definition I mean the data type and data width of the variable. To see the definition of the bind variable you have to write the keyword VARIABLE followed by the name of the bind variable in question.
Let’s do an example and see the definition of this bind variable v_Bind2.
Variable v_bind2;
Execution of above command will show you the definition of bind variable RebellionRider.
So these are the few uses of Variable command in Oracle database. If you know any other uses then do tweet and tell me at @RebellionRider.
Restriction:
If you are creating a bind variable of NUMBER datatype then you can not specify the precision and scale.
Initialize the Bind Variable
As we have now declared the bind variable next we have to initialize it. We have several different ways of initializing the bind variable. Let’s see what those are.
You can initialize the bind variable using “Execute” command.
Execute command is like a wrapper which works as an execution section of PL/SQL block. Let’s see how it works. Let’s initialize our bind variable v_bind1 with a string RebellionRider.
Exec :v_bind1 := ‘Rebellion Rider’;
This statement starts with keyword Exec which is the starting 4 alphabets of Keyword Execute. You can either write whole keyword Execute or just the starting 4 alphabets “Exec” both will work fine. This is followed by the name of our bind variable which is v_bind1. After that we have assignment operator followed by the string Rebellion Rider, as it’s a string thus it’s enclosed in single quotes.
That’s the first way of initializing the bind variable. The second way is:
Initialize the bind variable by explicitly writing execution section of PL/SQL block.
If you do not like shortcuts and are willing to do some hard work of writing a few extra lines of code then this is for you.
SET SERVEROUTPUT ON;
BEGIN
:v_bind1 := ‘Manish Sharma’;
END;
/
This is a simple execution block where I initialized the bind variable v_bind1 with the string Manish Sharma.
That is how we initialize the bind variable in Oracle Database or in PL/SQL.
Referencing the Bind Variable
Manish why did you put the colon sign (:) before the name of bind variable (:v_bind1) while initializing it? Glad you asked.
Unlike user variables which you can access simply by writing their name in your code, you use colon before the name of bind variable to access them or in other words you can reference bind variable in PL/SQL by using a colon (:) followed immediately by the name of the variable as I did in the previous section.
Display The Bind variable.
There are 3 ways of displaying the value held by bind variable in PL/SQL or say in Oracle Database.
- Using DBMS OUTPUT package.
- Using Print command
- Setting Auto print parameter on
Let’s check out each of them one by one.
Using DBMS_OUTPUT Package
This is the simplest way of displaying the value held by any variable in PL/SQL. To display the value held by your bind variable you simply have to pass the name of your bind variable as a parameter to the PUT_LINE procedure of DBMS_OUTPUT package. Let’s see an example
BEGIN
:v_bind1 := ‘RebellionRider’;
DBMS_OUTPUT.PUT_LINE(:v_bind1);
END;
/
Things you must take care of here are:
- PUT_LINE is an executable statement which will require the execution section of PL/SQL block for its execution. In simple words you can only execute this statement in execution section of PL/SQL block otherwise you will get an error.
- To see the output returned from this statement you have set the serveroutput on. You can do that by simply writing and executing
SET SERVEROUTPUT ON;
I highly recommend you to read my PL/SQL Tutorial 1 to understand Blocks & Section in PL/SQL you can also watch my video tutorial on the same.
Using PRINT command
Like DBMS_OUTPUT statement print command also displays the current value of the bind variable except that you can write this command in host environment rather than inside any PL/SQL block or section. Yes similar to variable command, print command does not require any PL/SQL block for execution.
Suppose you want to see the current value of bind variable v_bind1 for that simply write the print command in your SQL*PLUS
Print :v_bind1;
Or
Print v_bind1;
Writing keyword PRINT without any argument will display you the current values of all the bind variables with their names in the session.
Setting Auto print parameter on
The last way of displaying the current value of a bind variable is by setting a session based parameter AUTOPRINT on. Doing so will display you the value of all the bind variables without the use of any specific commands such as Print or DBMS_OUTPUT which we just saw.
To set this parameter you simply have to write
SET AUTOPRINT ON;
And this command will set AutoPrint parameter on for the session which will automatically print the values of bind variable.
If you are like me who prefer learning by watching video then you can check out the video tutorial on this topic right here.
That’s all about Bind Variables. Next in the course you can learn about Conditional Control statements in PL/SQL here.
Hope this article was helpful. Kindly please share it with your friends and help me reach out to more people. Thanks guys & have a great day!
[…] can read more about bind variables in PL/SQL tutorial 6 […]