numeric or value error, execption handling

[ramesh_1600]

You can create your own exception for any Oracle error messages.
The following PL/SQL block takes ORA-01400 ( "Can not insert NULL into" ) as an example.

SQL>create table temp_null_val ( temp_name varchar2(10) not null );
SQL>set serveroutput on
SQL> declare
2 null_value exception;
3 pragma exception_init ( null_value, -1400 );
4 begin
5 insert into temp_null_val values ( NULL );
6 exception
7 when null_value then
8 dbms_output.put_line ( 'Trying to insert a null value into non null column; insert failed' );
9* end;
10 /

Output:

Trying to insert a null value into non null column; insert failed

PL/SQL procedure successfully completed.