Solved

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

Posted on 2011-09-04
3
5,508 Views
Last Modified: 2013-12-07

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

0
Comment
Question by:donnatronious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36482165
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
 

Author Comment

by:donnatronious
ID: 36482189
Can you show me something similar without using a cursor?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36482208
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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question