Exception means an Error.
In PL/SQL error are called exceptions.
When an exception is raised, processing jumps to the exception handlers.
An exception handler is a sequence of statements to be processed when a certain exception occurs.
When an exception handler is complete processing of the block terminates.
Exception Is a mechanism provided by pl/sql to detect run time errors and process them with out halting the program abnormally.
1) PRE DEFINED
2) USER DEFINED
3) Non Pre-defined Exceptions
PRE-DEFINED:
Defined by oracle
Activated by oracle automatically
Solution provided by User
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
10)zero_devided ------ attempted to divided by zero.
USER-DEFINED:
Defined by user
Activated by user using "Raise" Stmt
solution provided by user.
Reference : exception handing section.
Non Pre-defined Exceptions
(Undefined Exceptions)
- Defined by user
- Activated automatically by constraints
- Solution provided by user
- used to handle constraint voilation errors in pl/sql block. [ pragma exception_init ]
There are four kinds of exceptions in PL/SQL:
1. Named system exceptions: Exceptions that have been given names by PL/SQL and raised as a result of an error in PL/SQL or RDBMS processing.
For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.
2. Named programmer-defined exceptions:Exceptions that are raised as a result of errors in your application code. You give these exceptions names by declaring them in the declaration section. You then raise the exceptions explicitly in the program.
EX : Exception_name EXCEPTION;
3. Unnamed system exceptions: Exceptions that are raised as a result of an error in PL/SQL or RDBMS processing but have not been given names by PL/SQL. Only the most common errors are so named; the rest have numbers and can be assigned names with the special PRAGMA EXCEPTION_INIT syntax.
EX : Child_rec_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (Child_rec_exception, -2292);
4. Unnamed programmer-defined exceptions: Exceptions that are defined and raised in the server by the programmer. In this case, the programmer provides both an error number (between -20000 and -20999) and an error message, and raises that exception with a call to RAISE_APPLICATION_ERROR. That error, along with its message, is propagated back to the client-side application.
EX : RAISE_APPLICATION_ERROR (error_number, error_message);
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.
Syntax:
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)
Examples:
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;
User defined exceptions?
Define p_dept_desc =’Oracleapps4u’
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;
In PL/SQL error are called exceptions.
When an exception is raised, processing jumps to the exception handlers.
An exception handler is a sequence of statements to be processed when a certain exception occurs.
When an exception handler is complete processing of the block terminates.
Exception Is a mechanism provided by pl/sql to detect run time errors and process them with out halting the program abnormally.
1) PRE DEFINED
2) USER DEFINED
3) Non Pre-defined Exceptions
PRE-DEFINED:
Defined by oracle
Activated by oracle automatically
Solution provided by User
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
10)zero_devided ------ attempted to divided by zero.
USER-DEFINED:
Defined by user
Activated by user using "Raise" Stmt
solution provided by user.
Declare : name the exception
Raise : explicitly raise the exception by using the raise statementsReference : exception handing section.
Non Pre-defined Exceptions
(Undefined Exceptions)
- Defined by user
- Activated automatically by constraints
- Solution provided by user
- used to handle constraint voilation errors in pl/sql block. [ pragma exception_init ]
There are four kinds of exceptions in PL/SQL:
1. Named system exceptions: Exceptions that have been given names by PL/SQL and raised as a result of an error in PL/SQL or RDBMS processing.
For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.
2. Named programmer-defined exceptions:Exceptions that are raised as a result of errors in your application code. You give these exceptions names by declaring them in the declaration section. You then raise the exceptions explicitly in the program.
EX : Exception_name EXCEPTION;
3. Unnamed system exceptions: Exceptions that are raised as a result of an error in PL/SQL or RDBMS processing but have not been given names by PL/SQL. Only the most common errors are so named; the rest have numbers and can be assigned names with the special PRAGMA EXCEPTION_INIT syntax.
EX : Child_rec_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (Child_rec_exception, -2292);
4. Unnamed programmer-defined exceptions: Exceptions that are defined and raised in the server by the programmer. In this case, the programmer provides both an error number (between -20000 and -20999) and an error message, and raises that exception with a call to RAISE_APPLICATION_ERROR. That error, along with its message, is propagated back to the client-side application.
EX : RAISE_APPLICATION_ERROR (error_number, error_message);
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.
Syntax:
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)
Examples:
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;
User defined exceptions?
Define p_dept_desc =’Oracleapps4u’
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;
No comments:
Post a Comment