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;
Is This Answer Correct ? | 5 Yes | 0 No |
how can we return morethan one parameters in procedure?
Tell me where we find the status of order information?
what is user exit and wat program do we write in user exit?
what is the prerequisite for defining unit of measure?
what are the receipt types in Po?
In developer Reports::there are 3 parameters.can you pass the paramter1+paratmeter2 value to the third parameter??
LOCKS?
what are the different calculation columns available in reports?
Where we can write a PL/SQL code in report triggers?
List the types of flexfield?
Who information?s?
where u do setup of operating unit?