Re: Dynamic SQL

You can use to ways:

1. Use EXECUTE IMMEDIATE "select count(*) from '||my_table This is only valid for Oracle 8i and my_table is a variable
2. Use spool in SQL/Plus to write a file with your needs. SPOOL table_rows select 'select table_name, count(*) from '||table_name||';' from user_tables; SPOOL OFF

Rui Brito

"Thomas Stuefer" <stuefer_at_halli-data.at> wrote in message news:5uUW7.8$iq5.87126_at_news.salzburg-online.at...
> With dynamic SQL i wish to output all table-names in my schema with count
of
> rows, for example:
>
> TABLE_NAME COUNT_ROWS
> ------------------------------ ------------------
> BONUS 0
> DEPT 4
> EMP 14
> SALGRADE 5
>
>
> I know i can use "ANALYZE TABLE ... COMPUTE STATISTICS " and then "SELECT
> TABLE_NAME, NUM_ROWS FROM USER_TABLES", but i will use "SELECT COUNT(*)
FROM
> ..." in dynamic SQL with assign to a variable and then output this
variable
> with DBMS_OUTPUT.PUT_LINE. Is this possible ?
>
> Many thans for your help !!!
>
> Best regards,
> Tom
>
>
>
Received on Tue Jan 01 2002 - 23:56:29 CET