PL/SQL Objects Invalidation

From: Ramnivas Chaurasia <>
Date: Tue, 21 Feb 2017 16:43:24 +0530
Message-ID: <>

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

*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

Received on Tue Feb 21 2017 - 12:13:24 CET

Original text of this message