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

Question

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?

You can take set serveroutput on size 100000; before DECLARE, then convert that line starting with execute immediate to execute immediate 'select count(*) from ' || rec.table_name into ctr; — Barbaros Özhan
– Barbaros Özhan, Commented Jun 1, 2022 at 17:31
Don't forget to put a / on a separate line after the END; — kfinity
– kfinity, Commented Jun 1, 2022 at 17:54

Alex Poole · Accepted Answer · 2022-06-01 18:17:14Z

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.

Justin Cave · Accepted Answer · 2022-06-01 18:00:24Z

2

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;

answered Jun 1, 2022 at 18:00

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related