Schema Object Dependencies

Recompilation occurs automatically when the invalid dependent object is referenced

SQL> CREATE TABLE test_table ( col1 INTEGER, col2 INTEGER );

Table created.

SQL> CREATE OR REPLACE PROCEDURE test_proc
  2  AS
  3  BEGIN
  4  FOR x IN ( SELECT col1, col2 FROM test_table )
  5  LOOP
  6  -- process data
  7  NULL;
  8  END LOOP;
  9  END;
 10  /

Procedure created.

SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';

OBJECT_NAME   	STATUS
----------------------------
TEST_PROC	VALID


SQL> ALTER TABLE test_table ADD col3 NUMBER;

Table altered.

SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';

OBJECT_NAME   	STATUS
----------------------------
TEST_PROC	VALID


SQL> ALTER TABLE test_table MODIFY col1 VARCHAR2(20);

Table altered.

SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';

OBJECT_NAME   	STATUS
----------------------------
TEST_PROC	INVALID


SQL> EXECUTE test_proc

PL/SQL procedure successfully completed.

SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';

OBJECT_NAME   	STATUS
----------------------------
TEST_PROC	VALID

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.