Oracle Database Interview Question:

What Is the Scope of a Local Variable?

Submitted by: Administrator

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) := 'ggl';
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 = ggl
Y = 999999
X = ggl
Y = CENTER
Z = -1
Submitted by: Administrator