Encountered the symbol "EXCEPTION" when expecting one of the following: ,

Question

1.This is a package to take input from user several details and store it in DB, but here I'm getting several errors while creating validation(exception part)

create or replace PACKAGE  MOVIE_PKG
AS
PROCEDURE ADDCONSUMERDETAILS(c_con_ID consumer_info.con_id%type,c_con_name consumer_info.con_name%type,
c_con_email consumer_info.con_email%type,c_con_co consumer_info.con_co%type,c_con_ct consumer_info.con_ct%type);
END MOVIE_PKG;

create or replace PACKAGE BODY MOVIE_PKG
AS
--ADD/INSERT CONSUMER INFORMATION--take input for cust id 
PROCEDURE ADDCONSUMERDETAILS(c_con_id consumer_info.con_id%type,c_con_name consumer_info.con_name%type,
c_con_email consumer_info.con_email%type,c_con_co consumer_info.con_co%type,c_con_ct consumer_info.con_ct%type)
AS
EXC_CON_ID EXCEPTION;
EXC_CON_NAME EXCEPTION;
EXC_CON_CO EXCEPTION;
BEGIN
--CONSUMER ID VALIDATION
IF LENGTH(EXC_CON_ID)=0 THEN
RAISE EXC_CON_ID;
ELSE IF LENGTH(EXC_CON_ID)<4 THEN
RAISE EXC_CON_NAME;
ELSE IF LENGTH(EXC_CON_CO)!=10 THEN
RAISE EXC_CON_CO;
ELSE
INSERT INTO CONSUMER_INFO VALUES(C_CON_ID,C_CON_NAME,C_CON_EMAIL,C_CON_CO,C_CON_CT);
  DBMS_OUTPUT.PUT_LINE('Data Added Successfully');
END IF;

EXCEPTION
WHEN EXC_CON_ID THEN
   DBMS_OUTPUT.PUT_LINE('INVALID CONSUMER ID, CHECK CONSUMER ID:ENTER CORRECT ID');
WHEN EXC_CON_NAME THEN
   DBMS_OUTPUT.PUT_LINE('INVALID CONSUMER NAME, ENTER CORRECT CONSUMER NAME');
WHEN EXC_CON_CO THEN
   DBMS_OUTPUT.PUT_LINE('INVALID CONSUMER NUMBER');
END ADDCONSUMERDETAILS;

END MOVIE_PKG;

--Trigger for insertion alert on consumer table

create or replace TRIGGER consumer_insert_chk
AFTER
INSERT
ON CONSUMER_INFO
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('A ROW IS INSERTED TO CONSUMER TABLE');
--End of trigger
END consumer_insert_chk;

--CLIENT SIDE BLOCK

SET SERVEROUTPUT ON;
DECLARE
  C_CON_ID consumer_info.con_id%type:='&IDENTIFICATIONNUMBER';
  C_CON_NAME  CONSUMER_INFO.CON_NAME%TYPE:='&NAME';
  C_CON_EMAIL CONSUMER_INFO.CON_EMAIL%TYPE:='&EMAIL';
  C_CON_CO CONSUMER_INFO.CON_CO%TYPE:=&CONTACTNO;
  C_CON_CT CONSUMER_INFO.CON_CT%TYPE:='&CITY';
BEGIN
MOVIE_PKG.ADDCONSUMERDETAILS(C_CON_ID,c_con_name,c_con_email,c_con_co,c_con_ct);
END;
/

2. This is the error I'm getting

   LINE/COL ERROR

   ---

   23/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 << continue close current delete fetch lock insert open rollback savepoint set SQL execute commit for all merge pipe purge 32/5 PLS-00103: Encountered the symbol "MOVIE_PKG" when expecting one of the following: Errors: check compiler log

Answer 1

You're missing two END IFs before the EXCEPTION keyword.

