SQL & PL/SQL

I have a two databases. CARRIER_DEV  and TRANSPORT_DEV . Everything is working fine in Oracle 11g but when we migrated to Oracle12c,INFO_FUNC ( Oracle function) which resides in CARRIER_DEV under carrier schema is called from TRANSPORT_DEV, throwing ORA-01775 : looping chain of synonym. which caused ~9 invalid objects.

SELECT   INFO_FUNC('1','NAME') FROM DUAL ; This was called from TRANSPORT_DEV.

The DB link  from TRANSPORT_DEV to CARRIER_DEV was created using

CREATE PUBLIC DATABASE LINK RCAR
CONNECT TO CARRIER_USR
IDENTIFIED BY <PWD>
USING 'Carrier_dev.WORLD';

We created the synonym and gave the necessary grants in CARRIER_DEV.

CREATE PUBLIC SYNONYM INFO_FUNC FOR CARRIER.INFO_FUNC;
GRANT EXECUTE ON CARRIER.INFO_FUNC TO CARRIER_USR;

From Transport_dev database,

I created the following synonym to access the CARRIER.INFO_FUNC

CREATE PUBLIC SYNONYM INFO_FUNC FOR "PUBLIC".INFO_FUNC@RCAR;

After analysing the issue, i found that the public synonym in Transport_dev is using "PUBLIC" as a schema name instead of CARRIER.( This works fine in Oracle 11g. Please explain what is "PUBLIC" means here).

So I decided to use Schema name instead of "PUBLIC".

CREATE PUBLIC SYNONYM INFO_FUNC FOR CARRIER.INFO_FUNC@RCAR;

After this change all the invalid objects are gone in Oracle 12c but a new problem came up.

SELECT   INFO_FUNC('1','NAME') FROM DUAL ;  this SQL sataement now gives ORA-00980: synonym translation is no longer valid

SELECT   INFO_FUNC@RCAR('1','NAME') FROM DUAL ; also gives the same error.

In CARRIER_DEV database,

CREATE TABLE CARRIER.CARRIER_NAME
(
  NAME_SEQ_NO  NUMBER                           NOT NULL,
  LNAME        VARCHAR2(50 BYTE),
  FNAME        VARCHAR2(20 BYTE),
  MNAME        VARCHAR2(20 BYTE),
  SUFFIX       VARCHAR2(10 BYTE)
);

CREATE OR REPLACE FUNCTION CARRIER."INFO_FUNC" (pin_in VARCHAR2, field_in VARCHAR2 := 'TEST')
RETURN VARCHAR2
IS
   tmpVar VARCHAR2(100);

BEGIN
  
   
       SELECT lname||', '||fname||' '||mname||' '||suffix
          INTO tmpVar
          FROM Carrier_Name
         WHERE name_seq_no = pin_in;
   
END INFO_FUNC;
/

CREATE PUBLIC SYNONYM INFO_FUNC FOR CARRIER.INFO_FUNC;
GRANT EXECUTE ON CARRIER.INFO_FUNC TO CARRIER_USR;


In TRANSPORT_DEV

CREATE PUBLIC SYNONYM INFO_FUNC FOR CARRIER.INFO_FUNC@RCAR;

Please guide me.