[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

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 …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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…

656 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