OraFAQ Forum: SQL & PL/SQL » PLS-00103: Encountered the symbol "END" when expecting one of

Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00103: Encountered the symbol "END" when expecting one of ( Oracle Database 11g XE 11.2.0.2.0 PL/SQL Release 11.2.0.2.0 OS WIN 7)

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

PLS-00103: Encountered the symbol "END" when expecting one of [message #630341]

Wed, 24 December 2014 19:18

weather3
Messages: 9
Registered: March 2014

Junior Member

I am coding in SQL Developer 4.0.3.16

I am trying to comply will all of your rules. It seems some of them are here and some are there. I may have not been to every place they are posted.

I have been all over the web and have not found an answer to this. I can't compile this procedure because I get this error. Any help on getting this code compiled would enable me to continue developing this procedure. Note that I am unable to compile this code. My error is occurring before I access any data in the tables.

Thank you

ERROR: Error(87,13): PLS-00103: Encountered the symbol "END" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset The symbol ";" was substituted for "END" to continue.

Code: [Select all] [Show/ hide]

CREATE OR REPLACE PROCEDURE ADD_USER_GROUPS_PROC AS 
    
    V_ ACTIVE_FLAG := WWTT_INPUT.ACTIVE_FLAG%TYPE;
    V_ BEGIN_DATE := WWTT_INPUT.BEGIN_DATE%TYPE;
    V_ END_DATE := WWTT_INPUT.END_DATE%TYPE;
    V_ GROUP_NAME := WWTT_INPUT.GROUP_NAME%TYPE;
    V_ USER_ID := WWTT_INPUT.USER_ID%TYPE;
    V_ USER_TYPE := WWTT_INPUT.USER_TYPE%TYPE;
    V_ ACCESS_LEVEL := WWTT_INPUT.ACCESS_LEVEL%TYPE;
    V_ COUNTRY := WWTT_INPUT.COUNTRY%TYPE;
    V_ FIRST_NAME := WWTT_INPUT.FIRST_NAME%TYPE;
    V_ GEOGRAPHY := WWTT_INPUT.GEOGRAPHY%TYPE;
    V_ LAST_NAME := WWTT_INPUT.LAST_NAME%TYPE;
    V_ USER_EMAIL := WWTT_INPUT.USER_EMAIL%TYPE;
    USER_INDEX NUMBER;
    
     CURSOR WWTT_INPUT_CUR IS 
       SELECT * FROM SYSTEM.WWTT_INPUT
       WHERE USER_ID LIKE 'A%';
       
     CURSOR WWTT_USERS_CUR IS
       SELECT * FROM SYSTEM.WWTT_USERS;
       
     CURSOR WWTT_GROUPS_CUR IS
       SELECT * FROM SYSTEM.WWTT_GROUPS;

  BEGIN

--    SELECT USER_ID INTO V_ASDF FROM WEATHER.WWTT_USER_GROUPS WHERE USER_id <> ' ';
--    DBMS_OUTPUT.PUT_LINE('V_ASDF: ' || V_ASDF); 

    OPEN WWTT_INPUT_CUR; 
    OPEN WWTT_USERS_CUR;
    OPEN WWTT_GROUPS_CUR;
    FETCH WWTT_INPUT_CUR INTO V_WWTT_INPUT;
    FETCH WWTT_USERS_CUR INTO V_WWTT_USERS;
    FETCH WWTT_GROUPS_CUR INTO V_WWTT_GROUPS;
    
    

            INSERT INTO SYSTEM.WWTT_GROUPS
            (GROUP_NAME,BEGIN_DATE,ACTIVE_FLAG,END_DATE) 
            VALUES 
            (V_WWTT_INPUT.GROUP_NAME,SYSDATE,'Y',NULL);  
           
            --<<outerloop>>
            FOR USER_INDEX IN WWTT_INPUT_CUR LOOP
              --IF WWTT_USERS_CUR.USER_ID =  
          
            END LOOP; -- outerloop; --FOR USER_INDEX IN WWTT_INPUT_CUR   --  ***** LINE 87 *****
            

END ADD_USER_GROUPS_PROC;

ERROR: Error(87,13): PLS-00103: Encountered the symbol "END" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset The symbol ";" was substituted for "END" to continue.

*BlackSwan corrected the {code} tags. read http://www.orafaq.com/forum/t/174502/ to learn how to use them correctly

[Updated on: Wed, 24 December 2014 19:32] by Moderator

Report message to a moderator

Re: PLS-00103: Encountered the symbol "END" when expecting one of [message #630342 is a reply to message #630341]

Wed, 24 December 2014 19:21

weather3
Messages: 9
Registered: March 2014

Junior Member

I realize you are not going to like the way my code looks, but I formatted it with the tool you suggested. When I pasted it in the window it was formated. I submitted and it looks like this. Do you have another formatter? I don't know if formatting it by hand would be undone when I posted it.

Report message to a moderator

Re: PLS-00103: Encountered the symbol "END" when expecting one of [message #630343 is a reply to message #630342]

Wed, 24 December 2014 19:22

weather3
Messages: 9
Registered: March 2014

Junior Member

This code below is properly formatted. I will post again.

CREATE OR REPLACE PROCEDURE ADD_USER_GROUPS_PROC AS

V_ ACTIVE_FLAG := WWTT_INPUT.ACTIVE_FLAG%TYPE;
V_ BEGIN_DATE := WWTT_INPUT.BEGIN_DATE%TYPE;
V_ END_DATE := WWTT_INPUT.END_DATE%TYPE;
V_ GROUP_NAME := WWTT_INPUT.GROUP_NAME%TYPE;
V_ USER_ID := WWTT_INPUT.USER_ID%TYPE;
V_ USER_TYPE := WWTT_INPUT.USER_TYPE%TYPE;
V_ ACCESS_LEVEL := WWTT_INPUT.ACCESS_LEVEL%TYPE;
V_ COUNTRY := WWTT_INPUT.COUNTRY%TYPE;
V_ FIRST_NAME := WWTT_INPUT.FIRST_NAME%TYPE;
V_ GEOGRAPHY := WWTT_INPUT.GEOGRAPHY%TYPE;
V_ LAST_NAME := WWTT_INPUT.LAST_NAME%TYPE;
V_ USER_EMAIL := WWTT_INPUT.USER_EMAIL%TYPE;
USER_INDEX NUMBER;

CURSOR WWTT_INPUT_CUR IS
SELECT * FROM SYSTEM.WWTT_INPUT
WHERE USER_ID LIKE 'A%';

CURSOR WWTT_USERS_CUR IS
SELECT * FROM SYSTEM.WWTT_USERS;

CURSOR WWTT_GROUPS_CUR IS
SELECT * FROM SYSTEM.WWTT_GROUPS;

BEGIN

-- SELECT USER_ID INTO V_ASDF FROM WEATHER.WWTT_USER_GROUPS WHERE USER_id <> ' ';
-- DBMS_OUTPUT.PUT_LINE('V_ASDF: ' || V_ASDF);

OPEN WWTT_INPUT_CUR;
OPEN WWTT_USERS_CUR;
OPEN WWTT_GROUPS_CUR;
FETCH WWTT_INPUT_CUR INTO V_WWTT_INPUT;
FETCH WWTT_USERS_CUR INTO V_WWTT_USERS;
FETCH WWTT_GROUPS_CUR INTO V_WWTT_GROUPS;

INSERT INTO SYSTEM.WWTT_GROUPS
(GROUP_NAME,BEGIN_DATE,ACTIVE_FLAG,END_DATE)
VALUES
(V_WWTT_INPUT.GROUP_NAME,SYSDATE,'Y',NULL);

--<<outerloop>>
FOR USER_INDEX IN WWTT_INPUT_CUR LOOP
--IF WWTT_USERS_CUR.USER_ID =

END LOOP; -- outerloop; --FOR USER_INDEX IN WWTT_INPUT_CUR

END ADD_USER_GROUPS_PROC;

Report message to a moderator

Re: PLS-00103: Encountered the symbol "END" when expecting one of [message #630344 is a reply to message #630343]

Wed, 24 December 2014 19:28

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Your PL/SQL block is not syntactically correct, below may be the one you wanted.

Code: [Select all] [Show/ hide]

CREATE OR REPLACE PROCEDURE ADD_USER_GROUPS_PROC
AS
   V_ACTIVE_FLAG    WWTT_INPUT.ACTIVE_FLAG%TYPE;
   V_BEGIN_DATE     WWTT_INPUT.BEGIN_DATE%TYPE;
   V_END_DATE       WWTT_INPUT.END_DATE%TYPE;
   V_GROUP_NAME     WWTT_INPUT.GROUP_NAME%TYPE;
   V_USER_ID        WWTT_INPUT.USER_ID%TYPE;
   V_USER_TYPE      WWTT_INPUT.USER_TYPE%TYPE;
   V_ACCESS_LEVEL   WWTT_INPUT.ACCESS_LEVEL%TYPE;
   V_COUNTRY        WWTT_INPUT.COUNTRY%TYPE;
   V_FIRST_NAME     WWTT_INPUT.FIRST_NAME%TYPE;
   V_GEOGRAPHY      WWTT_INPUT.GEOGRAPHY%TYPE;
   V_LAST_NAME      WWTT_INPUT.LAST_NAME%TYPE;
   V_USER_EMAIL     WWTT_INPUT.USER_EMAIL%TYPE;
   USER_INDEX       NUMBER;

   CURSOR WWTT_INPUT_CUR
   IS
      SELECT *
        FROM SYSTEM.WWTT_INPUT
       WHERE USER_ID LIKE 'A%';

   CURSOR WWTT_USERS_CUR
   IS
      SELECT * FROM SYSTEM.WWTT_USERS;

   CURSOR WWTT_GROUPS_CUR
   IS
      SELECT * FROM SYSTEM.WWTT_GROUPS;
BEGIN
   -- SELECT USER_ID INTO V_ASDF FROM WEATHER.WWTT_USER_GROUPS WHERE USER_id <> ' ';
   -- DBMS_OUTPUT.PUT_LINE('V_ASDF: ' || V_ASDF);

   OPEN WWTT_INPUT_CUR;

   OPEN WWTT_USERS_CUR;

   OPEN WWTT_GROUPS_CUR;

   FETCH WWTT_INPUT_CUR INTO V_WWTT_INPUT;

   FETCH WWTT_USERS_CUR INTO V_WWTT_USERS;

   FETCH WWTT_GROUPS_CUR INTO V_WWTT_GROUPS;



   INSERT INTO SYSTEM.WWTT_GROUPS (GROUP_NAME,
                                   BEGIN_DATE,
                                   ACTIVE_FLAG,
                                   END_DATE)
        VALUES (V_WWTT_INPUT.GROUP_NAME,
                SYSDATE,
                'Y',
                NULL);

   --<<outerloop>>
   FOR USER_INDEX IN WWTT_INPUT_CUR
   LOOP
      --IF WWTT_USERS_CUR.USER_ID =
      NULL;
   END LOOP; -- outerloop; --FOR USER_INDEX IN WWTT_INPUT_CUR -- ***** LINE 87 *****
END ADD_USER_GROUPS_PROC;

But why opening 3 cursors all together, what you are doing inside LOOP?

Manu

Report message to a moderator

Re: PLS-00103: Encountered the symbol "END" when expecting one of [message #630345 is a reply to message #630344]

Wed, 24 December 2014 19:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

correct syntax is such that every END LOOP must be match with a starting LOOP command; which does not exist in your code

http://docs.oracle.com/database/121/LNPLS/controlstatements.htm#LNPLS004

"LOOP statements can be labeled, and LOOP statements can be nested. Labels are recommended for nested loops to improve readability. You must ensure that the label in the END LOOP statement matches the label at the beginning of the same loop statement (the compiler does not check)."

what datatype is "V_WWTT_INPUT" & where is it defined?

[Updated on: Wed, 24 December 2014 20:13]

Report message to a moderator

Re: PLS-00103: Encountered the symbol "END" when expecting one of [message #630346 is a reply to message #630343]

Wed, 24 December 2014 20:40

weather3
Messages: 9
Registered: March 2014

Junior Member

I received this in an email:
(
Subject: Re: PLS-00103: Encountered the symbol "END" when expecting one of Author: BlackSwan Date: Wed, 24 December 2014 17:36
correct syntax is such that every END LOOP must be match with a starting LOOP command; which does not exist in your code

Below is the code that I originally posted. I don't see how this does not contain a starting LOOP (the whole line is: FOR USER_INDEX IN WWTT_INPUT_CUR LOOP) command that is to go with the END LOOP. By what you say, my code should compile. Please explain how I am wrong.

--<<outerloop>>
FOR USER_INDEX IN WWTT_INPUT_CUR LOOP
--IF WWTT_USERS_CUR.USER_ID =

END LOOP; -- outerloop; --FOR USER_INDEX IN WWTT_INPUT_CUR -- ***** LINE 87 *****

Report message to a moderator

Re: PLS-00103: Encountered the symbol "END" when expecting one of [message #630347 is a reply to message #630344]

Wed, 24 December 2014 20:44

weather3
Messages: 9
Registered: March 2014

Junior Member

The three cursors Fetch data from three different tables. That data will be inserted into different table inside the table. That is the job of this procedure.

I didn't see the difference between your syntax and mine. Could you point out the difference?

Thanks for your comments.

Report message to a moderator

Re: PLS-00103: Encountered the symbol "END" when expecting one of [message #630348 is a reply to message #630346]

Wed, 24 December 2014 20:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Please explain how I am wrong.

an executable statement is required inside the LOOP; then you will see all the other errors inside this procedure.

Report message to a moderator

Re: PLS-00103: Encountered the symbol "END" when expecting one of [message #630349 is a reply to message #630345]

Wed, 24 December 2014 20:51

weather3
Messages: 9
Registered: March 2014

Junior Member

OK, I see this now. I didn't see it in the email. I will check into the labels on the loop.

Hmmm. I reply to this and I can no longer see the message I am replying to. Be patient, I will get used to the site.

Report message to a moderator

Re: PLS-00103: Encountered the symbol "END" when expecting one of [message #630350 is a reply to message #630349]

Wed, 24 December 2014 21:05

weather3
Messages: 9
Registered: March 2014

Junior Member

Sorry for going back and forth.

The code I originally posted had the labels commented out, so they would not have any effect on the code. So I am back to not being able to see why my code did not compile.

Report message to a moderator

Re: PLS-00103: Encountered the symbol "END" when expecting one of [message #630351 is a reply to message #630350]

Wed, 24 December 2014 21:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Code: [Select all] [Show/ hide]

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PROCEDURE ADD_USER_GROUPS_PROC AS
  2      V_ ACTIVE_FLAG := WWTT_INPUT.ACTIVE_FLAG%TYPE;
  3      V_ BEGIN_DATE := WWTT_INPUT.BEGIN_DATE%TYPE;
  4      V_ END_DATE := WWTT_INPUT.END_DATE%TYPE;
  5      V_ GROUP_NAME := WWTT_INPUT.GROUP_NAME%TYPE;
  6      V_ USER_ID := WWTT_INPUT.USER_ID%TYPE;
  7      V_ USER_TYPE := WWTT_INPUT.USER_TYPE%TYPE;
  8      V_ ACCESS_LEVEL := WWTT_INPUT.ACCESS_LEVEL%TYPE;
  9      V_ COUNTRY := WWTT_INPUT.COUNTRY%TYPE;
 10      V_ FIRST_NAME := WWTT_INPUT.FIRST_NAME%TYPE;
 11      V_ GEOGRAPHY := WWTT_INPUT.GEOGRAPHY%TYPE;
 12      V_ LAST_NAME := WWTT_INPUT.LAST_NAME%TYPE;
 13      V_ USER_EMAIL := WWTT_INPUT.USER_EMAIL%TYPE;
 14      USER_INDEX NUMBER;
 15       CURSOR WWTT_INPUT_CUR IS
 16         SELECT * FROM SYSTEM.WWTT_INPUT
 17         WHERE USER_ID LIKE 'A%';
 18       CURSOR WWTT_USERS_CUR IS
 19         SELECT * FROM SYSTEM.WWTT_USERS;
 20       CURSOR WWTT_GROUPS_CUR IS
 21         SELECT * FROM SYSTEM.WWTT_GROUPS;
 22    BEGIN
 23  --    SELECT USER_ID INTO V_ASDF FROM WEATHER.WWTT_USER_GROUPS WHERE USER_id <> ' ';
 24  --    DBMS_OUTPUT.PUT_LINE('V_ASDF: ' || V_ASDF);
 25      OPEN WWTT_INPUT_CUR;
 26      OPEN WWTT_USERS_CUR;
 27      OPEN WWTT_GROUPS_CUR;
 28      FETCH WWTT_INPUT_CUR INTO V_WWTT_INPUT;
 29      FETCH WWTT_USERS_CUR INTO V_WWTT_USERS;
 30      FETCH WWTT_GROUPS_CUR INTO V_WWTT_GROUPS;
 31              INSERT INTO SYSTEM.WWTT_GROUPS
 32              (GROUP_NAME,BEGIN_DATE,ACTIVE_FLAG,END_DATE)
 33              VALUES
 34              (V_WWTT_INPUT.GROUP_NAME,SYSDATE,'Y',NULL);
 35              --<<outerloop>>
 36              FOR USER_INDEX IN WWTT_INPUT_CUR LOOP
 37                --IF WWTT_USERS_CUR.USER_ID =
 38                 V_USER_EMAIL := NULL;
 39              END LOOP; -- outerloop; --FOR USER_INDEX IN WWTT_INPUT_CUR   --  ***** LINE 87 *****
 40* END ADD_USER_GROUPS_PROC;
SQL> /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE ADD_USER_GROUPS_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/8      PL/SQL: Item ignored
2/8      PLS-00201: identifier 'ACTIVE_FLAG' must be declared
3/8      PL/SQL: Item ignored
3/8      PLS-00201: identifier 'BEGIN_DATE' must be declared
4/8      PL/SQL: Item ignored
4/8      PLS-00201: identifier 'END_DATE' must be declared
5/8      PL/SQL: Item ignored
5/8      PLS-00201: identifier 'GROUP_NAME' must be declared
6/8      PL/SQL: Item ignored
6/8      PLS-00201: identifier 'USER_ID' must be declared
7/8      PL/SQL: Item ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/8      PLS-00201: identifier 'USER_TYPE' must be declared
8/8      PL/SQL: Item ignored
8/8      PLS-00201: identifier 'ACCESS_LEVEL' must be declared
9/8      PL/SQL: Item ignored
9/8      PLS-00201: identifier 'COUNTRY' must be declared
10/8     PL/SQL: Item ignored
10/8     PLS-00201: identifier 'FIRST_NAME' must be declared
11/8     PL/SQL: Item ignored
11/8     PLS-00201: identifier 'GEOGRAPHY' must be declared
SQL>

Report message to a moderator

Re: PLS-00103: Encountered the symbol "END" when expecting one of [message #630359 is a reply to message #630348]

Thu, 25 December 2014 05:21

weather3
Messages: 9
Registered: March 2014

Junior Member

I spent hours searching the web for the cause of this error, looking at documentation on LOOPS as well as documentation on this error. Yours is the first reference to needing an executable inside the loop to enable it to compile.

The good thing about this, and it has happened many times before over the years, is that I will never forget this almost undocumented requirement. Thank you for your help and have a great Christmas.

weather3

Report message to a moderator

Re: PLS-00103: Encountered the symbol "END" when expecting one of [message #630360 is a reply to message #630359]

Thu, 25 December 2014 05:27

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

PL/SQL Language Reference
Chapter 4 PL/SQL Control Statements
Section LOOP Statements
Subsection Basic LOOP Statement

Quote:

The basic LOOP statement has this structure:
Code: [Select all] [Show/ hide]
[ label ] LOOP statements END LOOP [ label ];
With each iteration of the loop, the statements run and control returns to the top of the loop.

"statements" is NOT between [], so it is a mandatory part (just like "LOOP" "END LOOP" and the semi-colon).

Report message to a moderator

Previous Topic:	ASCII in oracle 11g
Next Topic:	Comparing two TimeStamp columns giving unexpected result

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 27 17:32:16 CDT 2025