Compilation Fails When Referencing Record Type on Remote Database : ORA-4054 Database Link <Name> Does Not Exist
(Doc ID 1324699.1)
Last updated on DECEMBER 16, 2019
Applies to:
PL/SQL - Version 8.1.7.0 and laterInformation 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
*/
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |