Dblink from oracle to PostgreSQL-compilation error

[satish]

Hi erman,

have successfully established heterogenous connectivity between Oracle 12C and Postgres SQL. I have defined a database link PG and the Link is working fine when I try to access Postgres table using select statement. Now the problem occurs when I have to execute this query from within a PL/SQL block. When I try compiling this code, Oracle throws out 'ORA-04052' error complaining it cannot lookup object postgres.employees. ERROR: relation "postgres.employees" does not exist; The problem is pl/sql compiler is prefixing the qualified table name with DB link username "postgres". Is there a way to tell Oracle not to do that? Hence I m getting the error relation doesn't exits as no such table exist at postgres end. Has anyone tried using DB/hetrogenous link in PL/SQL block

Thanks

[ErmanArslansOracleBlog]

Send me the screenshot where I can see the error and the plsql itself. So I can check the whole error stack and the PLSQL that is failing.
Ensure your setup is okay, the gateway setup files... Also check if you can workaround the issue by using synonyms..

[satish]

Dear erman,

Dblink is working

SQL> select count(*) from "test_table"@ortopost;



 COUNT(*)

----------

  906608



SQL> create or replace PROCEDURE test_ortopost_proc

IS

 2  3 CURSOR monthly_cur IS

 4     SELECT

 5   "gpf_no",

  "trans_month",

 6  7   "trans_year",

 8   "reg_contribution"

 9 FROM

 10   "test_table"@ortopost

 11 WHERE

 12   "trans_year" = 2014

   and "trans_month" =1

 13  14 ORDER BY

  "trans_month" DESC;

 15  16 BEGIN

 17  dbms_output.put_line ('welcome');

 18  FOR monthly_rec IN monthly_cur loop

 19   INSERT INTO apps.xxttd_gpf_mon_test_stg_tbl (

    gpf_no,

    trans_month,

 20  21  22     trans_year,

 23     reg_contribution

 24   ) VALUES (

 25     monthly_rec.gpf_no,

    monthly_rec.trans_month,

 26  27     monthly_rec.trans_year,

    monthly_rec.reg_contribution

 28  29   );

  END LOOP;

 30  31   EXCEPTION

 32         WHEN OTHERS THEN

 33           fnd_file.put_line(

 34             fnd_file.log,

 35             ' Exception while fetching data from ortopostsql table ' || sqlerrm

          );

END; 36  37

 38 /

create or replace PROCEDURE test_ortopost_proc

*

ERROR at line 1:

ORA-04045: errors during recompilation/revalidation of APPS.TEST_ortopost_PROC

ORA-04052: error occurred when looking up remote object

[e-mail]

ORA-00604: error occurred at recursive SQL level 2

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

ERROR: relation "readonly.test_table" does not exist;

No query has been executed with that handle {42P01,NativeErr = 1}

ORA-02063: preceding 3 lines from ortopost

Thank you

[ErmanArslansOracleBlog]

Did you check the following MOS Notes?

ERROR: relation "table" does not exist; No query has been executed with that handle {42P01,NativeErr = 1}( from oracle to postgres) (Doc ID 2520893.1)

This one especially; -> Ora-4052 Ora-28500 With DG4ODBC Select Command Denied Using Pl/Sql And Mysql (Doc ID 819344.1) -- you may need to change that select statement accordingly.**

I also see the US.ORACLE.COM suffix appended to the dblink name.. That's normal as if you specify only the database name, then Oracle Database implicitly appends the database domain of the local database. You may fix this by changing the global name / db_domain accordingly.. Or by  creating the db link with a domain name.. That may not be a problem at all in your case.. I mean I can't just say it is a real problem or not in this case, but keep that in mind as well.