CODEWITHSUNDEEP
oracle-databaseplsqlscopeoracle12c
cpkendall
cpkendall

Reputation: 17

Oracle Variable Scope

I'm fairly new to Oracle, but have had a good search. Can someone explain the scoping rules in relation to this:

BEGIN
    DECLARE 
        Variable1 number := 1;

    BEGIN
       DECLARE 
        Variable2 number := 2;


        BEGIN  
            dbms_output.put_line('Variable1: ' || Variable1); 
            dbms_output.put_line('Variable2: ' || Variable2); 
        END;

        BEGIN  
            dbms_output.put_line('Variable1: ' || Variable1); 
            dbms_output.put_line('Variable2: ' || Variable2); 
        END;
    END;
END;

errors with:

Error report - ORA-06550: line 17, column 55: PLS-00201: identifier 'VARIABLE2' must be declared ORA-06550: line 17, column 17: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Why is Variable2 unavailable in my second attempt to output it? Is there a way to achieve access to Variable2 within the second BEGIN...END block, or is this just the way Oracle works?

I'm using Oracle 12c. Thanks!

Upvotes: 0

Views: 1542

Answers (2)

MT0
MT0

Reputation: 168096

A PL/SQL block has the format:

DECLARE
  -- your variables
BEGIN
  -- your statements
END;

The DECLARE part of the block is optional (but is still part of the block)

So your code is:

BEGIN                         -- Block 1 Start
  DECLARE                     -- Block 1.1 Start
    Variable1 number := 1;
  BEGIN
    DECLARE                   -- Block 1.1.1 Start
      Variable2 number := 2;
    BEGIN  
      NULL;
    END;                      -- Block 1.1.1 End

    BEGIN                     -- Block 1.1.2 Start
      NULL;
    END;                      -- Block 1.1.2 End
  END;                        -- Block 1.1 End
END;                          -- Block 1 End

You have 4 PL/SQL blocks:

  • Block 1 has no local variables.
  • Block 1.1 is nested inside Block 1 and declares the local variable variable1
  • Block 1.1.1 is nested inside Block 1.1 (which is nested inside Block 1) and declares the variable variable2 and can access its surrounding block's scopes so can see variable1.
  • Block 1.1.2 is nested inside Block 1.1 (which is nested inside Block 1) and has no local variables but can access its access its surrounding block's scopes so can see variable1. variable2 is not in an ancestor's scope but is in a sibling and is not visible.

Upvotes: 0

Turo
Turo

Reputation: 4914

I think your indentation is fooling you: A block is (the DECLARE part is optional)

DECLARE
   ...
BEGIN
   ...
END

and the second output is clearly after the block in which Variable2 is declared.

Upvotes: 3

Related Questions