Exception

Predefined Exception In Oracle

ACCESS_INTO_NULL        ORA-06530
CASE_NOT_FOUND          ORA-06592
COLLECTION_IS_NULL      ORA-06531
CURSOR_ALREADY_OPEN     ORA-06511
DUP_VAL_ON_INDEX        ORA-00001
INVALID_CURSOR          ORA-01001
INVALID_NUMBER          ORA-01722
LOGIN_DENIED            ORA-01017
NO_DATA_FOUND           ORA-01403
NOT_LOGGED_ON           ORA-01012
PROGRAM_ERROR           ORA-06501
ROWTYPE_MISMATCH        ORA-06504
SELF_IS_NULL            ORA-30625
STORAGE_ERROR           ORA-06500
SUBSCRIPT_BEYOND_COUNT  ORA-06533
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532
SYS_INVALID_ROWID       ORA-01410
TIMEOUT_ON_RESOURCE     ORA-00051
TOO_MANY_ROWS           ORA-01422
VALUE_ERROR             ORA-06502
ZERO_DIVIDE             ORA-01476

Advertisements

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;