Exception Handilings?

Question Posted / kiran kumar da

1 Answers
8567 Views
I also Faced
E-Mail Answers

Exception Handilings?..

Answer / saleem

Is a mechanism provided by pl/sql to detect runtime
errors and process them with out halting the program abnormally
1) pre-defined
2) user-defined.
PRE-DEFINED:
1) cursor_already_open--------attempted to open an already
open cursor.
2) Dup_val_on_index --------attempted to insert a
duplicate values.
3) Invalid_cursor -------- illegal cursor operation
occurred.
4) Invalid_number -------- conversion of character
string to number fails.
5) Login_denied ---------loging on to oracle with
an invalid user name
and password.
6) program_error -------- pl/sql has an internal
problem.
7) storage_error -------- pl/sql ran out of
memory or memory is
corrupted.
8) to_many_row ---------single row select
returned more than one row.
9) value_error --------
arithmetic,conversion,truncation or size

constraint error occurred.
10) zero_devided -------- attempted to divided by
zero.

USER-DEFINED:
Declare : name the exception
Raise : explicitly raise the exception by
using the raise statements
Reference: exception handing section.

The Raise_Application_Error_Procedure:
 You can use this procedure to issue user-defined error
messages from stored sub programs.
 You can report errors to your applications and avoid
returning unhandled exceptions.

Raise_Application_Error(error_number,message[,{true/false}]
Error number  between -20000 to -20999

pragma exception_init?
It tells the compiler to associate an exception with
an oracle error. To get an error message of a specific
oracle error.
Ex: pragma exception_init(exception name, oracle
error number)

Example for Exceptions?
1) Check the record is exist or not?
Declare
E emp% rowtype
Begin
e.empno := &empno;
select * into e from emp where empno
=e.empno;
Dbms_output.putline(?empno? || e.empno);
Exception
When no_data_found then
Dbms_output.putline(e.empno ||?doest
exist?);
End;

2) User defined exceptions?
Define p_dept_desc =?gvreddy?
Define p_dept_number =1236
Declare
E_invalid_dept exception;
Begin
Update departments
Set dept_name=?&p_dept_desc?
Where dept_id =?&p_dept_number?;
If sql% not found then
Raise e_invalid_departments;
End if;
Commit;
Exception
When e_invalid_departments then
Dbms_output.putline(?no such dept?);
End;