[Error] Execution (1: 1): ORA-01403: no data found ORA-06512: at line 5

Toad for Oracle & DevOps Toolkit
1

Hello,
Good day,
If I write 100 it works but if I write 207 it doesn't work.
Can you help me please?

declare
e_loc exception;
v_name hr.employees.first_name%type;
begin
select first_name into v_name from hr.employees
where employee_id = &v_employee_id ;
if sql%notfound then
raise e_loc;
else
dbms_output.put_line('the name is ' || v_name );
end if;
exception
when e_loc then
dbms_output.put_line('nope');
end;

Thank you.

2

The select is raising the no_data_found exception, before your check for not found. I suspect.

You probably need a behin-exception-end around the select and in that exception handler, trap no_data_found and raise your exception there.

Cheers,
Norm[TeamT]

3

This is what I mean:

declare
    e_loc exception;
    v_name hr.employees.first_name%type;
begin
    begin
        select first_name 
            into v_name 
        from hr.employees
        where 
            employee_id = &v_employee_id ;

        dbms_output.put_line('the name is ' || v_name );
    exception
        when no_data_found then
            raise e_loc;
    end;

exception
    when e_loc then
        dbms_output.put_line('nope');
end;

HTH

Cheers,
Norm. [TeamT]

PS. It's Sunday, and the UK is in lockdown. But at least my garage is now very tidy! :wink:

4

Thank you so much, thanks a lot
:wink: