?
Solved

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

Posted on 2011-09-04
3
Medium Priority
?
5,716 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

752 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