LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: the1don

I have an anonymous block I run in Oracle to get the number of records in every table in a schema to get output like this. I have tried a DB Query node, DB Execute node. I have run the top query and passed that into a filter node and created 3 commands (ex Select COUNT(*) from DON>TABLE1 , etc). That works if I only have one row. Fails on 2 or more rows.

Any ideas would be appreciated.


Output
DON.TABLE1 977
DON.TABLE2 3
DON.TABLE3 256658



DECLARE
match_count integer;

BEGIN
FOR t IN (SELECT owner, table_name
FROM all_tab_columns
WHERE owner = 'DON'
GROUP BY owner, table_name
)
LOOP
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name
INTO match_count;
dbms_output.put_line( t.owner || '.' || t.table_name ||' '||match_count );

END;
END LOOP;
END;

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gmullin

You won't be able to execute 3 different commands in the same node and have them on the same output pin. If you're using DB Execute you could put your 3 commands in 3 fields going into the node and output each one on a different pin. Your DB Execute would have 3 output pins and would look like this:

sqlSelect(1,query1)
sqlSelect(2,query2)
sqlSelect(3,query3)

Another option could be to use a Do While node and write out the output for each iteration. This will probably work better if you want to count all the tables and you don't have a set number of commands. Above I'm presuming you know you're 3 commands for 3 tables.