What is Raise Application Error in oracle?

What is Raise_application_error in oracle?

Raise_application_error allows users to create custom error messages.
Syntax:-

Raise_application_error (error_number error_messages);

Here, error_number is between -20000 to -20999 because remaining numbers are for Oracle. Message can be varchar(2000)
They are most commonly used to handle unwanted and unmanageable exceptions.

Raise command is used for raise an user defined exception.

Raise_application_error is a procedrue in dbms_standard package..It will raise the user defined error message and error number.

Error number range will be from -20000 to -20999. Error msg length can be 2048 bytes..(new versions may be more than this).

——————————- predefined Exception ———————

declare
emp_id number;
begin
select employee_id into emp_id
from employees
where department_id=1000;
dbms_output.put_line(’employee_id’||emp_id);
Exception
when no_data_found
then
dbms_output.put_line(‘No such employee exist’);
when too_many_rows
then
dbms_output.put_line(‘fetch too many row, so please use cursor’);
end;

————————————— declare block use in Exception block ————————-

declare
emp_id number;
begin
select employee_id into emp_id
from employees
where department_id=1000;
dbms_output.put_line(’employee_id’||emp_id);
Exception
when no_data_found
then
declare
sal number;
begin
dbms_output.put_line(‘No such employee exist’);
select salary into sal from employees
where employee_id=100;
dbms_output.put_line(‘SAL’||sal);
exception
when no_data_found
then
dbms_output.put_line(’employee does not exits’);
end;
dbms_output.put_line(‘fetch too many row’);
when too_many_rows
then
dbms_output.put_line(‘fetch too many row, so please use cursor’);
end;
/

———————————————-  Pragma exception —————–

declare
emp_exception exception;
pragma exception_init(emp_exception,100);
emp_id number;
begin
select employee_id into emp_id from employees
where employee_id=1111;
exception
when emp_exception
then
dbms_output.put_line(‘NO RECORD’);
–dbms_output.put_line(sqlcode||chr(9)||sqlerrm);
end;
/

————————- USer defined exception(raise) ———————–

declare
emp_exception exception;
begin
update employees
set employee_id=10
where employee_id=10;
IF sql%notfound
then
raise emp_exception;
end if;
exception
when emp_exception then
dbms_output.put_line(‘HELLO’);
end;
/

declare
emp_exception exception;
emp_id number;
begin
begin
select employee_id into emp_id
from employees
where employee_id=10;
exception
when no_data_found
then
raise emp_exception;
end;
exception
when emp_exception then
dbms_output.put_line(‘HELLO’);
end;
/

———————————- Raise_application_error —————————

declare
emp_exception exception;
begin
update employees
set employee_id=10
where employee_id=10;
IF sql%notfound
then
raise emp_exception;
end if;
exception
when emp_exception then
raise_application_error(-20001,’No Data found for that record’);
end;
/

create or replace procedure XXC05_RAISE_EXCEPTION_PROC
is
XXC05_RAISE_EXCEP EXCEPTION;
emp_id number;
begin
begin
select employee_id
into emp_id
from employees
where employee_id=1000;
exception
when no_data_found
then
raise XXC05_RAISE_EXCEP;
end;
exception
when XXC05_RAISE_EXCEP
then
raise_application_error(-20001,’NO ROW FOUND FROM ABOVE QUERY’);
–dbms_output.put_line(‘NO ROW SELECTED’);
end XXC05_RAISE_EXCEPTION_PROC;

Advertisements