Skip to Main Content
  • Questions
  • Package gets invalidated when adding procedure to dependent

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ionut.

Asked: October 03, 2024 - 3:18 pm UTC

Last updated: October 04, 2024 - 4:51 pm UTC

Version: 21c XE

Viewed 1000+ times

You Asked

I have a misunderstanding related to invalidating a spec package.
I create 2 package spec.

create or replace package pkg_1
as
 function f_1 return number;
end;
/
create or replace package pkg_2
as
    v number := pkg_1.f_1;
end;
/


When checking, both of them are valid.

select object_name,
       status, object_type
  from user_objects
 where object_type = 'PACKAGE'
   and object_name in ('PKG_1', 'PKG_2');



Then, I define a procedure, P_1, in package spec of package pkg_1

create or replace package pkg_1
as
    function f_1 return number;
    procedure p_1;
end;

... and I check again the status of both packages

select object_name,
       status, object_type
  from user_objects
 where object_type = 'PACKAGE'
   and object_name in ('PKG_1', 'PKG_2');

... and both of them are valid

I define 2nd procedure P_2 in package spec of package pkg_1

create or replace package pkg_1
as
    procedure p_2;
    function f_1 return number;
    procedure p_1;
end;

... and I check again the status of both packages

select object_name,
       status, object_type
  from user_objects
 where object_type = 'PACKAGE'
   and object_name in ('PKG_1', 'PKG_2');


... but this time, the package spec of PKG_2 gets invalidated.

Can you explain, please, why after defining 2nd procedure (in front of function F_1) the package get invalidated as PKG_1 is not impacted by this?
In Fine Grained Dependency Tracking, I couldn't find anything specified related to changing the order of the objects into a package.

On the other hand, if I create the PKG_1 as

create or replace package pkg_1
as
    function f_1 return number;
    procedure p_1;
    procedure p_2;
end;
instead of

create or replace package pkg_1
as
    procedure p_2;
    function f_1 return number;
    procedure p_1;
end;

Both packages are valid.

Thank you,

and Chris said...

This is as documented:

Dependent object references a package procedure or function whose call signature or entry-point number*, changed. [...]

*The entry-point number of a procedure or function is determined by its location in the PL/SQL package code. A procedure or function added to the end of a PL/SQL package is given a new entry-point number.


https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/schema-object-dependency.html#GUID-AEC8DF50-2AC2-4697-8CA9-F13108FBB810

You've added a procedure at the start of the package. So the entry-point number for f_1 has changed.

You can check this by querying:

select procedure_name, subprogram_id 
from   user_procedures
where  object_name = 'PKG_1';


And you'll see the subprogram_id change.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.