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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.