Solved

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

Posted on 2011-09-04
3
5,110 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
  • 2
3 Comments
 
LVL 73

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 73

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

770 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