Oracle 11g存储过程通过DBLINK访问PostgreSQL报错求助

在Oracle 11g环境中，通过DBLINK直接查询PostgreSQL数据可正常运行，但创建包含该查询的存储过程时触发报错：

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 not exist

存储过程代码如下：

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;

• 直接授予存储过程所有者远程对象权限：存储过程运行时不会继承角色权限，需直接给存储过程所属用户授予远程表的SELECT权限：

  GRANT SELECT ON "table_1"@DBLINK_NAME TO 你的存储过程用户名;
  GRANT SELECT ON "table_2"@DBLINK_NAME TO 你的存储过程用户名;
  

• 显式指定PostgreSQL表的Schema：报错提示找不到postgres.card，可能是Oracle默认使用postgres schema查找远程表，需在远程表名前显式指定实际Schema（比如public）：

  -- 修改后的USING子查询示例
  SELECT c."card_id", 1, n."channel"
  FROM    "public"."table_1"@DBLINK_NAME n
       JOIN "public"."table_2"@DBLINK_NAME c
       ON n."card_id" = c."id"
  WHERE n."type" = 'param1'
  

• 改用动态SQL执行MERGE：Oracle 11g对非Oracle DBLINK的静态SQL解析存在兼容性问题，将MERGE语句改为动态SQL执行：

  CREATE OR REPLACE PROCEDURE test_merge as
  begin
    EXECUTE IMMEDIATE '
      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;
  

  注意动态SQL中单引号需要用两个单引号转义。

• 检查DBLINK配置：确认DBLINK的HS参数（如HS=OK）配置正确，同时PostgreSQL连接用户拥有对应表的访问权限，init.ora中的HS_FDS_CONNECT_INFO指向正确的PostgreSQL数据库实例。

内容的提问来源于stack exchange，提问作者Alex1__1