How to Define A Global Variable In Pl/Sql In Oracle?

9 minutes read

To define a global variable in PL/SQL in Oracle, you can use the DECLARE keyword followed by the variable name, data type, and an optional initial value. Here is an example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
DECLARE
  global_variable NUMBER := 10;
  -- You can replace NUMBER with any other appropriate data type
  
  -- additional global variables can be defined here
  
BEGIN
  -- your PL/SQL code here
END;
/


In the above example, global_variable is defined as a global variable of type NUMBER with an initial value of 10. You can replace NUMBER with any other suitable data type like VARCHAR2, DATE, etc., based on your requirements.


Global variables can be utilized across multiple PL/SQL blocks or procedures within a session. However, it's important to note that the scope of such variables is limited to the session in which they are defined.

Best Oracle Books to Read in 2024

1
Oracle PL/SQL by Example (The Oracle Press Database and Data Science)

Rating is 5 out of 5

Oracle PL/SQL by Example (The Oracle Press Database and Data Science)

2
Oracle Database 12c DBA Handbook (Oracle Press)

Rating is 4.9 out of 5

Oracle Database 12c DBA Handbook (Oracle Press)

3
Oracle Database Administration: The Essential Refe: A Quick Reference for the Oracle DBA

Rating is 4.8 out of 5

Oracle Database Administration: The Essential Refe: A Quick Reference for the Oracle DBA

4
Oracle DBA Mentor: Succeeding as an Oracle Database Administrator

Rating is 4.7 out of 5

Oracle DBA Mentor: Succeeding as an Oracle Database Administrator

5
OCA Oracle Database SQL Exam Guide (Exam 1Z0-071) (Oracle Press)

Rating is 4.6 out of 5

OCA Oracle Database SQL Exam Guide (Exam 1Z0-071) (Oracle Press)

6
Oracle Database 12c SQL

Rating is 4.5 out of 5

Oracle Database 12c SQL

7
Oracle Autonomous Database in Enterprise Architecture: Utilize Oracle Cloud Infrastructure Autonomous Databases for better consolidation, automation, and security

Rating is 4.4 out of 5

Oracle Autonomous Database in Enterprise Architecture: Utilize Oracle Cloud Infrastructure Autonomous Databases for better consolidation, automation, and security


How to use a global variable in a SQL statement?

To use a global variable in a SQL statement, you need to follow these steps:

  1. Declare the global variable: You need to declare the global variable in the SQL environment. The mechanism to create global variables varies depending on the database system you are using. For example, in MySQL, you can use the SET statement to declare the global variable like this: SET @myVariable = 'your value';.
  2. Use the global variable in the SQL statement: Once the variable is declared, you can use it in your SQL statements like any other variable. Here's an example of a SELECT statement that uses a global variable:
1
2
3
SELECT column1, column2
FROM your_table
WHERE some_column = @myVariable;


This query uses the @myVariable global variable to filter the results based on the value stored in the variable.


Note: Global variables are session-specific, meaning they will be available throughout the current database session until they are either changed or removed.


How to override a global variable in a nested block in PL/SQL?

In PL/SQL, there is no direct way to override a global variable in a nested block. However, you can achieve a similar effect by declaring a local variable with the same name in the nested block.


Here is an example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
DECLARE
   global_var NUMBER := 10;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Global Variable: ' || global_var);
   
   BEGIN
      -- Declaring a local variable with the same name
      global_var NUMBER := 5;
     
      DBMS_OUTPUT.PUT_LINE('Local Variable: ' || global_var);
   END;
   
   DBMS_OUTPUT.PUT_LINE('Global Variable: ' || global_var);
END;


Output:

1
2
3
Global Variable: 10
Local Variable: 5
Global Variable: 10


As you can see, the local variable with the same name as the global variable takes precedence within the nested block, but the original global variable is unchanged.


How to assign a value to a global variable in PL/SQL?

To assign a value to a global variable in PL/SQL, you can declare the variable at the package level and then assign a value to it.


Here is an example:

  1. Declare a global variable at the package level:
1
2
3
4
CREATE OR REPLACE PACKAGE my_package AS
  g_variable NUMBER; -- Declare the global variable
END my_package;
/


  1. Assign a value to the global variable using the assignment operator (:=) within a procedure or function:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE OR REPLACE PACKAGE BODY my_package AS
  PROCEDURE assign_value(p_value NUMBER) IS
  BEGIN
    g_variable := p_value; -- Assign a value to the global variable
  END assign_value;
  
  FUNCTION get_value RETURN NUMBER IS
  BEGIN
    RETURN g_variable; -- Retrieve the value of the global variable
  END get_value;
END my_package;
/


Now you can call the assign_value procedure to assign a value to the global variable and then use the get_value function to retrieve the value.


Example usage:

1
2
3
4
5
BEGIN
  my_package.assign_value(42); -- Assign a value to the global variable
  DBMS_OUTPUT.PUT_LINE(my_package.get_value); -- Retrieve and display the value of the global variable
END;
/


This will output 42 as the assigned value of the global variable.


How to pass a global variable as a parameter in a PL/SQL procedure?

In PL/SQL, a global variable can be passed as a parameter to a procedure in the following steps:

  1. Declare the global variable in the package specification or declare it outside of any procedures or functions in the package body. -- In package specification PACKAGE my_package IS g_variable NUMBER := 10; PROCEDURE my_procedure(p_variable IN NUMBER); END my_package; -- OR in package body PACKAGE BODY my_package IS g_variable NUMBER := 10; PROCEDURE my_procedure(p_variable IN NUMBER); END my_package;
  2. Define the procedure in the package specification or body and include the parameter to receive the global variable value. -- In package specification PACKAGE my_package IS g_variable NUMBER := 10; PROCEDURE my_procedure(p_variable IN NUMBER); END my_package; -- OR in package body PACKAGE BODY my_package IS g_variable NUMBER := 10; PROCEDURE my_procedure(p_variable IN NUMBER) IS BEGIN -- Use the value of the global variable DBMS_OUTPUT.PUT_LINE('Global Variable: ' || g_variable); -- Use the value of the passed parameter DBMS_OUTPUT.PUT_LINE('Passed Parameter: ' || p_variable); END my_procedure; END my_package;
  3. Call the procedure and pass the global variable as a parameter. -- Call the procedure and pass the global variable BEGIN my_package.my_procedure(g_variable); END;


When the procedure is called, it will have access to the value of the global variable as well as any other parameters passed to it.

Facebook Twitter LinkedIn Telegram

Related Posts:

To execute stored procedures with parameters in Oracle, you can follow the steps below:Connect to the Oracle database using a client tool such as SQL Developer or SQL*Plus. Ensure that you have the necessary privileges to execute the stored procedure. Open a n...
To run a PL/SQL script in Oracle, you can follow these steps:Open the SQL*Plus application by typing "sqlplus" in the command prompt or terminal. Connect to Oracle by providing your username, password, and database details. For example: SQL> CONNECT...
Recursive Oracle SQL queries can be avoided by following certain techniques and approaches. Here are some methods to avoid recursive Oracle SQL queries:Restructuring the query: Instead of using recursive SQL, try to restructure the query using standard SQL fea...