Oracle11g's dependence judgment reaches the field level
In Oracle 10 Gb, the dependency only reaches the object level. That is to say, once the object accessed by the Stored Procedure changes, Oracle sets the stored procedure to INVALID. Therefore, after you perform DDL operations on the table, you need to re-compile the stored procedure.
In Oracle 11g, the dependency judgment is more detailed and the field level is determined. Although such a good feature exists, I suggest you recompile the stored procedure after DDL is implemented.
Oracle 10 GB:
SQL> select * from v $ version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bi
PL/SQL Release 10.2.0.1.0-Production
CORE 10.2.0.1.0 Production
TNS for 64-bit Windows: Version 10.2.0.1.0-Production
NLSRTL Version 10.2.0.1.0-Production
SQL> drop table TEST purge;
SQL> drop PROCEDURE P_TEST;
SQL> CREATE TABLE TEST (ID NUMBER );
SQL> CREATE OR REPLACE PROCEDURE P_TEST
BEGIN
Insert into test (ID) VALUES (100 );
END;
/
SQL> col OBJECT_NAME format a10;
SQL> col OBJECT_TYPE format a10;
SQL> col STATUS format a10;
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'P _ test ';
OBJECT_NAM OBJECT_TYP STATUS
------------------------------
P_TEST PROCEDURE VALID
SQL> ALTER TABLE TEST ADD (NAME VARCHAR2 (30 ));
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'P _ test ';
OBJECT_NAM OBJECT_TYP STATUS
------------------------------
P_TEST PROCEDURE INVALID
Under Oracle 11g:
SQL> select * from v $ version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production
SQL> drop table TEST purge;
SQL> drop PROCEDURE P_TEST;
SQL> CREATE TABLE TEST (ID NUMBER );
SQL> CREATE OR REPLACE PROCEDURE P_TEST
BEGIN
Insert into test (ID) VALUES (100 );
END;
/
SQL> col OBJECT_NAME format a10;
SQL> col OBJECT_TYPE format a10;
SQL> col STATUS format a10;
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'P _ test ';
OBJECT_NAM OBJECT_TYP STATUS
------------------------------
P_TEST PROCEDURE VALID
SQL> ALTER TABLE TEST ADD (NAME VARCHAR2 (30 ));
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'P _ test ';
OBJECT_NAM OBJECT_TYP STATUS
------------------------------
P_TEST PROCEDURE VALID