Pls-00364: loop index variable 'j' use is invalid

Question

I'm trying to get count of every table in my schema.

SET serveroutput ON;
BEGIN
FOR I IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP
FOR J IN (SELECT COUNT(1) CNT FROM I.TABLE_NAME) LOOP
DBMS_OUTPUT.PUT_LINE(I.TABLE_NAME||'>>'||J.CNT);
END LOOP;
END LOOP;
END;
/

But i'm getting error like this.,

Error report:
ORA-06550: line 3, COLUMN 37:
PL/SQL: ORA-00942: TABLE OR VIEW does NOT exist
ORA-06550: line 3, COLUMN 10:
PL/SQL: SQL Statement ignored
ORA-06550: line 4, COLUMN 42:
PLS-00364: loop INDEX variable 'J' USE IS invalid
ORA-06550: line 4, COLUMN 1:
PL/SQL: Statement ignored

Can it be achieved in sql query itself,.? /

You can't use a variable (and a implicit loop record *is* a variable) as a table name. You need dynamic SQL. — , Dec 30 '15 at 13:49
Possible duplicate of [Is it possible to pass table name as a parameter in Oracle?](http://stackoverflow.com/questions/3690038/is-it-possible-to-pass-table-name-as-a-parameter-in-oracle) — user272735, Dec 31 '15 at 05:12

score 5 · Accepted Answer · answered Dec 30 '15 at 13:51

You need to use dynamic sql to achieve this:

DECLARE 
  val NUMBER;
BEGIN
FOR I IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP
  EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || i.table_name INTO val;
  DBMS_OUTPUT.PUT_LINE(i.table_name || ' ==> ' || val );
END LOOP;
END;
/

Pls-00364: loop index variable 'j' use is invalid

1 Answers1