Invalidation Of Objects When An Object With Same Name As Public Synonym Created. (Doc ID 1264502.1)

Last updated on AUGUST 30, 2017

Applies to:

PL/SQL - Version 11.1.0.7 to 11.2.0.2 [Release 11.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 24-Aug-2017***

Symptoms

After recreating a package in Schema A  which has the same name as  a public synonym a package in schema B which has the same name may become invalid.

The problem may also be exposed with the following error message seen intermittently:


ORA-6508 PL/SQL: could not find program unit being called


create user usera identified by tiger;
create user userb identified by tiger;
create user userc identified by tiger;

grant dba to usera;
grant dba to userb;
grant dba to userc;

create or replace package usera.pkgtest is
  procedure procA;
  end pkgtest;
  /

create or replace package body userA.pkgtest is
   procedure procA is
      pragma autonomous_transaction;
   begin
     null;
   end;
   end pkgtest;
  /
grant execute on userA.pkgtest to public;

--Grant succeeded.

create or replace public synonym pkgtest for userA.pkgtest;


-- now create in userC schema a package that calls userA.pkgtest.procA

create or replace package userc.call_pkgtest is
procedure procB;
end call_pkgtest;
/


create or replace package body userc.call_pkgtest is
procedure procB is
begin
pkgtest.procA;
end;
end call_pkgtest;
/

-- check for invalid objects...

SELECT OWNER, OBJECT_NAME,STATUS
FROM dba_objects
WHERE owner IN ('USERA','USERB','USERC')
AND OBJECT_NAME IN ('CALL_PKGTEST','PKGTEST');

/*
USERA
PKGTEST
VALID

USERA
PKGTEST
VALID

USERC
CALL_PKGTEST
VALID

USERC
CALL_PKGTEST
VALID
*/
-- In the 3rd schema recreate the package -with the same name.

create or replace package userB.pkgtest is
procedure procA;
end pkgtest;
/

create or replace package body userB.pkgtest is
procedure procA is
pragma autonomous_transaction;
begin
null;
end;
end pkgtest;
/
-- drop the package we just created and recreate it..

drop package userB.pkgtest;

create or replace package userB.pkgtest is
procedure procA;
end pkgtest;
/

create or replace package body userB.pkgtest is
procedure procA is
pragma autonomous_transaction;
begin
null;
end;
end pkgtest;
/

--Check for invalid objects.

SELECT OWNER, OBJECT_NAME,STATUS
FROM dba_objects
WHERE owner IN ('USERA','USERB','USERC')
AND OBJECT_NAME IN ('CALL_PKGTEST','PKGTEST');

/*
USERA
PKGTEST
VALID

USERA
PKGTEST
VALID

USERB
PKGTEST
VALID

USERB
PKGTEST
VALID

USERC
CALL_PKGTEST
INVALID <
USERC
CALL_PKGTEST
VALID


6 rows selected.
*/

Changes

Problem does not occur in 10.2.0.x or in 11.2.0.1.0

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms