Oracle PL/SQL Tutorial - PL/SQL Scope

The variable scope refers to when a declared item can be seen by another PL/SQL block.

Any item declared in the declaration section of a function or procedure is visible only within the same function or procedure.

Any item declared in the declaration section of a package body is visible only within any other item in the same package body.

Any item declared in a package specification is visible to any other stored function, stored procedure, and package for which the owner of the calling method has execute privileges.

Example 2

The following code is a Package Specification to Test Scope.


create or replace package SCOPE as 
    -- global variable declaration -- from  w w w . j  a  v a2 s .c  o m
    gv_scope varchar2(80) := 'I''m a global (or package spec) variable'; 
    
    -- a package function declaration 
    FUNCTION my_scope_is_global return varchar2; 

    -- a package procedure declaration 
    PROCEDURE my_scope_is_global; 

end SCOPE; 
/

The following code is A Package Body to Test Scope.


create or replace package SCOPE as 
    -- global variable declaration -- www .j  a  va  2 s  .  c om
    gv_scope varchar2(80) := 'I''m a global (or package spec) variable'; 
    
    -- a package function declaration 
    FUNCTION my_scope_is_global return varchar2; 

    -- a package procedure declaration 
    PROCEDURE my_scope_is_global; 

end SCOPE; 
/ 

create or replace package body SCOPE as 
    -- an package body variable declaration 
    iv_scope varchar2(80) := 'a package body variable'; 
    
    -- package body function declaration 
    FUNCTION my_scope_is_instance return varchar2 is 
        v_answer_1 varchar2(3) := 'Yes'; 
    begin 
        SYS.DBMS_OUTPUT.put_line(chr(9)||'in function'); 
        SYS.DBMS_OUTPUT.put_line(chr(9)||gv_scope); 
        return v_answer_1; 
    end my_scope_is_instance; 

    -- package function declaration 
    FUNCTION my_scope_is_global 
    return varchar2 is 
        v_answer_2 varchar2(3) := 'Yes'; 
    begin 
        SYS.DBMS_OUTPUT.put_line(chr(9)||'procedure'); 
        SYS.DBMS_OUTPUT.put_line(chr(9)||iv_scope); 
        return v_answer_2; 
    end my_scope_is_global; 


    -- package body procedure declaration 
    PROCEDURE my_scope_is_instance is 
        v_answer_3 varchar2(3) := 'Yes'; 
    begin 
        SYS.DBMS_OUTPUT.put_line(chr(9)||'my_scope_is_instance'); 
        SYS.DBMS_OUTPUT.put_line(chr(9)||gv_scope); 
        SYS.DBMS_OUTPUT.put_line(v_answer_3); 
    end my_scope_is_instance; 


    -- package procedure declaration 
    PROCEDURE my_scope_is_global is 
        v_answer_4 varchar2(3) := 'Yes'; 
    begin 
        SYS.DBMS_OUTPUT.put_line(chr(9)||'my_scope_is_global'); 
        SYS.DBMS_OUTPUT.put_line(chr(9)||iv_scope); 
        SYS.DBMS_OUTPUT.put_line(v_answer_4); 
    end my_scope_is_global; 
end SCOPE; 
/

The following code shows how to use the Package SCOPE.


declare -- from  ww w  . j av  a 2  s . c  o  m
    v_scope varchar2(40) := 'a local variable'; 
    -- This is a local (or embedded) function 
    FUNCTION my_scope_is_local 
    return varchar2 is 
        v_answer_0 varchar2(3) := 'Yes'; 
    begin 
        return v_answer_0; 
    end my_scope_is_local; 

    -- This is a local (or embedded) procedure 
    PROCEDURE my_scope_is_local is 
        v_answer varchar2(3) := 'Yes'; 
    begin 
        SYS.DBMS_OUTPUT.put_line(v_answer); 
    end my_scope_is_local; 

begin 
    SYS.DBMS_OUTPUT.put_line(v_scope); 
    SYS.DBMS_OUTPUT.put_line('Can I access SCOPE'' global variable?'); 
    SYS.DBMS_OUTPUT.put_line(SCOPE.gv_scope); 
    SYS.DBMS_OUTPUT.put_line('Can I access SCOPE'' instance variable?'); 
    
    SYS.DBMS_OUTPUT.put_line(my_scope_is_local()); 
    SYS.DBMS_OUTPUT.put_line('Can I access SCOPE'' global function?'); 
    SYS.DBMS_OUTPUT.put_line(SCOPE.my_scope_is_global()); 
    SYS.DBMS_OUTPUT.put_line('Can I access SCOPE'' instance function?'); 
    
    SYS.DBMS_OUTPUT.put_line('Can I access my local procedure?'); 
    my_scope_is_local(); 
    SYS.DBMS_OUTPUT.put_line('Can I access SCOPE'' global procedure?'); 
    SCOPE.my_scope_is_global(); 
    SYS.DBMS_OUTPUT.put_line('Can I access SCOPE'' instance procedure?'); 
end; 
/

The code above generates the following result.