using select INTO to raise a user defined error

[Erikxxx]

Hi all,

I want to use a user defined exception if my SELECT INTO statement return no data found. I'm not how to achieve this since Oracle automatically seems to throw a NO_DATA exception.

Thanks
E

 

[carp]

I suppose the easiest way would be something along the lines of

DECLARE
x_yikes EXCEPTION;
BEGIN
your code here
EXCEPTION
WHEN no_data_found THEN
RAISE x_yikes;
WHEN x_yikes THEN
whatever you want to do
END;

I've seen people redefine the NO DATA FOUND error code to point to another exception, but trying to mess with things that Oracle has pre-defined seems like asking for hard-to-diagnose problems to me!

 

[SantaMufasa]

Erik,

Here is a simple example:

set verify off
set serveroutput on
accept emp_id prompt "Enter the employee id to display: "
declare
    e s_emp%rowtype;
begin
    select * into e from s_emp where id = '&emp_id';
    dbms_output.put_line('&emp_id: '||e.first_name||' '||e.last_name);
exception
    when no_data_found then
        dbms_output.put_line ('Error: No employee id -- &emp_id');
end;
/

SQL> @tt_298
Enter the employee id to display: 1
1: Carmen Velasquez

PL/SQL procedure successfully completed.

SQL> @tt_298
Enter the employee id to display: 999
Error: No employee id -- 999

PL/SQL procedure successfully completed.

If you want/need to know how to put an exception handler inside a LOOP, and not abend when an exception occurs, please post your interest, and I'll post an example of that, as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Erikxxx]

Thanks Carp,

I also found this example that does what I'm looking for.

DECLARE
i NUMBER;
BEGIN
select count(*) into i from emptable where employee_id =206;
if i < 1 THEN
-- throw user defined exception
END IF;

END;

 

[dbomrrsm]

Be carefull

Select count (*) will never return no_data_found as it will return 0 if there are no rows to count.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[carp]

If all you want is to check the existence of the employee, then that will do it. However, if you actually need the data from the employee record, you will now need to query the table twice. But I am guessing you just needed to confirm the employee's existence.