Hi,
I try to generate a report which shows the rowcounts of certain tables. As the tables are not analysed frequently enough I need to get this information in two steps:
- Extraction of the relevant table names from SYS.ALL_TABLES: “Select Table_Name from SYS.ALL_TABLES where owner = xxx”
- Executing a “Select count(*)” for each table returned by SQL above.
Doing this in a script which returns the date in the Toad Output-Window is no problem:
Declare
j integer;
k varchar2(256);
sqlstmt varchar2(256);cursor my_tables is select owner, table_name from SYS.ALL_ALL_TABLES where owner=‘xxx’ and table_name like ‘yyy%’;begin
dbms_output.put_line(rpad(‘Table Name’,25)||’ : ‘||‘Record Count’);
for entries in my_tables loop
sqlstmt :=‘select count(*) from ‘||entries.owner||’.’||entries.table_name;
execute immediate sqlstmt into k;
dbms_output.put_line(rpad(entries.table_name,25)||’ : '||k);
end loop;
end;
My question is the following: how do I get the dbms.output into a Toad Report? Is there any solution to this problem? Writing the results of the procedure is unfortunately no option as I do not get the necessary rights.
Thanks for your help!
Bernd