CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle11g
Fathy Kaid
Fathy Kaid

Reputation: 21

PLS Error: 00103 Encountered the symbol "EXCEPTION"

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;

Upvotes: 0

Views: 1234

Answers (2)

APC
APC

Reputation: 146349

"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.

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59592

You can achieve it like this:

BEGIN 
   OPEN cur_FreqFlyer; --  open cursor for use   

   FETCH cur_FreqFlyer INTO fPassengerID,pFirst,pLast,fFreqflyernum,fFreqflyerMileage,
        fMileageBalance,fMileageExpDate;
   IF cur_FreqFlyer%NOTFOUND THEN
      dbms_output.put_line('Results not found.Try again ');
   END ID;

   LOOP   
      EXIT WHEN cur_FreqFlyer%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE ('CUSTOMER INFORMATION: ');
      DBMS_OUTPUT.PUT_LINE (' ');
      DBMS_OUTPUT.PUT_LINE ('The Frequent Flyer PassengerID is:  ' ||fPassengerID);
      ...

      --Fetch cursor data
      FETCH cur_FreqFlyer INTO fPassengerID,pFirst,pLast,fFreqflyernum,fFreqflyerMileage,
        fMileageBalance,fMileageExpDate;

   END LOOP;

   CLOSE cur_FreqFlyer; --close cursor

END sp_GetMileageBalance;

Upvotes: 0

Related Questions