Try this script. It'll create a neat CSV so that you can open with Excel.



-- count_rec count items in all tables
set array 10;
set serveroutput on size 1000000;


SPOOL COUNT.CSV

DECLARE
vSql VARCHAR2(300);
vCount NUMBER;
vInto VARCHAR2(1000);
BEGIN
-- SELECT COUNT(*) INTO vCount FROM TAB WHERE TABTYPE='TABLE';
-- dbms_output.put_line('Number of tables: '||vCount);

FOR c IN (SELECT * FROM USER_TABLES )
LOOP
vSql:= 'SELECT COUNT(*) FROM ' || c.table_name ;
EXECUTE IMMEDIATE( vSql ) INTO vCount;

--dbms_output.put_line(rpad(c.tname,30,' ') || ' ' || vCount);
dbms_output.put_line(c.table_name || ',' || vCount);

END LOOP;

END;


/
SPOOL OFF;


Ning Wong


-----Original Message-----
From: Linda Shen
[mailto:oracledba-ezmlmshield-x47370268.[Email address protected]
Sent: Wednesday, May 10, 2006 5:36 PM
To: LazyDBA Discussion
Subject: get count(*) from execute imm


I need to get count(*) from a list of tables. I used the following statement in a procedure.

v_stmt := 'select count(*) from '||v_tablename||' where curr_flag=''Y'' returning count(*) into :a';
execute immediate v_stmt using out v_cnt_fdb;

But, I got an error. ORA-00933: SQL command not properly ended

Could anyone help? Thanks in advance.

Linda


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page