• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » What is Oracle PLSQL Variables

What is Oracle PLSQL Variables

March 7, 2021 by techgoeasy Leave a Comment


Oracle PLSQL Variable

1) Variables and constants must be declared for use in procedural and SQL code, although the datatypes available in SQL are only a subset of those available in PL/SQL.

2) All variables and constants must be declared before they are referenced.

3) Remember that each variable must be declared on a separate line and multiple variables cannot be defined in the same statement.

4) The declarations of variables and constants are similar, but constant definitions must contain the CONSTANT keyword and must be assigned a value as part of the definition. Subsequent attempts to assign a value to a constant will result in an error

5)  %TYPE – allows for unknown column datatypes when assigning a variable. That is used to declare a variable that is of the same type as a specified table’s column

6) BOOLEAN variables can only accept values of TRUE, FALSE, and NULL.

7) ( : ) is used to prefix a bind variable.

8) By default Oracle PLSQL variable are initialized as Null. Unless you explicitly assigned a value to the variable, it is undefined

9) We can have Not Null on variable, then we must assign some value to it. We can change the value of that variable but we cannot make it null

10.We can initialized variable using the assignment operator (:=) or using Define

General syntax

variable_name datatype [NOT NULL := value ];

1) variable_name is the name of the variable.

2) datatype is a valid PL/SQL datatype.

Example of declaring Variables:

v_name varchar2(100); 
 creation_date Date;
 v_num number default 5;
 v_num1 number not null := 2;
 v_emp varchar2(20) := Null;
 Var5 varchar2(20) default Null;

Example of declaring Constants:

v_global constant number := 200; 
 Constants cannot be changed.
 You must initialize constants at the time of declaration.

Scope of Oracle PLSQL Variable

PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.

Local variables – These are declared in a inner block and cannot be referenced by outside Blocks.

Global variables – These are declared in a outer block and can be referenced by its itself and by its inner blocks.

Types of Variable

1) Scalar datatype: It holds single value. The main datatypes are the datatypes which are supported as column type in Oracle server. PLSQL also support Boolean type

2) Composite type: These are records  and allows groups of field to be defined  and manipulated in PLSQL. Composite datatypes also known as collection are table, record ,Varray and nested table. Composite datatype has internal components which can be manipulated individually

Related links

Oracle PLSQL documentation
Most commonly asked 25 Oracle PlSQL interview question
Oracle sql and plsql
how to write sql queries


Filed Under: Oracle

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • How to check Tablespace in Oracle -Size ,Free space,datafiles ,growth
  • Oracle dba interview questions and answers
  • how to check all constraints on a table in oracle
  • Oracle Indexes and types of indexes in oracle with example
  • how to tar a directory in Linux



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to run Autoconfig on Patch system in R12.2
  • Admin scripts in R12.2 Ebuisness Suite
  • Timezone settings in Oracle database
  • how to enable trace in oracle apps r12
  • Transparent Data Encryption Queries

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us