Exception error when creating procedure oracle sql developer

Question

i have been whacking my brain for the past 2 hours can't find a solution to this error. I am creating a simple procedure to find an employee. PL/SQL keeps giving me error. What is the problem ? what am i doing wrong here ?

This is my Procedure:

create or replace PROCEDURE find_employee (employeeNo IN number) as
    INVALID_ID exception;
    TOO_MANY_ROWS exception;
    res number;
BEGIN
    dbms_output.enable;
    Select count(*) into res from employee where ID=employeeNo;
    if (res>1)then      -- Checking that the total count of the employee is 1 or not
        raise TOO_MANY_ROWS;                                            -- if greater then 1 then it raise TOO_MANY_ROWS error
    ELSE IF (NOT EXISTS (Select ID from employee where ID=employeeNo))  -- Checking that the employeeNo user passes exist or not
    then
        raise INVALID_ID;                                               -- if employeeNo doesnot exit then display invalid id message
    ELSE
        Select* from Employee where ID=employeeNo;                      -- else return employee info whose id==employeeNo
    END IF;
EXCEPTION
    when TOO_MANY_ROWS then
        DBMS_OUTPUT.PUT_LINE ('Too many Rows with same employee id');
    when INVALID_ID then
        DBMS_OUTPUT.PUT_LINE ('Invalid employee id');
END find_employee;

And error is this:

Error(15,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 

Error(20,18): 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 

Please God Help me :'(

Answer 1

You're missing END IF (line #16). it is easier to spot it if you write formatted code (nested IF should have been indented).

SQL> create or replace PROCEDURE find_employee (employeeNo IN number) as
  2      INVALID_ID exception;
  3      TOO_MANY_ROWS exception;
  4      res number;
  5  BEGIN
  6      dbms_output.enable;
  7      Select count(*) into res from employee where ID=employeeNo;
  8      if (res>1)then      -- Checking that the total count of the employee is 1 or not
  9          raise TOO_MANY_ROWS;                                            -- if greater then 1 then it raise TOO_MANY_ROWS error
 10      ELSE IF (NOT EXISTS (Select ID from employee where ID=employeeNo))  -- Checking that the employeeNo user passes exist or not
 11      then
 12          raise INVALID_ID;                                               -- if employeeNo doesnot exit then display invalid id message
 13      ELSE
 14          Select* from Employee where ID=employeeNo;                      -- else return employee info whose id==employeeNo
 15      END IF;
 16      END IF;        --> this is missing
 17  EXCEPTION
 18      when TOO_MANY_ROWS then
 19          DBMS_OUTPUT.PUT_LINE ('Too many Rows with same employee id');
 20      when INVALID_ID then
 21          DBMS_OUTPUT.PUT_LINE ('Invalid employee id');
 22  END find_employee;

---

As @Dornaut commented, that code probably isn't the best one could produce. Here's another option; see if it helps.

CREATE OR REPLACE PROCEDURE find_employee (employeeNo IN NUMBER)
AS
   res    NUMBER;
   e_row  employee%ROWTYPE;
BEGIN
   SELECT *
     INTO e_row
     FROM employee
    WHERE id = employeeNo;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('Invalid employee ID');
   WHEN TOO_MANY_ROWS
   THEN
      DBMS_OUTPUT.put_line ('Too many rows with same employee ID');
END find_employee;

So: if SELECT returns NO_DATA_FOUND or TOO_MANY_ROWS, it'll be handled. Otherwise, it'll fetch the whole row into a variable.