-- 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
*/