Add sequence number to a query.

Kamal Agnihotri
Kamal Agnihotri used Ask the Experts™
on
Experts,

There are 203 Tables in my Schema. The code below gives me the count of number of records in each table.

declare
     v_rowcnt integer;
 begin
     for t in (select table_name from user_tables order by 1) loop
         execute immediate 'select count(*) from ' || t.table_name into v_rowcnt;
         dbms_output.put_line(rpad(t.table_name,40,'.') || v_rowcnt);
     end loop;
 end;
/

--############################################

The first few lines of the output look like this:

ACCOUNT.................................1
AD......................................7
AD_AGE_STATED...........................6
AD_BODY.................................7
AD_CATEGORY.............................1
AD_LOCATION_STATED......................7
AD_LOCATION_WEBSITE.....................7
AD_NAME_ADVERTISED......................1

--#####################

I would like the output to look below.  Add another column to the right (First Column)  to tell the sequence number of the table. How do I modify the script above to get the desired result.

1     ACCOUNT.................................1
2     AD......................................7
3     AD_AGE_STATED...........................6
4     AD_BODY.................................7
5     AD_CATEGORY.............................1
6     AD_LOCATION_STATED......................7
7     AD_LOCATION_WEBSITE.....................7
8     AD_NAME_ADVERTISED......................1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Given that code:
add another integer in the declare.
Add that variable to the dbms_output line.
Increment the variable:

v_row := v_row+1;


You do know you can to it all in a single select, right?

http://www.experts-exchange.com/questions/25111045/How-to-get-total-row-count-from-Oracle-10g.html#a26476860

Then you can use use rownum.

Author

Commented:
Hi Slightwv,

With your guidance, the code below works. Would you written it any differently.
--#######################################
declare
     v_row  integer := 0;
     v_rowcnt integer;
 begin
     for t in (select table_name from user_tables order by 1) loop
         v_row := v_row + 1;
         execute immediate 'select count(*) from ' || t.table_name into v_rowcnt;
         dbms_output.put_line (rpad(v_row, 5, ' ') || (rpad(t.table_name,40,'.') || v_rowcnt));
     end loop;    
 end;    
/
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>Would you written it any differently.

Yes.

I would use the XML trick in the thread I posted.

That is sort of why I posted it........

For ALL tables, remove my where clause.  I just didn't want to return them all for this small test.
drop table tab1 purge;
drop table tab2 purge;
create table tab1(col1 char(1));
create table tab2(col1 char(1));
insert into tab1 values('a');
insert into tab2 values('a');
insert into tab2 values('a');
commit;


SELECT row_number() over(order by table_name) rn, table_name,
       TO_NUMBER(EXTRACTVALUE(
                     xmltype(DBMS_XMLGEN.getxml('select count(*) X from ' || table_name)),
                     '/ROWSET/ROW/X'
                 ))
           COUNT
FROM user_tables
where table_name like 'TAB%';

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial