Non-Predefined Exception in oracle

Non-Predefined Exception in oracle

Using PRAGMA EXCEPTION_INIT
The technique that is used to bind a number exception handler to a named using Pragma Exception_init(). This binding of a numbered of a numbered exception handler, to a name (i.e. String), is done in the declare section of a PL/SQL block.

The Pragma action word is a call to a pre-compiler, which immediately binds the numbered exception handler to a name when encountered.

The function Exception_init() takes two parameters the first is the user defined exception name the second of the oracle engine’s exception number. These lines will be included in the Declare section of the PL/SQL block.

-create a parent child relationship table
-2292 > Entity integrity error

declare
e_emp_remaining EXCEPTION ;
PRAGMA EXCEPTION_INIT(e_emp_remaining,-2292) ;
begin
delete from dept1 where deptno=10 ;
commit;
exception
when e_emp_remaining then
dbms_output.put_line('canot remove dept ');
end;
------------------------------------
declare
z_divide EXCEPTION ;
PRAGMA EXCEPTION_INIT(z_divide,-1476) ;
x number:= 20 ;
y number:= 0 ;
z number;
begin
z:=x/y;
exception
when z_divide then
dbms_output.put_line('Number is divided zero ');
end;

------------------------------------

declare
t_row_many EXCEPTION ;
PRAGMA EXCEPTION_INIT(t_row_many,-1422) ;
begin
select sal into msal from emp where deptno=20;
exception
when t_row_many then
dbms_output.put_line('Too Many Rows returned ');
end;

------------------------------------

declare
t_row_many EXCEPTION ;
PRAGMA EXCEPTION_INIT(t_row_many,-1422) ;
msal emp.sal%type;
begin
select sal into msal from emp where deptno=20;
exception
when t_row_many then
dbms_output.put_line('Too Many Rows returned ');
end;

Error – Trapping Functions:

When an Exception occurs, we can identify the associated error code and error message by using two functions.
SQLCODE returns number of the Oracle error for internal exception. We can pass an error number to SQLERRM, which then return the message associated with the error number.

Declare
Err_num number ;
Err_msg Varchar2(100) ;
Begin

---------

Exception
Err_num = SQLCODE ;
Err_msg := sunstr(SQLERRM,100) ;
Insert into errors values (err_num,err_msg) ;
End ;