Search This Blog

Thursday, October 31, 2013

PL/SQL Variables Scope

PL/Scope is a compiler-driven tool that collects data about identifiers in PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assigment) and the location of each usage in the source code.

PL/Scope enables the development of powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code. PL/Scope is intended for application developers, and will usually be used in the environment of a development database.

PROCEDURE PROC

AS
V_DESC_SIZE INTEGER(5);
V_PROD_DESCRIPTION VARCHAR2(70) := 'You ';
V_DATE1 DATE DEFAULT SYSDATE;
V_DATE2 V_DATE1%TYPE DEFAULT SYSDATE+360;
V_DATE_DIFF INTEGER;
V_DT_STR DATE := '05-FEB-2013';
V_DT_STR2 DATE := '03-JAN-2013';
V_OUTER VARCHAR2(20) :='Global variable';
BEGIN <<OUTER_LBL>>
DBMS_OUTPUT.PUT_LINE('PROCEDURE PROC');
V_DESC_SIZE := LENGTH(V_PROD_DESCRIPTION);
DBMS_OUTPUT.PUT_LINE('V_DESC_SIZE : '||V_DESC_SIZE);
V_DATE_DIFF := MONTHS_BETWEEN(V_DATE1, V_DATE2);
DBMS_OUTPUT.PUT_LINE('V_DATE_DIFF : '||V_DATE_DIFF );
DBMS_OUTPUT.PUT_LINE('V_DT_STR : '||V_DT_STR );
DBMS_OUTPUT.PUT_LINE('V_DT_STR2 : '||V_DT_STR2 );
    DECLARE 
        V_INNER VARCHAR2(20) := 'Local Var';
        V_OUTER VARCHAR2(20) :='V_OUTER LOCAL var';
    BEGIN <<INNER_LBL>>
        DBMS_OUTPUT.PUT_LINE('V_OUTER : '||V_OUTER );
        DBMS_OUTPUT.PUT_LINE('V_INNER : '||V_INNER );
        DBMS_OUTPUT.PUT_LINE('OUTER_LBL.V_OUTER : '||OUTER_LBL.V_OUTER );
        DBMS_OUTPUT.PUT_LINE('PROC.V_OUTER : '||PROC.V_OUTER );
        V_OUTER :=V_OUTER;
    END;
    DBMS_OUTPUT.PUT_LINE('V_OUTER : '||V_OUTER );
  
    
END PROC;

The out put will be
PROCEDURE PROC
V_DESC_SIZE : 4
V_DATE_DIFF : -12
V_DT_STR : 05-FEB-13
V_DT_STR2 : 03-JAN-13
V_OUTER : V_OUTER LOCAL var
V_INNER : Local Var
OUTER_LBL.V_OUTER : V_OUTER LOCAL var
PROC.V_OUTER : Global variable
V_OUTER : Global variable

Reference for extra reading:
http://docs.oracle.com/cd/B13789_01/appdev.101/b10807/02_funds.htm#i15754

No comments: