Compilation Fails When Referencing Record Type on Remote Database : ORA-4054 Database Link <Name> Does Not Exist (Doc ID 1324699.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 8.1.7.0 and later
Information in this document applies to any platform.
***Checked for relevance on 30-Oct-2013***

Symptoms

Consider the Scenario:

A record type is declared in Schema C
In Schema A a package uses this record type via a dblink (remote DB ) to Schema C.
A package in Schema B is compiled with a reference to the package in Schema A.

Compiling the package in schema B fails with:

ORA-04054 database link <name> does not exist

An example is given below:

-- CREATE 3 schema users  SchemaA, SchemaB and SchemaC


CREATE OR REPLACE
PACKAGE SchemaC.C IS
 --
 TYPE TYPEREC_EKBSTATUS IS RECORD (col1 VARCHAR2(1),
                                   col2 VARCHAR2(1)
                                   );
 --
END C;
/

CREATE PUBLIC SYNONYM C FOR SchemaC.C
/

GRANT EXECUTE ON SchemaC.C TO public WITH GRANT OPTION
/

CREATE OR REPLACE
PACKAGE BODY SchemaC.C IS
--
END C;
/
----------------

--Next create a dblink from SchemaA to SchemaC

connect SchemaA

create database link MYLINK
connect to SchemaC
identified by tiger
using 'ORCL'
/

--Database link created.

CREATE OR REPLACE
PACKAGE SchemaA.A
IS
--
SUBTYPE typerec_ekbstatus IS C.typerec_ekbstatus@MYLINK;
--
PROCEDURE test;
--
END A;
/

CREATE PUBLIC SYNONYM A FOR SchemaA.A
/
GRANT EXECUTE ON SchemaA.A TO SchemaB
/

CREATE OR REPLACE
PACKAGE BODY SchemaA.A
IS
--
PROCEDURE test
IS
v_ekbstatus C.typerec_ekbstatus@MYLINK;
BEGIN
  NULL;
END test;
--
END A;
/

----------
-- Quick test of the package

exec a.test

-- PL/SQL procedure successfully completed.

------------
-- now to test script schemaB.sql

conn SchemaB

CREATE OR REPLACE
PACKAGE SchemaB.B
IS
--
PROCEDURE test_type;
--
END B;
/


CREATE OR REPLACE
PACKAGE BODY SchemaB.B
IS
--
PROCEDURE test_type
IS
   v_stat A.typerec_ekbstatus;
BEGIN
   A.test;
END test_type;
--
END B;
/

-- Warning: Package Body created with compilation errors.

show errors
/*
Errors for PACKAGE BODY JSUNGB.B:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 ORA-04052: error occurred when looking up remote object
SCHEMAC.C@MYLINK
ORA-00604: error occurred at recursive SQL level 1
ORA-02019: connection description for remote database not found
*/

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