BEGIN
   --CONSUMER ID VALIDATION
   IF LENGTH (EXC_CON_ID) = 0
   THEN
      RAISE EXC_CON_ID;
   ELSE
      IF LENGTH (EXC_CON_ID) < 4
      THEN
         RAISE EXC_CON_NAME;
      ELSE
         IF LENGTH (EXC_CON_CO) != 10
         THEN
            RAISE EXC_CON_CO;
         ELSE
            INSERT INTO CONSUMER_INFO
                 VALUES (C_CON_ID,
                         C_CON_NAME,
                         C_CON_EMAIL,
                         C_CON_CO,
                         C_CON_CT);

            DBMS_OUTPUT.PUT_LINE ('Data Added Successfully');
         END IF;
      END IF;              --> this
   END IF;                 --> this
EXCEPTION
   WHEN EXC_CON_ID
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'INVALID CONSUMER ID, CHECK CONSUMER ID:ENTER CORRECT ID');
   WHEN EXC_CON_NAME
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'INVALID CONSUMER NAME, ENTER CORRECT CONSUMER NAME');
   WHEN EXC_CON_CO
   THEN
      DBMS_OUTPUT.PUT_LINE ('INVALID CONSUMER NUMBER');
END ADDCONSUMERDETAILS;

Believe or not, but it actually helps a lot if you pay attention to formatting. Indentation makes miracles and lets you easily spot the culprit. Unformatted code is a mess so ... no wonder you have problems. I suggest you to either use GUI's formatter or - if you're in SQL*Plus - format it manually.

Answer 2

The correct syntax for the ELSE IF statement is ELSIF (link). Also I don't understand why you always check the length of an exception.

create or replace PACKAGE  MOVIE_PKG
AS
PROCEDURE ADDCONSUMERDETAILS(c_con_ID consumer_info.con_id%type,
                             c_con_name consumer_info.con_name%type,
                             c_con_email consumer_info.con_email%type,
                             c_con_co consumer_info.con_co%type,
                             c_con_ct consumer_info.con_ct%type
                            );
END MOVIE_PKG;
/

create or replace PACKAGE BODY MOVIE_PKG
AS
--ADD/INSERT CONSUMER INFORMATION--take input for cust id 

PROCEDURE ADDCONSUMERDETAILS(c_con_ID consumer_info.con_id%type,
                             c_con_name consumer_info.con_name%type,
                             c_con_email consumer_info.con_email%type,
                             c_con_co consumer_info.con_co%type,
                             c_con_ct consumer_info.con_ct%type
                            );
AS
  EXC_CON_ID EXCEPTION;
  EXC_CON_NAME EXCEPTION;
  EXC_CON_CO EXCEPTION;
BEGIN
  --CONSUMER ID VALIDATION
  IF LENGTH(C_CON_ID) = 0 THEN
    RAISE EXC_CON_ID;

  ELSIF LENGTH(C_CON_NAME) < 4 THEN
    RAISE EXC_CON_NAME;

  ELSIF LENGTH(C_CON_CO) != 10 THEN
    RAISE EXC_CON_CO;

  ELSE
    INSERT INTO CONSUMER_INFO 
           VALUES(C_CON_ID,
                  C_CON_NAME,
                  C_CON_EMAIL,
                  C_CON_CO,
                  C_CON_CT);
    DBMS_OUTPUT.PUT_LINE('Data Added Successfully');
  END IF;

EXCEPTION
  WHEN EXC_CON_ID THEN
    DBMS_OUTPUT.PUT_LINE('INVALID CONSUMER ID, CHECK CONSUMER ID:ENTER CORRECT ID');
  WHEN EXC_CON_NAME THEN
    DBMS_OUTPUT.PUT_LINE('INVALID CONSUMER NAME, ENTER CORRECT CONSUMER NAME');
  WHEN EXC_CON_CO THEN
    DBMS_OUTPUT.PUT_LINE('INVALID CONSUMER NUMBER');

END ADDCONSUMERDETAILS;

END MOVIE_PKG;
/