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 :'(

Littlefoot · Accepted Answer · 2020-08-13 07:28:20Z

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.

edited Aug 13, 2020 at 7:28

answered Aug 13, 2020 at 6:20

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dornaut Over a year ago

Isn't it more concise and readable to just use 'ELSIF' on line 10? Also would it compile in current state? Looks like no because of 'not exists' outside SQL and select statement without 'into' (btw this select don't do anything in fact...).

Littlefoot Over a year ago

You're more than right, @Dornaut. I added some more info. Thank you for the comment.

Collectives™ on Stack Overflow

Exception error when creating procedure oracle sql developer

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related