SQL-To-SQL

Hi experts,

Usind sql-to-sql or Dynamic Sql, I would like to get the count of number of rows in ALL tables in a Schema. There is a two step process shown below which works

Step 1) spool runme.sql

select 'select count(*) from '||table_name||';' from dba_tables where owner = 'XXX';

spool off

step 2) @runme

Using Sql-to-sql, I should be able to do the same in one step.

Some thing like this.
Begin
     For X In (select 'select count(*) from '||table_name||';' sql_string
        from dba_tables where owner = 'AAA')
    Loop
        execute immediate X.sql_string;
    End Loop;
End;

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

When I execute the code above, I get error below.  


Begin
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 5


SQL>

Your comments.

Thanks a lot.


/
Kamal AgnihotriAsked:
Who is Participating?
 
sdstuberCommented:
While I wrote the original one-sql answer referenced above, it can be a little more expensive.

If you want to do it by pl/sql looping then try something like this

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


you can add instrumentation to the looping to indicate where it is in processing and handle exceptions but I wouldn't expect any

also, both answers are based on USER_TABLES, you might want to use ALL_TABLES or DBA_TABLES depending on your privileges and what you really want to read
0
 
slightwv (䄆 Netminder) Commented:
You can do it with a single SQL statement.

Check out this previously asked question:
http://www.experts-exchange.com/Database/Oracle/Q_25111045.html#a26476860
0
 
Kamal AgnihotriAuthor Commented:
Hi sdstuber.

Thanks a lot. that worked. I will close the question.
0
 
Geert GOracle dbaCommented:
is it for an estimate or does it have to be accurate ?

for all tables analyzed this output will be very fast, but not accurate.
for tables not analyzed the num_rows will be 0
<code>select table_name, num_rows from user_tables  </code>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.