CODEWITHSUNDEEP
plsql
atul kushwaha
atul kushwaha

Reputation: 1

Why RAISE_APPLICATION_ERROR() function is not working in this code?

I want to throw a customize error when the query fetches no record. But the below code is not throwing my desired error. It is throwing the system generated an error. Could you please help me? What mistake I am making?

DECLARE
v_em_id NUMBER := &var_id;
v_name varchar2(20);
BEGIN
SELECT first_name INTO v_name FROM employees WHERE employee_id =v_em_id;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20002,'Employee not exist');
END IF;
DBMS_OUTPUT.PUT_LINE('Hi! '|| v_name);
END;

I should throw the error code:-20002 and error message: Employee not exist.

Upvotes: 0

Views: 1771

Answers (1)

Chaitanya Kotha
Chaitanya Kotha

Reputation: 476

You can try the below code...

DECLARE
  v_em_id NUMBER := &var_id;
  v_name  varchar2(20);
BEGIN

  SELECT first_name INTO v_name FROM employees WHERE employee_id = v_em_id;

  DBMS_OUTPUT.PUT_LINE('Hi! ' || v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    IF SQL%NOTFOUND THEN
      RAISE_APPLICATION_ERROR(-20002, 'Employee not exist');
    END IF;
END;

If the select statement returns no rows it will raise no_data_found exception. So system is failing before the IF SQL%NOTFOUND THEN line..

Upvotes: 2

Related Questions