Dynamic Single Row Query
Once in awhile doing analysis, I'd like to see the count from each table, just to get an idea of how much data I am working with. Of course this doesn't measure width, but it is
a metric.
Of course I could go through and write a SELECT COUNT(*) from every table, that works if there are like 4 tables. Anything more and...well it sucks.
Yes, I could gather stats on the schema and then reference NUM_ROWS, but this is an occasion where I don't have the necessary privileges.
I've been trying to do this for years, with no luck, until yesterday. Typically I would do something like this:
DECLARE
l_count INTEGER;
l_sql VARCHAR2(200);
BEGIN
FOR i IN ( SELECT table_name
FROM dba_tables
WHERE owner = 'SYSTEM' )
LOOP
l_sql := 'SELECT COUNT(*) INTO :1 FROM SYSTEM.' || i.table_name;
EXECUTE IMMEDIATE l_sql USING l_count, i.table_name;
dbms_output.put_line( i.table_name || '-' || l_count );
END LOOP;
END;
/
Which would of course would give me this:
DECLARE
*
ERROR at line 1:
ORA-01745: invalid host/bind variable name
ORA-06512: at line 10
Only now did I realize a fatal flaw with that...I was trying to bind the variable into the string, which would obviously never work.
For whatever reason, this time I decided to research it. I came across Flavio Cassetta's site and this post,
SQL Error: ORA-01745: invalid host/bind variable namI'll skip a few iterations and get to the final product (because I went through this exercise on Friday night and quite honestly, don't remember all the permutations).
SET SERVEROUTPUT ON
DECLARE
l_count INTEGER;
l_table VARCHAR2(61);
BEGIN
FOR i IN ( SELECT table_name
FROM dba_tables
WHERE owner = 'SYSTEM' )
LOOP
l_table := 'SYSTEM.' || i.table_name;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || l_table INTO l_count;
dbms_output.put_line( l_table || '-' || l_count );
END LOOP;
END;
/
...snip
SYSTEM.LOGMNR_USER$-0
SYSTEM.LOGMNR_OBJ$-0
SYSTEM.LOGMNR_DICTIONARY$-0
SYSTEM.LOGMNR_DICTSTATE$-0
SYSTEM.OL$NODES-0
SYSTEM.OL$HINTS-0
SYSTEM.OL$-0
PL/SQL procedure successfully completed.
Done.
Labels: oradb, plsql, sql
