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?
|
|
|
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: 68757 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
|