Re: Select Count off tables.

Use dynamic SQL. The code I've included uses htp.p for web output but you can replace it with anything else.

create or replace procedure example_code is cursor curs is
  select distinct table_name from all_tab_columns   where owner = 'SYS'
  order by 1;
  cs number;
  ret number;
  tab_count number;
begin
  cs:=dbms_sql.open_cursor;

  for i in curs loop
    dbms_sql.parse(cs, 'select count(*) from '||i.table_name, DBMS_SQL.NATIVE);
    dbms_sql.define_column(cs, 1, tab_count);

    ret:=dbms_sql.execute(cs);
    ret:=dbms_sql.fetch_rows(cs);

    dbms_sql.column_value(cs, 1, tab_count);     htp.p(tab_count||', '||i.table_name||'<br>');   end loop;

  dbms_sql.close_cursor(cs);

  exception when others then htp.p(sqlerrm); end example_code;
/
show err;

• Jason

Posted via www.orafocus.com - Focusing on the World of Oracle Received on Fri Jul 27 2001 - 01:46:04 CDT