Under Oracle 10g, the dependency is judged to be at the object level, which means that once the object accessed by the stored procedure has changed, Oracle will place the stored procedure in the invalid state. So after the DDL operation for the table, the stored procedure needs to be recompiled.
in Oracle 11g, the decision to rely is more granular and the field level is judged. Although there are such good features, I recommend that you do the DDL and then recompile the stored procedure.
Under Oracle 10g:
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 as
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 ());
Sql> SELECT object_name, object_type, STATUS from user_objects WHERE object_name = ' p_test ';
Object_nam Object_typSTATUS
---------- ---------- ----------
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 as
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 ());
Sql> SELECT object_name, object_type, STATUS from user_objects WHERE object_name = ' p_test ';
Object_nam Object_typSTATUS
---------- ---------- ----------
P_test PROCEDURE VALID
