PLS-00364: loop index variable 'T' use is invalid


Apparently I'm doing something "illegal" here.  How can I use a variable where I'm trying to use v_PRT?

Error is

Error report:
ORA-06550: line 6, column 47:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 6, column 17:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 34:
PLS-00364: loop index variable 'T' use is invalid
ORA-06550: line 8, column 13:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

DECLARE
    v_PRT varchar(30) := 'ALL_INTERNAL_MAIN_TEST_PRT';
    
  BEGIN
      FOR t IN (Select column_nm, src_nm from v_PRT)
      LOOP
        DBMS_OUTPUT.PUT_LINE(t.column_nm||t.src_nm);
      END LOOP;
  END;
  /

Open in new window

donnatroniousAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
you can't build dynamic sql like statements like that

and, you can't use dynamic sql in a cursor for loop.


DECLARE
    v_prt       VARCHAR(30) := 'ALL_INTERNAL_MAIN_TEST_PRT';

    v_cur       SYS_REFCURSOR;
    v_column_nm VARCHAR2(100);
    v_src_nm    VARCHAR2(100);
BEGIN
    OPEN v_cur FOR 'select column_nm, src_nm from ' || v_prt;

    LOOP
        FETCH v_cur
          INTO v_column_nm, v_src_nm;

        EXIT WHEN v_cur%NOTFOUND;

        DBMS_OUTPUT.put_line(v_column_nm || ' ' || v_src_nm);
    END LOOP;
END;
0
 
donnatroniousAuthor Commented:
Can you show me something similar without using a cursor?
0
 
sdstuberCommented:
no, not if you want to iterate through the rows.  You must use a cursor.

Also, if you have dynamic sql - that is, sql generated from a variable table you'll need "something" like the above.

if you're willing to go with static then, what you started with is ok.


Note, this is still using a cursor in the for loop (it's called a "cursor for loop")


BEGIN
      FOR t IN (Select column_nm, src_nm from ALL_INTERNAL_MAIN_TEST_PRT)
      LOOP
        DBMS_OUTPUT.PUT_LINE(t.column_nm||t.src_nm);
      END LOOP;
END;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.