In this article, we will discuss PL/SQL Variables in Oracle PL/SQL tutorial, and you will learn about PL/SQL variables and how to use them effectively.
If you missed the previous article of data types in PL/SQL, then please click here.
Let’s start the tour…
A variable is a title assigned to a storage area that a program can use. Each variable in PL/SQL has a specific data type, which determines the size and layout of the variable’s memory.
NOTE: A variable should not exceed 30 characters. Its letter optionally followed by more letters, dollar signs, numerals, underscore etc.
How to declare variables in Oracle PL/SQL

The variables must be declared in the declaration section or in a package as a global variable. After declaring the variables, PL/SQL allocates memory for the variable’s value, and the storage location identifies by the variable name.
The syntax for a PL/SQL Variables declaration is :
variable_name datatype [NOT NULL] [:= Default initial_values];
Here, First, specify the name of the variable. The name of the variable should be as descriptive as possible, e.g., 2_total_sales
.Second, choose an appropriate data type for the variable, depending on the kind of value which you want to store, for example, number, character, Boolean, and DateTime. PL/SQL allows us to set a default value for a variable at the declaration time. To assign a default value to a variable, we use the assignment operator (:=
) or the DEFAULT keyword. or you can also initiate the value of the variable.
Naming rules for PL/SQL variables
Like every programming language, the variable must follow some naming rules. So, here in PL/SQL variables, some rules are there:
- The variable_name should not exceed 30 characters.
- The name of the variable must begin with ASCII letter. The PL/SQL is not case sensitive so, it can be either lowercase or uppercase. For example, v_data and V_DATA both refer to the same variables.
- We should make sure that our variables are easy to read and understand, after the first character, it may be any number, underscore (_) or a dollar sign ($).
- The NOT NULL is an optional specification on the variable.
Initializing Variables in PL/SQL
Whenever we declare a variable, PL/SQL assigns it a default value of NULL. If we want to initialize a variable with a value other than the NULL value, then we can do so during the declaration of a variable, using either of the following −
- The DEFAULT keyword
- The assignment operator
For example −
counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Hello Lucine';
Here, we can also specify NOT NULL constraint to avoid NULL value. If we specify the NOT NULL constraint, you must assign an initial value for that variable.
It is a good programming practice to initializing the variables properly; otherwise, sometimes programs would produce unexpected results.
Example of initializing variable:
DECLARE
var_salary number(8);
var_emp_id number(6) = 226;
BEGIN
SELECT salary
INTO var_salary
FROM employee
WHERE emp_id = var_emp_id;
dbms_output.put_line(var_salary);
dbms_output.put_line('The employee '
|| var_emp_id || ' has salary ' || var_salary);
END;
/
NOTE: backward slash ‘/’ in the above record indicates to execute the above PL/SQL Block.
Variable Scope in PL/SQL
PL/SQL allows the nesting of blocks. A program block could contain another inner block. If we declare a variable within an inner block, it is not accessible to an outer block. There are two types of variable scope in PL/SQL:
- Local variables – These are declared in an inner block and cannot be referenced by outside Blocks.
- Global variables – These are declared in an outer block and can be referenced by its itself and by its inner blocks.
Example of Local and Global variables:
DECLARE
-- Global variables
vnum1 number := 95;
vnum2 number := 75;
BEGIN
dbms_output.put_line('The outer Variable num1: ' || vnum1);
dbms_output.put_line('The outer Variable num2: ' || vnum2);
DECLARE
-- Local variables
num1 number := 195;
num2 number := 185;
BEGIN
dbms_output.put_line('Inner Variable num1: ' || num1);
dbms_output.put_line('Inner Variable num2: ' || num2);
END;
END;
/
After the execution, this will produce the following result:
Outer Variable vnum1: 95
Outer Variable vnum2: 75
Inner Variable num1: 200
Inner Variable num2: 195
PL/SQL procedure successfully completed.
FAQs:
Answer: A data type with size, scale or precision limit is called a constrained declaration. The constrained declaration needs less memory than an unconstrained declaration.
Answer: True.
Answer: A BLOB data type is a varying length binary string that is used to store two gigabytes of memory. Length should be specified in Bytes for BLOB
Answer: the NULL value represents missing or unknown data. This uses as a place holder or represented, it in as default entry to indicate that there is no actual data present.
Answer: The WITH CHECK option clause specifies the verify level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the subquery.
This article is all about the variables their uses and etc. I hope this article is as much as easy for anyone. In the future article we will discuss the constant, Literals, operators and many more for that, please stay with us.
Nice keep it up
Very informative
Keep it up computer wali madam ji🤣
Very authentic Information
Very informative article! This article incredibly helpful for programmers. Keep up the good work!!
Very informative
very good information. keep doing it
Hello Lucine,
Great article! This is surely going to help beginners programmers.
Informative
Best site for technical information…..keep sharing
Nicely written
Good day! Would you mind if I share your blog with my twitter group?
There’s a lot of folks that I think would really enjoy your content.
Please let me know. Cheers
This info is priceless. Where can I find out more?
After looking into a handful of the blog articles on your site, I truly like your way of writing a blog.
I saved as a favorite it to my bookmark webpage list and will be checking back in the near
future. Please check out my web site as well and tell
me how you feel.
Hi there i am kavin, its my first time to commenting
anywhere, when i read this article i thought i could also
make comment due to this good piece of writing.
A person necessarily help to make severely posts I’d state.
This is the very first time I frequented your website page and so far?
I surprised with the research you made to create this actual submit amazing.
Wonderful activity!
Great blog right here! Also your site rather a lot up very fast!
What web host are you the usage of? Can I get your affiliate link to your host?
I wish my site loaded up as fast as yours lol
Hi friends, its wonderful paragraph about educationand entirely
defined, keep it up all the time.
There’s certainly a lot to know about this issue. I like all the points
you made.
What’s up, the whole thing is going sound here and ofcourse every
one is sharing information, that’s in fact fine, keep up writing.
I simply could not depart your site before suggesting
that I actually loved the standard info an individual supply for
your visitors? Is going to be again incessantly to check out new posts
Thank you for the good writeup. It in fact was a amusement account
it. Look advanced to far added agreeable from you! By the way, how can we communicate?
we can communicate through emails.
This piece of writing is really a good one it helps new web
visitors, who are wishing for blogging.
Your style is really unique in comparison to other folks I have read stuff from.
Many thanks for posting when you have the opportunity, Guess I’ll just book mark this site.
Thank you so much, please keep visiting