PLSQL error - PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of

Question

I'm new to PL/SQL, needed your help to resolve the below error. while trying on a script I keep receiving the below. I tried to resolve and spend some time to get through, but was in vain. Answer/resolution will be much appreciated, Thank you :-)

ORA-06550: line 34, column 1:
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 <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge
ORA-06550: line 52, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   end not pragma final instantiable order overriding static
   member constructor map

Script Terminated on line 5.

The original script is:

DECLARE
    VRO_HISAL HISAL%ROWTYPE;
    VRO_LOSAL LOSAL%ROWTYPE;
    VRO_MSGS HILOSAL_MSG%ROWTYPE;

    V_SAL HISAL.SAL%TYPE;

    EX_BELOW10K EXCEPTION;
    EX_TOLOSAL EXCEPTION;

BEGIN

    SELECT NVL(MAX(EMPNO), 7000) + 1   INTO   VRO_HISAL.EMPNO   FROM   HISAL; 
    VRO_HISAL.ENAME  := '&NAME';
    VRO_HISAL.SAL    := &SALARY;

    VRO_LOSAL.EMPNO := VRO_HISAL.EMPNO;
    VRO_LOSAL.ENAME := VRO_HISAL.ENAME;
    VRO_LOSAL.SAL   := VRO_HISAL.SAL;

    IF VRO_HISAL.SAL < 10000 THEN
        RAISE EX_BELOW10K;

    ELSE IF VRO_HISAL.SAL > 10000 AND VRO_HISAL.SAL < 30000 THEN
        RAISE EX_TOLOSAL;

        ELSE 
            INSERT INTO HISAL VALUES (VRO_HISAL);
    END IF;

EXCEPTION
    WHEN EX_BELOW10K THEN
        SELECT NVL(MAX(MSGNO), 500) + 1    INTO   VRO_MSGS.MSGNO     FROM   HILOSAL_MSG;
        VRO_MSGS.MSGTXT := 'This salary  '||VRO_HISAL.SAL||'  is not a valid amount';
        VRO_MSGS.MSGTIME := TO_CHAR (SYSDATE, 'YYYY-MON-DD HH24:MI:SS');

    WHEN EX_TOLOSAL THEN
        INSERT INTO LOSAL VALUES VRO_LOSAL;

    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE ('Invalid operation.');

    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE ('Result is more than expected.');

    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE ('Some other error occurred. Contact Admin.');

END;

tables used:

TABLES: HISAL & LOSAL --2 tables with identical columns:

EMPNO NUMBER
EMPNAME VARCHAR2(20),
SAL NUMBER


TABLE 3: HILOSAL_MSG columns:

 MSGNO   NUMBER,
 MSGTXT  VARCHAR2(30),
 MSGTIME DATE

Thank you, Cheers :-)

XING · Answer 1 · 2016-12-16T14:58:45.637

You code has few issues. Try as below:

DECLARE
   VRO_HISAL     HISAL%ROWTYPE;
   VRO_LOSAL     LOSAL%ROWTYPE;
   VRO_MSGS      HILOSAL_MSG%ROWTYPE;
   V_SAL         HISAL.SAL%TYPE;
   EX_BELOW10K   EXCEPTION;
   EX_TOLOSAL    EXCEPTION;
BEGIN
   SELECT NVL (MAX (EMPNO), 7000) + 1
     INTO VRO_HISAL.EMPNO   
     FROM HISAL;

   VRO_HISAL.ENAME := '&NAME';
   VRO_HISAL.SAL := &SALARY;

   VRO_LOSAL.EMPNO := VRO_HISAL.EMPNO;
   VRO_LOSAL.ENAME := VRO_HISAL.ENAME;
   VRO_LOSAL.SAL := VRO_HISAL.SAL;

   IF VRO_HISAL.SAL < 10000
   THEN
      RAISE EX_BELOW10K;
   ELSIF VRO_HISAL.SAL > 10000 AND VRO_HISAL.SAL < 30000
    THEN
      RAISE EX_TOLOSAL;
   ELSE
         INSERT INTO HISAL
              VALUES VRO_HISAL;
   END IF;       
EXCEPTION
   WHEN EX_BELOW10K
   THEN
      SELECT NVL (MAX (MSGNO), 500) + 1
        INTO VRO_MSGS.MSGNO
        FROM HILOSAL_MSG;

      VRO_MSGS.MSGTXT :=
         'This salary  ' || VRO_HISAL.SAL || '  is not a valid amount';
      VRO_MSGS.MSGTIME := TO_CHAR (SYSDATE, 'YYYY-MON-DD HH24:MI:SS');
   WHEN EX_TOLOSAL
   THEN
      INSERT INTO LOSAL
           VALUES VRO_LOSAL;
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE ('Invalid operation.');
   WHEN TOO_MANY_ROWS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Result is more than expected.');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Some other error occurred. Contact Admin.');
END;

Issues:

1) Misspelt ELSEIF; 2) Your table name has column empname and code ename 3) INSERT INTO HISAL VALUES (VRO_HISAL) at this statement brackets need to be removed.

I don't think that first issue is "missing `end if`"; instead, I think it's using `else if` instead of `elsif`. — Boneist, Dec 16 '16 at 11:20
@Boneist..Thanks. I considered as `IF block` inside a `IF block` and suggested. Anyways i made changes according to `if-elsif block`. — XING, Dec 16 '16 at 13:01
@XING yeah, it's kind of six-of-one, half-a-dozen-of-the-other situation. Either would work, but I think the code the OP provided is clearer as an if-elsif-else, rather than a nested if, and - I suspect - what they were after. — Boneist, Dec 16 '16 at 14:05

PLSQL error - PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of

1 Answers1