Home » SQL & PL/SQL » SQL & PL/SQL » Cursored FOR loops, PLS-00103 compile error (12.1.0.2.0)
icon5.gif  Cursored FOR loops, PLS-00103 compile error [message #679747] Sat, 21 March 2020 23:14 Go to next message
thegerman
Messages: 5
Registered: March 2020
Location: Ohio
Junior Member
I can't seem to find an obvious answer having searched google that all this past week, but I would like to know why I get this compiler error:

PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: * & - + / at mod remainder rem .. || multiset year day

I have a bunch of procedures in a package, but one proc for some reason is not agreeing with me. I used the same pattern I normally would, appropriate or not, yet this new proc is throwing errors. Out of a sanity check, I copied it, took all the business logic out of it, reduced it to just a cursor and a for loop, and still the error persists.

I made this example to mask and demonstrate the issue:

CREATE OR REPLACE EDITIONABLE PACKAGE "UTEST"."PKG_TEST_PROCS" IS

    PROCEDURE PRC_CURSOR_LOOP_ONE(vDATE DATE);
    PROCEDURE PRC_CURSOR_LOOP_TWO(vLOOKUPSTRING VARCHAR2);

END PKG_TEST_PROCS;
/

CREATE OR REPLACE EDITIONABLE PACKAGE BODY "UTEST"."PKG_TEST_PROCS" IS

    PROCEDURE PRC_CURSOR_LOOP_ONE(vDATE DATE) AS
        CURSOR c_TESTCURSOR_ONE IS (SELECT A.ID FROM SOMETABLE WHERE DATE_COLUMN = vDATE);
    BEGIN
        FOR i IN (c_TESTCURSOR_ONE)
        LOOP
            PRC_SOMELOGGER('Logging ' || i.ID);
        END LOOP;
    END PRC_CURSOR_LOOP_ONE;    

    PROCEDURE PRC_CURSOR_LOOP_TWO(vLOOKUPSTRING VARCHAR2) AS
        CURSOR c_TESTCURSOR_TWO IS (SELECT A.ID FROM SOMETABLE WHERE VARCHAR2_COLUMN = vLOOKUPSTRING);
    BEGIN
        FOR i IN (c_TESTCURSOR_TWO)
        LOOP
            PRC_SOMELOGGER('Logging ' || i.ID);
        END LOOP;
    END PRC_CURSOR_LOOP_TWO;   

END PKG_TEST_PROCS;
/

I get the PLS-00103 compile error for the 2nd proc, PROCEDURE PRC_CURSOR_LOOP_TWO(vLOOKUPSTRING VARCHAR2), but not the first one.

If, however, I change that proc to use the same SELECT statement in the FOR definition, it compiles no problem. Here is an example where it works:

PROCEDURE PRC_CURSOR_LOOP_TWO(vLOOKUPSTRING VARCHAR2) AS
BEGIN
    FOR i IN (SELECT A.ID FROM SOMETABLE WHERE VARCHAR2_COLUMN = vLOOKUPSTRING)
    LOOP
        PRC_SOMELOGGER('Logging ' || i.ID);
    END LOOP;
END PRC_CURSOR_LOOP_TWO;  
What I would like to know is, why can I define and alias a cursor explicitly and use its alias in the FOR loop for one proc but not the other?

What the flipping flounder is happening. Is there some kind of FOR loop scoping issue rule I don't know about? I would normally just accept that I have to user a different method, but I really want to know, why?
Re: Cursored FOR loops, PLS-00103 compile error [message #679748 is a reply to message #679747] Sun, 22 March 2020 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't get the same thing than you (error on first procedure not the second one):
SQL> CREATE OR REPLACE EDITIONABLE PACKAGE "UTEST"."PKG_TEST_PROCS" IS
  2
  3      PROCEDURE PRC_CURSOR_LOOP_ONE(vDATE DATE);
  4      PROCEDURE PRC_CURSOR_LOOP_TWO(vLOOKUPSTRING VARCHAR2);
  5
  6  END PKG_TEST_PROCS;
  7  /

Package created.

SQL> CREATE OR REPLACE EDITIONABLE PACKAGE BODY "UTEST"."PKG_TEST_PROCS" IS
  2
  3      PROCEDURE PRC_CURSOR_LOOP_ONE(vDATE DATE) AS
  4          CURSOR c_TESTCURSOR_ONE IS (SELECT A.ID FROM SOMETABLE WHERE DATE_COLUMN = vDATE);
  5      BEGIN
  6          FOR i IN (c_TESTCURSOR_ONE)
  7          LOOP
  8              PRC_SOMELOGGER('Logging ' || i.ID);
  9          END LOOP;
 10      END PRC_CURSOR_LOOP_ONE;
 11
 12      PROCEDURE PRC_CURSOR_LOOP_TWO(vLOOKUPSTRING VARCHAR2) AS
 13          CURSOR c_TESTCURSOR_TWO IS (SELECT A.ID FROM SOMETABLE WHERE VARCHAR2_COLUMN = vLOOKUPSTRING);
 14      BEGIN
 15          FOR i IN (c_TESTCURSOR_TWO)
 16          LOOP
 17              PRC_SOMELOGGER('Logging ' || i.ID);
 18          END LOOP;
 19      END PRC_CURSOR_LOOP_TWO;
 20
 21  END PKG_TEST_PROCS;
 22  /

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY "UTEST"."PKG_TEST_PROCS":
LINE/COL ERROR
-------- -------------------------------------------------------------------------------------
7/9      PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
         * & - + / at mod remainder rem .. <an exponent (**)> ||
         multiset year day
9/9      PLS-00103: Encountered the symbol "END" when expecting one of the following:
         begin function pragma procedure subtype type <an identifier>
         <a double-quoted delimited-identifier> current cursor delete
         exists prior
SQL> @v

Oracle version: 12.1.0.2.181016 EE
This is why you must copy and paste your SQL*Plus session and not just tell.

From Database PL/SQL Language Reference, Cursor FOR LOOP Statement, syntax is:
[ FOR record IN
  { cursor [ ( cursor_parameter_dec
               [ [,] cursor_parameter_dec ]... )]
  | ( select_statement )
  }
    LOOP statement... END LOOP [label] ;
So just remove the parentheses around the cursor names and it will work.

[Updated on: Sun, 22 March 2020 02:03]

Report message to a moderator

icon14.gif  Re: Cursored FOR loops, PLS-00103 compile error [message #679751 is a reply to message #679748] Sun, 22 March 2020 18:27 Go to previous message
thegerman
Messages: 5
Registered: March 2020
Location: Ohio
Junior Member
OMFG, Thank you.

I'm going to go take a vacation now... Sad
Previous Topic: Moving sequences from one schema to other with the next value.
Next Topic: Joined two tables on one row
Goto Forum:
  


Current Time: Sun Sep 20 19:21:30 CDT 2020