使用 DB Link 从远程表中获取数据的 PL/SQL 过程

由于您必须在查询中包含表名和数据库链接名，因此您需要动态 SQL。

下面是一个示例;我正在使用表格，因为我无权访问表格，所以 - 你必须解决这个问题。另外，我只是将输出显示到屏幕上 - 您将将其插入到表格中（也修复它）。user_...dba_...

SQL> set serveroutput on
SQL> declare
  2    rc           sys_refcursor;
  3    l_table_name user_tables.table_name%type;
  4    l_cnt        number;
  5  begin
  6    for src_link in (select db_link from user_db_links) loop
  7
  8      open rc for 'select table_name from user_tables@' || src_link.db_link;
  9
 10      loop
 11        fetch rc into l_table_name;
 12        exit when rc%notfound;
 13
 14        execute immediate 'select count(*) from ' || l_table_name ||'@'|| src_link.db_link
 15          into l_cnt;
 16
 17        dbms_output.put_line(l_table_name||'@'|| src_link.db_link ||' -> ' || l_cnt || ' row(s)');
 18      end loop;
 19
 20      close rc;
 21    end loop;
 22  end;
 23  /
BONUS@DBL_MIKE -> 0 row(s)
DUMMY@DBL_MIKE -> 1 row(s)
SALGRADE@DBL_MIKE -> 5 row(s)
DEMO_TAGS@DBL_MIKE -> 6 row(s)
<snip>

PL/SQL procedure successfully completed.

SQL>

[编辑]

例如，您的过程存在一些错误

• 缺少前面的空间WHERERC
• 缺少将表名括起来的单引号SQL_STMT2
• 变量的数据类型错误（应该是 ，而不是V_LAST_ANALYZEDDATENUMBER)
• 良好的实践表明，在使用动态 SQL 时，您应该首先显示要执行的语句 （using ），然后，当您确定它看起来没问题时，实际使用 .没有它，你就无法真正确定你要执行什么dbms_output.put_lineexecute immediate
• 您不需要动态 SQL 将行插入到目标表中;它没有任何动态
• 良好的做法是，您应该避免在循环中提交

修复后，它可以工作：

示例目标表：

SQL> CREATE TABLE swms_tab_count_stats_rds
  2  (
  3     opco_name         VARCHAR2 (30),
  4     table_name        VARCHAR2 (30),
  5     actual_rowcount   NUMBER,
  6     stats_num_rows    NUMBER,
  7     stats_date        DATE
  8  );

Table created.

过程（我使用和我自己的用户，）：all_tablesmike

SQL> CREATE OR REPLACE PROCEDURE fetch_tables_stats
  2  AS
  3  BEGIN
  4     DECLARE
  5        rc               SYS_REFCURSOR;
  6        l_table_name     all_tables.table_name%TYPE;
  7        v_row_count      NUMBER;
  8        v_num_rows       NUMBER;
  9        v_last_analyzed  DATE;
 10        sql_stmt1        VARCHAR2 (1000);
 11        sql_stmt2        VARCHAR2 (1000);
 12     BEGIN
 13        DELETE FROM swms_tab_count_stats_rds;
 14
 15        FOR src_link IN (SELECT db_link FROM all_db_links)
 16        LOOP
 17           OPEN rc FOR
 18                 'select table_name from all_tables@'
 19              || src_link.db_link
 20              || ' where owner = '
 21              || q'['MIKE']';
 22
 23           LOOP
 24              FETCH rc INTO l_table_name;
 25
 26              EXIT WHEN rc%NOTFOUND;
 27              sql_stmt1 :=
 28                 'select count(*) from ' || l_table_name || '@' || src_link.db_link;
 29
 30              EXECUTE IMMEDIATE sql_stmt1
 31                 INTO v_row_count;
 32
 33              sql_stmt2 :=
 34                    'SELECT num_rows, last_analyzed FROM all_tables@'
 35                 || src_link.db_link
 36                 || ' WHERE table_name = '
 37                 || CHR (39)
 38                 || l_table_name
 39                 || CHR (39)
 40                 || ' AND owner = '
 41                 || q'['MIKE']';
 42
 43              EXECUTE IMMEDIATE sql_stmt2
 44                 INTO v_num_rows, v_last_analyzed;
 45
 46              INSERT INTO swms_tab_count_stats_rds (opco_name,
 47                                                    table_name,
 48                                                    actual_rowcount,
 49                                                    stats_num_rows,
 50                                                    stats_date)
 51                   VALUES (src_link.db_link,
 52                           l_table_name,
 53                           v_row_count,
 54                           v_num_rows,
 55                           v_last_analyzed);
 56           END LOOP;
 57
 58           CLOSE rc;
 59        END LOOP;
 60
 61        COMMIT;
 62     END;
 63  END fetch_tables_stats;
 64  /

Procedure created.

测试：

SQL> EXEC fetch_tables_stats;

PL/SQL procedure successfully completed.

SQL>   SELECT *
  2      FROM swms_tab_count_stats_rds
  3  ORDER BY stats_date;
    
OPCO_NAME  TABLE_NAME           ACTUAL_ROWCOUNT STATS_NUM_ROWS STATS_DATE
---------- -------------------- --------------- -------------- -------------------
DBL_MIKE   BONUS                              0              0 17.05.2018 08:57:22
DBL_MIKE   SALGRADE                           5              5 17.05.2018 22:00:10
DBL_MIKE   DUMMY                              1              1 17.05.2018 22:00:10
DBL_MIKE   DEMO_ORDERS                       10             10 06.10.2018 22:01:03
DBL_MIKE   APEX$TEAM_DEV_FILES                0              0 06.10.2018 22:01:03
<snip>