Re: Dynamic SQL

In article <5uUW7.8$iq5.87126_at_news.salzburg-online.at>, "Thomas says...
>
>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
>
>
>

sigh, no version. I'll assume software written in the last three years then (Oracle8i and up)

ops$tkyte_at_ORA815.US.ORACLE.COM> create or replace   2 function get_rows( p_tname in varchar2 ) return number   3 AUTHID CURRENT_USER
  4 as
  5 l_columnValue number default NULL;   6 begin

  7      execute immediate
  8         'select count(*)
  9            from ' || p_tname INTO l_columnValue;
 10  
 11      return l_columnValue;

Function created.

ops$tkyte_at_ORA815.US.ORACLE.COM>
ops$tkyte_at_ORA815.US.ORACLE.COM> select user, table_name,   2 get_rows( user||'.'||table_name) cnt   3 from user_tables
  4 /

USER                           TABLE_NAME                            CNT
------------------------------ ------------------------------ ----------
OPS$TKYTE                      DEPT                                    4
OPS$TKYTE                      DRIVE_TAB                               1
OPS$TKYTE                      EMP                                    14
OPS$TKYTE                      EMP2                                    0
OPS$TKYTE                      FK_TAB_DETAIL                           0
OPS$TKYTE                      FK_TAB_MASTER                           1
OPS$TKYTE                      MARKET_SECURITY                         0
OPS$TKYTE                      MYTABLE                                 0
OPS$TKYTE                      T                                      13
OPS$TKYTE                      TMP_ROW_COUNT                           0
OPS$TKYTE                      X                                       0

11 rows selected.

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp