I have a table "TEST_TABLE" with two columns TABLE_NAME and RECORD_COUNT.

enter image description here

We want to update the column RECORD_COUNT by taking the total records in table specified in TABLE_NAME.

CodePudding user response：

You could do this with dynamic SQL but why be complicated?
Create a view

Create view my_tables as
Select 
  'table_1' as "table name",
  count(*)  as "rows"
From table_1

Add the following for each table

  Union all
Select 'table_2',count(*) From table_2

You can then use the view like a table:

Select * from my_tables;

CodePudding user response：

OK, but - why wouldn't you use information Oracle already provides for you? You should regularly gather statistics anyway, so:

SQL> execute dbms_stats.gather_schema_stats(ownname => 'SCOTT', estimate_percent  => null);

PL/SQL procedure successfully completed.

and then fetch num_rows from user_tables:

SQL> select table_name, num_rows from user_tables where rownum <= 10;

TABLE_NAME             NUM_ROWS
-------------------- ----------
EMP                          14
DEPT                          4
BONUS                         0
SALGRADE                      5
DUMMY                         1
TBL_ERROR                     1
AUDIT_TAB                     2
SOURCE_DET                    3
EXAMPLE                       1
FLIGHT                        0

10 rows selected.

SQL>

It can't be much worse than your attempt (due to possible frequent changes to tables' contents; inserts and deletes) because you'd collect your own data periodically (how often?) anyway.

If it has to be your way, then you'd use dynamic SQL, looping through all tables in your schema and actually count number of rows:

SQL> create table test_table
  2    (table_name varchar2(30),
  3     num_rows   number);

Table created.

SQL> create or replace procedure p_test as
  2    l_cnt number;
  3  begin
  4    execute immediate 'truncate table test_table';
  5    for cur_R in (select table_name from user_tables) loop
  6      execute immediate 'select count(*) from ' ||cur_R.table_name into l_Cnt;
  7      insert into test_table (table_name, num_rows) values (cur_r.table_name, l_cnt);
  8    end loop;
  9  end;
 10  /

Procedure created.

Running the procedure and viewing the result:

SQL> exec p_test;

PL/SQL procedure successfully completed.

SQL> select * From test_Table where rownum <= 10;

TABLE_NAME             NUM_ROWS
-------------------- ----------
EMP                          14
DEPT                          4
BONUS                         0
SALGRADE                      5
DUMMY                         1
TBL_ERROR                     1
AUDIT_TAB                     2
SOURCE_DET                    3
EXAMPLE                       1
FLIGHT                        0

10 rows selected.

SQL>

Note that performance will suffer as number of tables and number of rows stored in them grows.

If I were you, I'd go for the first option.