Re: Dynamic SQL

Sure it is. Here's a code snippet that'll get you going:

procedure show_row_count( table_name varchar2) is  cnt number;
begin
 execute immediate 'select count(*) from '||table_name into cnt;  dbms_output.put_line(table_name||' '||cnt); exception
 when others then
   dbms_output.put_line(sqlerrm);
end;

--
Vladimir Zakharychev (bob_at_dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"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

>

>

>