1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

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

Discussion in 'SQL PL/SQL' started by Vicky, Dec 29, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi frndz,

    I'm trying to get count of every table in my schema.
    Code (SQL):

    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.,

    Code (SQL):

    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
     
    Whether, i need to use execute immediate?
    Or it could be achieved in sql query itself,.?
    /
     
  2. nikhiljuneja

    nikhiljuneja Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Delhi
    you can't variable as a table name, you need to use execute immediate for this...