Hi Experts,
I understand that all the dependent SQL statements in library cache are
invalidated in case there is any DDL operation on an object.
But is there any basic rule when a PL/SQL object becomes invalid if there
is a DDL operation on dependent objects?
I did a small test below but couldn't figure out:
Database Version: EE 11.2.0.1.0
*create table t1 (no number);*
*create or replace procedure proc_test as*
* v_no number;*
* begin*
* select no into v_no from t1 where rownum<2;*
* end;*
*/*
truncate table t1; <<---procedure still valid
alter table t1 add (name varchar2(10)); <<--- procedure becomes invalid
alter table t1 drop column name; << --- procedure still valid
create index idx_t1_no on t1(no); <<--- Procedure stil valid
drop index idx_t1_no; <<---- Procedure still valid
If a "select * from t1" is used as cursor in the procedure, then any
addition/deletion/modification of the columns causes the procedure to
become invalid.
Thanks & Regards,
Ramniwas Chaurasia
