CREATE TABLE fgd_test
AS
   SELECT *
     FROM hr.employees

Get and Display Validity Status of Program Unit

CREATE OR REPLACE PROCEDURE show_validity (  
   change_in    IN VARCHAR2  
 , unit_name_in      IN VARCHAR2  
 , recompile_in IN BOOLEAN DEFAULT TRUE  
)  
IS  
   l_validity   all_objects.status%TYPE;  
BEGIN  
   SELECT status  
     INTO l_validity  
     FROM user_objects  
    WHERE object_name = unit_name_in;  
  
   DBMS_OUTPUT.put_line ('After "' || change_in || '"');  
 
   DBMS_OUTPUT.put_line (  
      '   State of ' || unit_name_in || ' = ' || l_validity );  
  
   IF l_validity = 'INVALID' AND recompile_in  
   THEN  
      EXECUTE IMMEDIATE   'alter procedure '  
                       || unit_name_in  
                       || ' COMPILE REUSE SETTINGS';  
   END IF;  
END show_validity;

CREATE OR REPLACE PACKAGE pkg1 
IS 
   PROCEDURE proc1 (a IN VARCHAR2); 
 
   FUNCTION func1 RETURN VARCHAR2; 
END pkg1;

Dependent on Table and Package

CREATE OR REPLACE PROCEDURE use_pkg1 
IS 
   l_name   fgd_test.last_name%TYPE; 
BEGIN 
   SELECT e.last_name 
     INTO l_name 
     FROM fgd_test e 
    WHERE e.employee_id = 198; 
 
   pkg1.proc1 ('a'); 
END use_pkg1;

BEGIN
   show_validity ('Freshly Compiled', 'USE_PKG1');
END;

Change Type of Column NOT Used in USE_PKG1

ALTER TABLE fgd_test MODIFY first_name VARCHAR2(2000)

Still Valid!

BEGIN 
   DBMS_OUTPUT. 
    put_line ('Change size of first_name column - state should remain VALID.' 
             ); 
   show_validity ('Change LAST_NAME Column', 'USE_PKG1'); 
END;

Add New Subprogram to Package

CREATE OR REPLACE PACKAGE pkg1 
IS 
   PROCEDURE proc1 (a IN VARCHAR2); 
 
   FUNCTION func1 
      RETURN VARCHAR2; 
 
   FUNCTION func2 
      RETURN NUMBER; 
END pkg1;

Still Valid!

BEGIN 
   DBMS_OUTPUT.put_line ('Add a new function - should not affect state.'); 
   show_validity ('Add new function', 'USE_PKG1'); 
END;

Add a New Column

ALTER TABLE fgd_test ADD nickname VARCHAR2(100)

Still Valid!

BEGIN 
   DBMS_OUTPUT. 
    put_line ('Add column to fgd_test; should not affect state.'); 
   show_validity ('Add new column', 'USE_PKG1'); 
END;

ALTER TABLE fgd_test DROP COLUMN nickname

Change Parameter List of Subprogram

CREATE OR REPLACE PACKAGE pkg1 
IS 
   PROCEDURE proc1 (a IN VARCHAR2, b IN PLS_INTEGER DEFAULT NULL); 
 
   FUNCTION func1 
      RETURN VARCHAR2; 
 
   FUNCTION func2 
      RETURN NUMBER; 
END pkg1;

Now Invalid

BEGIN 
   DBMS_OUTPUT. 
    put_line ( 
      'Add new IN parameter with trailing default - should not affect state? It does!' 
   ); 
   show_validity ('Add new parameter', 'USE_PKG1'); 
END;

Change Datatype of Existing Parameter

CREATE OR REPLACE PACKAGE pkg1 
IS 
   PROCEDURE proc1 (a IN DATE); 
 
   FUNCTION func1 
      RETURN VARCHAR2; 
 
   FUNCTION func2 
      RETURN NUMBER; 
END pkg1;

Now Invalid

BEGIN 
   DBMS_OUTPUT. 
    put_line ( 
      'Change datatype of argument that is used; should affect state.' 
   ); 
   show_validity ('Change parameter type to DATE', 'USE_PKG1'); 
END;

What Happens If I Change a Constant's Value?

CREATE OR REPLACE PACKAGE magic_value 
IS 
   c_value1   NUMBER := 1; 
END;

CREATE OR REPLACE PROCEDURE use_magic_value
IS
BEGIN
   DBMS_OUTPUT.put_line (magic_value.c_value1);
END;

Valid (Of Course) Right After Compilation

BEGIN 
   show_validity ('Initial Compile', 'USE_MAGIC_VALUE'); 
END;

Change Variable Value to 2

CREATE OR REPLACE PACKAGE magic_value 
IS 
   c_value1   NUMBER := 2; 
END;

Still Valid!

BEGIN 
   show_validity ('Change packaged constant value', 'USE_MAGIC_VALUE'); 
END;