Search code examples
postgresqloracle-databaseplsqldblink

How create Oracle procedure witch select with dblink from postgres

I can select(in Oracle) from postgers with dblink, and its work fine. But if i create procedure with this select:

Procedure:

`CREATE OR REPLACE PROCEDURE test_merge as
    begin
    MERGE INTO CARDS C
            USING (SELECT c."card_id", 1, n."channel"
                     FROM    "table_1"@DBLINK_NAME n
                          JOIN
                             "table_2"@DBLINK_NAME c
                          ON n."card_id" = c."id"
                    WHERE n."type" = 'param1') B
               ON (C.CARDID = B."card_id")
       WHEN MATCHED
       THEN
          UPDATE SET C.SENDR = 1, C.PHONE = '+' || B."channel";
    end;`

ORA-04052: error occurred when looking up remote object postgres.table_name@DBLINK_NAME ORA-00604: error occurred at recursive SQL level 1 ORA-28500: connection from ORACLE to a non-Oracle system return this message: ERROR: relation "postgres.card" does

Have any idea? Oracle 11g Thanks!

Solution

  • It was necessary to specify the owner of the database on postgre. In my case, it was enough to specify "public". in the procedure. before accessing tables.

    "public"."table_1"@DBLINK_NAME