plsql raise_application_error with predefined exception

Question

Can anyone please let me know what mistake I m making here? Can we have a predefined exception with raise_application_error?

declare
        s1 emp.sal %type;
begin
        select sal into s1 from emp where ename='SOMDUTT';
        if no_data_found then
            raise_application_error(20001, 'somdutt is not there');
        end if;
        if(s1 > 10000) then
            raise_application_error(20002, 'somdutt is earing a lot');
        end if;
        update emp set sal=sal+500 where ename='SOMDUTT';
end;
/

if no_data_found then
*
ERROR at line 5:
ORA-06550: line 5, column 4:
PLS-00382: expression is of the wrong type
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

no_data_found is an exception, not a variable that you can test with if. — Jeffrey Kemp
– Jeffrey Kemp, Commented Nov 26, 2018 at 7:47

Kaushik Nayak · Accepted Answer · 2018-11-20 05:47:29Z

3

Move the condition to check no_data_found to an exception block.

Also, you can only use an error number in the range of -20000 to -20999

declare
        s1 emp.sal%type;
begin
        select sal into s1 from emp where ename='SOMDUTT';

        if s1 > 10000  then
            raise_application_error(-20002, 'somdutt is earing a lot');
        end if;
        update emp set sal=sal+500 where ename='SOMDUTT';
     EXCEPTION

     when no_data_found then
            raise_application_error(-20001, 'somdutt is not there');
end;
/

answered Nov 20, 2018 at 5:47

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

2 Comments

Nav Over a year ago

Thanks, so it means we cannot use raise_application_error with a predefined exception like no_data_found in the if block?

Kaushik Nayak Over a year ago

@Nav : no_data_found will be raised by default when the select into returns no rows. If you want to raise it forcefully, you can still do it using a raise no_data_found but it won't display the error message you want.

Collectives™ on Stack Overflow

plsql raise_application_error with predefined exception

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related