CODEWITHSUNDEEP
sqloracle-databaseplsql
Cornelius
Cornelius

Reputation: 15

Select from table name defined in a loop

I have 2 database connections: a and b where they both share the same schema but not necessarily the same data. I need to check both database's total number of rows for each table using a loop and compare their counts. Using a nested table collection, my attempt so far:

DECLARE  
    TYPE nt_rows IS TABLE OF NUMBER;
    nt_rows1 nt_num := nt_num();
    nt_rows2 nt_num := nt_num();
    counter INT := 0;

    CURSOR c_tables1 IS 
        SELECT TABLE_NAME
        FROM ALL_TABLES
        WHERE OWNER IN ('a')
        ORDER BY TABLE_NAME ASC;

    CURSOR c_tables2 IS
        SELECT TABLE_NAME
        FROM ALL_TABLES
        WHERE OWNER IN ('b')
        ORDER BY TABLE_NAME ASC;

BEGIN block: loop through each cursor and add them to each array 

BEGIN
    FOR i IN c_tables1 LOOP  
        nt_rows1.extend;
        SELECT COUNT(*) FROM a.i.TABLE_NAME INTO nt_rows1(counter);
    END LOOP;

The last line does not work where 'a' is the database connection name and i is index of each table_name. I also tried:

nt_rows1(counter) := SELECT COUNT(*) FROM a.i.TABLE_NAME;

and it also doesn't work, any suggestions are appreciated.

Upvotes: 0

Views: 1563

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64949

If you want to query data from a table whose name is in a string (for example a local variable or a cursor field) and hence not known at compile time, you will need to use dynamic SQL.

Try replacing the line

SELECT COUNT(*) FROM a.i.TABLE_NAME INTO nt_rows1(counter);

with

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM a.' || i.TABLE_NAME INTO nt_rows1(counter);

Note also that your code is not incrementing the counter variable. Since you are starting with counter set to 0, and 0 isn't a valid index into a nested table, I would recommend adding a line to increment counter before the line above.

Upvotes: 1

Related Questions