CODEWITHSUNDEEP
oracle-databaseexceptionplsql
ranga swamy
ranga swamy

Reputation: 59

How to raise exception in procedures?

This is my procedure:

create or replace procedure p1(p_deptno in number)
is
  cursor c is select * from emp where deptno=p_deptno;
  i emp%rowtype;
begin
  open c;
  loop
    fetch c into i;
    exit when c%notfound;
    dbms_output.put_line(i.ename);
  end loop;
exception
  when no_data_found then
    dbms_output.put_line('Give proper deptno');
end p1;
/

When I run it using SQL*Plus, I get this:

SQL> exec p1(70);

PL/SQL procedure successfully completed.

But deptno 70 is not available. Exception should be raised, but it is not. What am I doing wrong?

Upvotes: 1

Views: 7993

Answers (3)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Using RAISE_APPLICATION_ERROR Will be more appropriate here if we need to show a user defined error for this particular error message.

CREATE OR REPLACE PROCEDURE p1(
    p_deptno IN NUMBER)
IS
  i emp%rowtype;
BEGIN
  SELECT * INTO i FROM emp WHERE deptno=p_deptno;
EXCEPTION
WHEN no_data_found THEN
  RAISE_APPLICATION_ERROR(-20001,'Dept no '||p_deptno||' has no data',TRUE);
END p1;

Upvotes: 4

Deepak Vn
Deepak Vn

Reputation: 122

No data found exception would be raised only if you fire a select (not a cursor). Following is a sample based on your code where this would happen:

create or replace procedure p1(p_deptno in number)
is
  i emp%rowtype;
begin
  select * into i
  from emp where deptno=p_deptno;
exception
  when no_data_found then
    dbms_output.put_line('Give proper deptno');
end p1;
/

You could raise an exception based on %NOTFOUND as demonstrated by Gavin.

Also,the first fetch from an open cursor, cursor_name%NOTFOUND returns NULL.Thereafter, it returns FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row. (https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems011.htm)

Upvotes: 2

GavinCattell
GavinCattell

Reputation: 3963

You need to call RAISE (documentation link here)

IF i IS NULL THEN
    RAISE no_data_found;
END IF;

Upvotes: 1

Related Questions