Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Scope of Local Variables

By: FYIcenter.com

(Continued from previous topic...)

What Is the Scope of a Local Variable?

The scope of a variable can be described with these rules:

  • A variable is valid within the procedure or function where it is defined.
  • A variable is also valid inside a sub procedure or function defined.
  • If a variable name is collided with another variable in a sub procedure or function, this variable becomes not visible in that sub procedure or function.

Here is a sample script to show you those rules:

The script below illustrates how to use named parameters:

SQL> CREATE OR REPLACE PROCEDURE PARENT AS
  2    X CHAR(10) := 'FYI';
  3    Y NUMBER := 999999.00;
  4    PROCEDURE CHILD AS
  5      Y CHAR(10) := 'CENTER';
  6      Z NUMBER := -1;
  7    BEGIN
  8      DBMS_OUTPUT.PUT_LINE('X = ' || X); -- X from PARENT
  9      DBMS_OUTPUT.PUT_LINE('Y = ' || Y); -- Y from CHILD
 10      DBMS_OUTPUT.PUT_LINE('Z = ' || TO_CHAR(Z));
 11    END;
 12  BEGIN
 13    DBMS_OUTPUT.PUT_LINE('X = ' || X); -- X from PARENT
 14    DBMS_OUTPUT.PUT_LINE('Y = ' || TO_CHAR(Y));
 15    -- DBMS_OUTPUT.PUT_LINE('Z = ' || TO_CHAR(Z));
 16    CHILD;
 17  END;
 18  /

SQL> EXECUTE PARENT;
X = FYI
Y = 999999
X = FYI
Y = CENTER
Z = -1

  1. What Is a Procedure?
  2. What Is a Function?
  3. How To Define an Anonymous Procedure without Variables?
  4. How To Define an Anonymous Procedure with Variables?
  5. How To Create a Stored Procedure?
  6. How To Execute a Stored Procedure?
  7. How To Drop a Stored Procedure?
  8. How To Pass Parameters to Procedures?
  9. How To Create a Stored Function?
  10. How To Call a Stored Function?
  11. How To Drop a Stored Function?
  12. How To Call a Stored Function with Parameters?
  13. How To Define a Sub Procedure?
  14. How To Call a Sub Procedure?
  15. How To Define a Sub Function?
  16. Can Sub Procedure/Function Be Called Recursively?
  17. What Happens If Recursive Calls Get Out of Control?
  18. What Is the Order of Defining Local Variables and Sub Procedures/Functions?
  19. What Is the Difference between Formal Parameters and Actual Parameters?
  20. What Are the Parameter Modes Supported by PL/SQL?
  21. How To Use "IN" Parameter Properly?
  22. How To Use "OUT" Parameter Properly?
  23. How To Use "IN OUT" Parameter Properly?
  24. How To Define Default Values for Formal Parameters?
  25. What Are Named Parameters?
  26. What Is the Scope of a Local Variable?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...