PL/SQL procedure to fetch data from remote tables using DB Link

Question

I am entirely new to PL/SQL programming..

My requirement is to collect table list under a specific schema, its count(*) and num_rows along with last_analyzed date for n number of databases and populate it into a single table. I am trying to access remote databases via DB link. Managed to write below code with the help of internet sources. But not able to get the output. Please help on this..

CREATE OR REPLACE PROCEDURE fetch_tables_stats
AS
BEGIN
  FOR src_link IN (SELECT db_link FROM dba_db_links WHERE USERNAME = '<user>') 
  LOOP
    FOR tbl IN (SELECT table_name FROM dba_tables@src_link.db_link WHERE owner = '<owner>')
    LOOP
      DECLARE
      v_row_count NUMBER;
      v_num_rows NUMBER;
      sql_stmt1 VARCHAR2(1000);
      sql_stmt2 VARCHAR2(1000);
      BEGIN
        sql_stmt1 := 'SELECT COUNT(*) FROM ' || tbl || '.' || table_name || '@' ||src_link || '.' || db_link;
        EXECUTE IMMEDIATE sql_stmt1 INTO v_row_count;
        sql_stmt2 := 'SELECT num_rows FROM dba_tab_statistics@' || src_link || '.' || db_link || ' WHERE table_name = ' || tbl ||'.' || table_name || ' AND owner = ' || q'['<owner>']';  
        EXECUTE IMMEDIATE sql_stmt2 INTO v_num_rows;
        INSERT INTO SWMS_TAB_COUNT_STATS_RDS (OPCO_NAME,TABLE_NAME,ACTUAL_ROWCOUNT,STATS_NUM_ROWS,STATS_DATE)
        VALUES (src_link.db_link,tbl.table_name, v_row_count, v_num_rows,sysdate);
        COMMIT;
      END;
    END LOOP;
  END LOOP; 
END fetch_tables_stats;
/ 

Output:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/17     PL/SQL: ORA-00942: table or view does not exist
6/17     PL/SQL: SQL Statement ignored
14/6     PL/SQL: Statement ignored
14/46    PLS-00364: loop index variable 'TBL' use is invalid
16/3     PL/SQL: Statement ignored
16/16    PLS-00306: wrong number or types of arguments in call to '||'
18/6     PL/SQL: SQL Statement ignored
19/34    PLS-00364: loop index variable 'TBL' use is invalid
19/38    PL/SQL: ORA-00984: column not allowed here
SQL>

Able to create procedure now.. But it is not populating data inside the table..

SQL> create or replace procedure fetch_tables_stats
as
begin
  declare
    rc sys_refcursor;
    l_table_name dba_tables.table_name%type;
    v_row_count number;
        v_num_rows NUMBER;
        v_last_analyzed NUMBER;
        sql_stmt1 VARCHAR2(1000);
        sql_stmt2 VARCHAR2(1000);
        sql_stmt3 VARCHAR2(1000);
    begin
      for src_link in (select db_link from user_db_links)
      loop
        open rc for 'select table_name from dba_tables@' || src_link.db_link || 'where owner = ' || q'['SWMS']';
        loop
          fetch rc into l_table_name;
          exit when rc%notfound;
          sql_stmt1 := 'select count(*) from ' || l_table_name ||'@'|| src_link.db_link;
                  execute immediate sql_stmt1 into v_row_count;
                  sql_stmt2 := 'SELECT num_rows,last_analyzed FROM dba_tables@' || src_link.db_link || ' WHERE table_name = ' || l_table_name || ' AND owner = ' || q'['SWMS']';
                  execute immediate sql_stmt2 into v_num_rows, v_last_analyzed;
                  sql_stmt3 := 'INSERT INTO SWMS_TAB_COUNT_STATS_RDS (OPCO_NAME,TABLE_NAME,ACTUAL_ROWCOUNT,STATS_NUM_ROWS,STATS_DATE)
                  VALUES (' || src_link.db_link || ',' || l_table_name || ', v_row_count, v_num_rows,v_last_analyzed)';
                  execute immediate sql_stmt3;
                  commit;
        end loop;
        close rc;
      end loop;
    end;
end fetch_tables_stats;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33

Procedure created.


SQL> exec fetch_tables_stats;

PL/SQL procedure successfully completed.

SQL> select count(*) from SWMS_TAB_COUNT_STATS_RDS;

  COUNT(*)
----------
         0

Answer 1

As you have to include table name and database link name into query, you'll need dynamic SQL.

Here's an example; I'm using user_... tables as I don't have access to dba_... ones so - you'll have to fix that. Also, I'm just displaying the output to the screen - you'll insert that into a table (fix it as well).

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>

---

[EDIT]

Your procedure has some errors, for example

• missing space in front of WHERE in RC
• missing single quotes that enclose table name in SQL_STMT2
• wrong datatype for V_LAST_ANALYZED variable (should be DATE, not NUMBER)
• good practice says that - when working with dynamic SQL - you should first display statement to be executed (using dbms_output.put_line) and then, when you're sure that it looks OK, actually run it using execute immediate. Without it, you can't be really sure what you're trying to execute
• you don't need dynamic SQL to insert rows into target table; there's nothing dynamic in it
• good practice says that you should avoid committing within the loop

When fixed, it works:

Sample target table:

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.

Procedure (I used all_tables and my own user, mike):

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.

Testing:

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>