OraFAQ Forum: SQL & PL/SQL » Cursored FOR loops, PLS-00103 compile error

Home » SQL & PL/SQL » SQL & PL/SQL » Cursored FOR loops, PLS-00103 compile error (12.1.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Cursored FOR loops, PLS-00103 compile error [message #679747]

Sat, 21 March 2020 23:14

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?

Report message to a moderator

Re: Cursored FOR loops, PLS-00103 compile error [message #679748 is a reply to message #679747]

Sun, 22 March 2020 02:00

Michel Cadot
Messages: 68641
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

Re: Cursored FOR loops, PLS-00103 compile error [message #679751 is a reply to message #679748]

Sun, 22 March 2020 18:27

thegerman
Messages: 5
Registered: March 2020
Location: Ohio

Junior Member

OMFG, Thank you.

I'm going to go take a vacation now... Sad

Report message to a moderator

Previous Topic:	Moving sequences from one schema to other with the next value.
Next Topic:	Joined two tables on one row

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 00:12:16 CDT 2024