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: plsql, sql

  ¶ 1/10/2010 09:36:00 PM