When trying to alter a table , one of the procedure which has only select statement is getting invalid.
table scripts:
create table t1(a number,b number);
create or replace procedure p1
is
x number;
y number;
begin
select a,b into x,y from t1;
end;
/
create or replace procedure p2(i number)
is
x number;
y number;
begin
select a,b into x,y from t1 where i=1;
end;
/
alter table t1 add (d number);
select object_name,status from dba_objects where object_name in ('T','P1','P2');
T VALID
P1 VALID
P2 INVALID
observed that when procedure takes in parameter and if we are using it in the select statement, the object is getting invalid or else it is not getting invalid.
Is it possible to alter the table without the procedure getting invalid?