Oracle SQL procedure getting invalid after table alter

Question

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?

user_13755538 · Answer

Actually, your object is not really invalid. The issue happens as your second procedure contains a where clause that produces an invalidation in the dictionary based on rules.

If you run the procedure, it will become valid automatically.

Demo

Oracle 19c

SQL> alter session set current_schema=test1 ;

Session altered.

SQL> create table t1(a number,b number);

Table created.

SQL> create or replace procedure p1
    is
    x number;
    y  number;
    begin
    select a,b into x,y from t1;
    end;
    /  2    3    4    5    6    7    8

Procedure created.

SQL> 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;
    /
  2    3    4    5    6    7    8
Procedure created.

SQL>  alter table t1 add (d number);

Table altered.

SQL> select object_name,status from dba_objects where object_name in ('T1','P1','P2') and owner='TEST1' ;

OBJECT_NAME               STATUS
------------------------- --------------------
P1                        VALID
P2                        INVALID
T1                        VALID

Now, if the procedure was really invalid, you could not execute it. Let's insert one row in your table and try to run it.

SQL> insert into t1 values ( 1 ,1 ,1) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> set serveroutput on
SQL> exec p2 ( i=>1 ) ;

PL/SQL procedure successfully completed.

SQL> select object_name,status from dba_objects where object_name in ('T','P1','P2') and owner='TEST1' ;

OBJECT_NAME               STATUS
------------------------- --------------------
P1                        VALID
P2                        VALID
T1                        VALID

There are internal rules which define the invalidation of objects in the dictionary, such as procedures, functions or packages when some changes happen in the objects which those have dependencies upon.

In this specific case, there is no real invalidation, as the object was only marked as invalid in the dictionary after the DDL was executed.

The reason why the first one was not invalid is because it does not contain any where clause, and only is retrieving the two existing columns. The second becomes invalid as it contains a where clause to a PLSQL variable. See below

https://docs.oracle.com/cd/E24693_01/appdev.11203/e17125/adfns_dependencies.htm#CHDJIIFC

Oracle SQL procedure getting invalid after table alter

1 Answers