Display count of several tables in an Oracle database via SQL*Plus-CodePudding

I'm attempting to display the count of all tables of a similar name in an Oracle database. The query will run via SQL*Plus at the command line. I've written the following code:

DECLARE
   ctr number;
   cursor to_check is
     select table_name from user_tables
       where table_name like '%REF%' or table_name like '%CNF%'
       order by table_name;
    
 BEGIN
 set serveroutput on size 100000;
   for rec in to_check loop
     execute immediate 'select count(*) into :ctr from ' || rec.table_name;
     dbms_output.put_line(rec.table_name || '     ' || ctr);
   end loop;
 END;

However, I'm not receiving the output of this code. Is there an issue with the dbms_output? Or am I missing a more elegant way of pulling and displaying this information?

CodePudding user response：

am I missing a more elegant way of pulling and displaying this information?

That's a bit subjective, but...

You can avoid PL/SQL (and needing the client to display the output) with an XML trick. You can use dbms_xmlgen to generate an XML document containing the information you want:

select dbms_xmlgen.getxmltype(
  'select ''' || table_name || ''' as table_name, count(*) as row_count'
    || ' from "' || table_name || '"')
from user_tab_columns
where table_name like '%REF%' or table_name like '%CNF%';

And then use that as a CTE or inline view and extract the values using XMLTable:

select x.table_name, x.row_count
from (
  select dbms_xmlgen.getxmltype(
    'select ''' || table_name || ''' as table_name, count(*) as row_count'
      || ' from "' || table_name || '"') as xml
  from user_tab_columns
  where table_name like '%REF%' or table_name like '%CNF%'
) t
cross apply xmltable(
  '/ROWSET/ROW'
  passing t.xml
  columns table_name varchar2(30) path 'TABLE_NAME',
    row_count number path 'ROW_COUNT'
) x
order by x.table_name;

db<>fiddle with a couple of dummy tables.

CodePudding user response：

set serveroutput on is a SQL*Plus command. It cannot appear inside a PL/SQL block. it has to be a separate command (and is only meaningful in the SQL*Plus client or a client that implements a subset of SQL*Plus's set commands). That would need to be done before your PL/SQL block executes.

The syntax for execute immediate would be

execute immediate 'select count(*) from ' || rec.table_name
   into ctr;