Hi,
I want to get the count(*) from all tables under a schema. How to can do this using a loop?
I'm a noob at PL/SQL.
I'm unable to find how to use the loop variable in the select statement.
Here is what I've tried so far:
spool $DD/get_oid_dump.log;
SET SERVEROUTPUT ON
DECLARE
dummy sys.dbms_debug_vc2coll; -- results (unused)
errm VARCHAR2(10000);
my_sql VARCHAR2(1000);
my_count VARCHAR(100);
BEGIN
FOR t IN (SELECT t.table_name, t.owner FROM all_tables t where owner = 'PM_CMP_MODEL')
LOOP
my_sql := 'select count(*) from ' || t.owner || '.' || t.table_name || ';' ;
dbms_output.put_line ('executing: ' || my_sql);
DBMS_DEBUG.EXECUTE (my_sql, -1, 0, dummy, errm);
dbms_output.put_line (errm);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR!! -- ' || SQLCODE || '--' || sqlerrm || '--' || errm || '--');
END;
/
Output is:
[root]# sqlplus -S system/manager @$DD/get_oid_dump.sql
executing: select count(*) from PM_CMP_MODEL.FOLDER_REPORT_LINK
ERROR!! -- 1--User-Defined Exception----
PL/SQL procedure successfully completed.
[root@helsinki kash]#
If I change line # 11 thru 13 with " select count(*) from || t.owner || '.' || t.table_name;
" I get following error:
select count(*) from || t.owner || '.' || t.table_name;
*
ERROR at line 9:
ORA-06550: line 9, column 24:
PL/SQL: ORA-00903: invalid table name
ORA-06550: line 9, column 3:
PL/SQL: SQL Statement ignored
Should I be using some other function than DBMS_DEBUG.EXECUTE()? I checked the list of functions and I could only find this one to be fitting my usecase.
Thanks.