Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: PLS-00364: Loop index variable "SearchResults" use is invalid.

    I am just learning oracle and am trying to create a package. Just to make it simple I created a package that has one sp that returns one variable. I can't get it to compile I am getting the following error:
    PLS-00364: Loop index variable "SearchResults" use is invalid.

    From all of the documentation I have this package looks exactly like it should.

    Code:
    CREATE OR REPLACE PACKAGE TMOSER.CZCS_SEARCH_PKG
    AS 
    TYPE ID IS TABLE OF VARCHAR(64) INDEX BY BINARY_INTEGER;
        PROCEDURE doSearch(o_ID OUT ID);
    END CZCS_SEARCH_PKG;
    
    CREATE OR REPLACE PACKAGE BODY TMOSER.CZCS_SEARCH_PKG
    AS
    PROCEDURE doSearch(o_ID OUT ID)
      IS
        CURSOR searchresults_cur IS
          SELECT ID
            FROM W6ADMIN.W6ENGINEERS
            WHERE POSTCODE='19464';
         recCount NUMBER DEFAULT 0;
      BEGIN
         FOR SearchResults IN searchresults_cur LOOP
           recCount:= recCount + 1;
           o_ID(recCount):= SearchResults.ID;
         END LOOP;
      END doSearch;
    END CZCS_SEARCH_PKG;
    Thanks in advance for your help.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try ... Remember doing a cursor is going to spin thru the
    recordset as defined in the cursor ... 1 row at a time ....

    CREATE OR REPLACE PACKAGE BODY TMOSER.CZCS_SEARCH_PKG
    AS
    PROCEDURE doSearch(o_ID OUT ID)
    IS
    CURSOR searchresults_cur IS
    SELECT ID
    FROM W6ADMIN.W6ENGINEERS
    WHERE POSTCODE='19464';
    recCount NUMBER DEFAULT 0;
    cur_id int;

    BEGIN
    open searchresults_cur;
    fetch searchresults_cur into cur_id;
    loop
    exit when searchresults_cur%notfound;

    ....
    ....
    recCount:= recCount + 1;
    fetch searchresults_cur into cur_id;
    end loop;
    o_ID:= reccount;
    END doSearch;


    Gregg

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    i tried to reproduce your problem, was not able.
    You might try changing the code by declaring a variable called rec_searchresult declared as searchresults_cur%rowtype and change the loop to :
    for rec_searchresult in searchresults_cur loop .......

    Also verify that you have select privileges on the table W6ADMIN.W6ENGINEERS directly.

    The other problem might be in :
    o_ID(recCount):= SearchResults.ID;

    Comment that line out and try again. If that's the problem try changing the names of local variables and / or verify that it is really needed to add the ID value to the PL/SQL table.

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •