PLS Error: 00103 Encountered the symbol “EXCEPTION”

Question

So, I've created the following procedure:

--First Stored Procedure--
CREATE OR REPLACE PROCEDURE sp_GetMileageBalance (StartMileage IN NUMBER,EndMileage IN NUMBER, ExpDate IN DATE)
IS  --Code declaration section--
    --variables to store column values returned from select into
     fPassengerID        VARCHAR2(10);
     pFirst              VARCHAR2(20);
     pLast               VARCHAR2(20);
     fFreqflyernum       NUMBER (10);
     fFreqflyerMileage   NUMBER (7);
     fMileagebalance     NUMBER (7);
     fMileageExpDate     DATE;
     MileageExpDate      DATE;
     MileageStart        NUMBER (7);
     MileageEnd          NUMBER (7);
     MileageBalance      NUMBER (7);

     --Declare Cursor
              CURSOR cur_FreqFlyer IS
     --Query cursor will point to results         
SELECT F.FPASSENGERID, P.First, P.Last, F.FREQFLYERNUM, F.FREQFLYERMILEAGE, F.MILEAGEBALANCE, F.MILEAGEEXPDATE
INTO  fPassengerID, pFirst,pLast,fFreqflyernum,fFreqflyerMileage,fMileagebalance,fMileageExpDate
FROM FREQUENT_FLYER F
INNER JOIN PASSENGER P
ON F.FPassengerID = P.PassengerID
WHERE F.MileageBalance >= StartMileage AND F.MILEAGEBALANCE <= EndMileage
AND MileageExpDate > ExpDate;


    --Start Execution section--
BEGIN 
     --Open Cursor
     OPEN cur_FreqFlyer; --  open cursor for use   
     --loop to display each record returned by cursor 
     --Use PL/SQL language control or loop to display each record pointed by cursor 
   LOOP
        --Fetch cursor data
        FETCH cur_FreqFlyer INTO fPassengerID,pFirst,pLast,fFreqflyernum,fFreqflyerMileage,
        fMileageBalance,fMileageExpDate;
        EXIT WHEN cur_FreqFlyer%NOTFOUND;
        --Display each record
        --Displaying the results


DBMS_OUTPUT.PUT_LINE ('CUSTOMER INFORMATION: ');
DBMS_OUTPUT.PUT_LINE (' ');
DBMS_OUTPUT.PUT_LINE ('The Frequent Flyer PassengerID is:  ' ||fPassengerID);
DBMS_OUTPUT.PUT_LINE ('First Name of passenger is:  ' ||pFirst);
DBMS_OUTPUT.PUT_LINE ('Last Name of passenger is:  ' ||pLast);
DBMS_OUTPUT.PUT_LINE ('Frequent Flyer number of passenger is: ' ||fFreqflyernum);
DBMS_OUTPUT.PUT_LINE ('Frequent Flyer Mileage of Passenger is:  ' ||fFreqflyerMileage);
DBMS_OUTPUT.PUT_LINE ('Frequent Flyer Balance of passenger is:  ' ||fMileageBalance );
DBMS_OUTPUT.PUT_LINE ('Mileage expiration date of passenger is:  ' ||fMileageExpDate);
DBMS_OUTPUT.PUT_LINE (' ');

END LOOP;

        CLOSE cur_FreqFlyer; --close cursor

        END sp_GetMileageBalance;

When I ran that, it worked as expected. When I ran the stored procedure:

SET SERVEROUTPUT ON

        EXECUTE sp_GetMileageBalance (10000,20000,'01-OCT-17');

I Got my results:

CUSTOMER INFORMATION: 

The Frequent Flyer PassengerID is:  KL87DF34DS
First Name of passenger is:  Michelle
Last Name of passenger is:  Mullington
Frequent Flyer number of passenger is: 9374392018
Frequent Flyer Mileage of Passenger is:  400
Frequent Flyer Balance of passenger is:  15000
Mileage expiration date of passenger is:  19-DEC-20

>PL/SQL procedure successfully completed.

However, when I tried to put an exception:

EXCEPTION
    WHEN no_data_found THEN
     -- do something
     dbms_output.put_line('Results not found.Try again ');

It gave me this error:

LINE/COL ERROR

54/2 PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following: ( begin case declare end exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

62/5 PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map

Don't know why it spiting this back at me.

This is how I placed it in my code:

--First Stored Procedure--
CREATE OR REPLACE PROCEDURE sp_GetMileageBalance (StartMileage IN NUMBER,EndMileage IN NUMBER, ExpDate IN DATE)
IS  --Code declaration section--
    --variables to store column values returned from select into
     fPassengerID        VARCHAR2(10);
     pFirst              VARCHAR2(20);
     pLast               VARCHAR2(20);
     fFreqflyernum       NUMBER (10);
     fFreqflyerMileage   NUMBER (7);
     fMileagebalance     NUMBER (7);
     fMileageExpDate     DATE;
     MileageExpDate      DATE;
     MileageStart        NUMBER (7);
     MileageEnd          NUMBER (7);
     MileageBalance      NUMBER (7);

     --Declare Cursor
              CURSOR cur_FreqFlyer IS
     --Query cursor will point to results         
SELECT F.FPASSENGERID, P.First, P.Last, F.FREQFLYERNUM, F.FREQFLYERMILEAGE, F.MILEAGEBALANCE, F.MILEAGEEXPDATE
INTO  fPassengerID, pFirst,pLast,fFreqflyernum,fFreqflyerMileage,fMileagebalance,fMileageExpDate
FROM FREQUENT_FLYER F
INNER JOIN PASSENGER P
ON F.FPassengerID = P.PassengerID
WHERE F.MileageBalance >= StartMileage AND F.MILEAGEBALANCE <= EndMileage
AND MileageExpDate > ExpDate;


    --Start Execution section--
BEGIN 
     --Open Cursor
     OPEN cur_FreqFlyer; --  open cursor for use   
     --loop to display each record returned by cursor 
     --Use PL/SQL language control or loop to display each record pointed by cursor 
   LOOP
        --Fetch cursor data
        FETCH cur_FreqFlyer INTO fPassengerID,pFirst,pLast,fFreqflyernum,fFreqflyerMileage,
        fMileageBalance,fMileageExpDate;
        EXIT WHEN cur_FreqFlyer%NOTFOUND;
        --Display each record
        --Displaying the results


DBMS_OUTPUT.PUT_LINE ('CUSTOMER INFORMATION: ');
DBMS_OUTPUT.PUT_LINE (' ');
DBMS_OUTPUT.PUT_LINE ('The Frequent Flyer PassengerID is:  ' ||fPassengerID);
DBMS_OUTPUT.PUT_LINE ('First Name of passenger is:  ' ||pFirst);
DBMS_OUTPUT.PUT_LINE ('Last Name of passenger is:  ' ||pLast);
DBMS_OUTPUT.PUT_LINE ('Frequent Flyer number of passenger is: ' ||fFreqflyernum);
DBMS_OUTPUT.PUT_LINE ('Frequent Flyer Mileage of Passenger is:  ' ||fFreqflyerMileage);
DBMS_OUTPUT.PUT_LINE ('Frequent Flyer Balance of passenger is:  ' ||fMileageBalance );
DBMS_OUTPUT.PUT_LINE ('Mileage expiration date of passenger is:  ' ||fMileageExpDate);
DBMS_OUTPUT.PUT_LINE (' ');

 EXCEPTION
    WHEN no_data_found THEN
     -- do something
     dbms_output.put_line('Results not found.Try again ');


END LOOP;

    CLOSE cur_FreqFlyer; --close cursor

END sp_GetMileageBalance;

Where did you put your EXCEPTION? Seems to be on the wrong place. Please provide entire faulty code. — Wernfried Domscheit
Your EXCEPTION block is in the wrong place - it either needs to be immediately before the END sp_GetMileageBalance, or you need a new block inside the loop. But nothing in your code can throw no_data_found, so why do you have it at all? Are you trying to show a message if the first fetch gets no data? Can the cursor query actually return multiple rows? — Alex Poole
Exactly. If the user inputs a number, for instance say 200, 400. If there is no record of those numbers within the actual tables itself, I would want an exception handler to say “Hey, there are no results. Try inputting different numbers.” And yes, this cursor within the stored procedure has the ability to return multiple records. When I placed the exception block immediately before the “END sp_GetMileageBalance” , attempting to input values that are not within the tables, the only thing I’ll get in return is: PL/SQL procedure successfully completed, instead of an error message — Fathy Kaid
Right, because FETCH will never raise a NO_DATA_FOUND exception - you'll only get that with a SELECT statement. — kfinity
So, what do you suggest? I’ll have error throwned at me if I place it after the select statement of the stored procedure — Fathy Kaid

APC APC · Accepted Answer · 2017-11-09T08:12:20

"Don't know why it spiting this back at me."

EXCEPTION is part of the structure of a PL/SQL block.

DECLARE
    ....
BEGIN
    ....
EXCEPTION
    ....
END

Each section has specific responsibilities and is restricted to code which is pertinent to the responsibility. So the DECLARE section is for declarations (variables, local procs), the BEGIN section is for process code and the EXCEPTION section is for handling exceptions.

By putting the exception keyword where you did you terminated the BEGIN section and started the exception handler. close is not a valid statement for handling exceptions so the compiler hurls.

PLS Error: 00103 Encountered the symbol “EXCEPTION”

2 Answers