EXCEPTIONPL/SQL predefines some common Oracle errors as exceptions . Server raise error was handle by exception program.Exception - it is a problem that may arise during the execution of program. Exception Handling is the mechanism to handle run-time malfunctions. Difference between exception type :
Pre_defined error Non pre_defined error User_defined error
---------------------------- -------------------------- ---------------------------------
One of approximately 20 errors Any other Standard Oracle A condition that the developer
that occur most often in PLSQL server error. determines is abnormal.
code.
You need not declare these Any other Standard Oracle Declare in the declarative section
exceptions. server. and raise explicitly.
They are predefined by the
Oracle server and raised
implicitly.
 Predefined Exception :
Exception Error-code Description
--------------------- ------------ --------------------------------------------
ACCESS_INTO_NULL 06530 Assign value to uninitialized object
CASE_NOT_FOUND 06592 None of the choice in the CASE statement
COLLECTION_IS_NULL 06531 Applying uninitialized collection method
DUP_VAL_ON_INDEX 00001 Insert duplicate value in unique index
INVALID_CURSOR 01001 An illegal cursor operation
INVALID_NUMBER 01722 Conversion of character string to number is failed
LOGIN_DENIED 01017 Username or password is invalid.
NO_DATA_FOUND 01403 No rows available to select
NOT_LOGGED_ON 01012 A call without connected to database.
PROGRAM_ERROR 06501 PL/SQL block has an internal error
Exception Error-code Description
--------------------- ------------ ----------------------------------------------------------
ROWTYPE_MISMATCH 06504 An assignment statement have incompatible return type.
SELF_IS_NULL 30625 Invoke a method without object initialized.
STORAGE_ERROR 06500 PL/SQL run memory was corrupted.
SUBSCRIPT_BEYOND_COUNT 06533 Nested table/varray using an index number larger than element.
SUBSCRIPT_OUTSIDE_LIMIT 06532 Nested table/varray using an index number is outside legal range
CURSOR_ALREADY_OPEN 06511 To Open already opened cursor
SYS_INVALID_ROWID 01410 The character string does not represent a ROWID value
TOO_MANY_ROWS 01422 Single row select return multiple rows.
VALUE_ERROR 06502 An arithmetic conversion or size constraint error occurred
ZERO_DIVIDE 01476 To divide a number by Zero
Functions for trapping exceptions
Fun Function Description
------------------ ---------------------------------------------------
SQLCODE Returns the numeric values for the error code.
SQLERRM Returns the message associated with the error number
SQLCODE value Description ---------------- ---------------------------------- 0 No exception encountered 1 User-defined exceptions +100 No_DATA_FOUND exceptions. Negative number Another Oracle server error number. Predefined Exception : Predefined exceptions in PL/SQL are declared globally in a package called STANDARD.So, one does not need to declare these exceptions. Instead we can write handlers for these predefined exceptions using their standard names. Syntax : Declare Begin Exception Error handle End declare tesdb_no emp.empno%type; tesdb_name emp.ename%type; tesdb_sal emp.sal%type; begin_ _ select empno, ename, sal into tesdb_no, tesdb_name, test_sal from emp; dbms_output.put_line (tesdb_no||' '||tesdb_name||' '||tesdb sal); exception when too_many_rows then dbms_output.put_line(‘Fetch only one row'); when no_data_found then dbms_output.put_line(‘No data to show'); when others then dbms_output.put_line(‘Contact DBA'); end; / Fetch only one row PL/SQL procedure successfullyNon-Predefined Exception : Non-predefined Oracle server error, other PL/SQL errors (no name)Declare within the declarative section and allow the Oracle Server to raise them implicitly. (automatically). Syntax : declare variable exception; begin Exception error handle endNon-Predefined Exception :
SQL> declare
tesdb_exp exception;
pragma exception_init(tesdb_exp,-01722);
begin
insert into emp(empno,ename,sal)
values('rose',21,5000);
exception
when tesdb_exp then
dbms_output.put_line(‘Datatype Error');
end;
/
Datatype Error
PL/SQL procedure successfully completed.
User defined Exception : PL/SQL user defined exception to make your own exception.PL/SQL give you control to make your own exception base on oracle rules. User define exception must be declare yourself and RAISE statement to raise explicitly.Using raise_application_error we assign errmesg. Sequence 20001 to 29999. Syntax : declare variable exception; begin raise variable; Exception error handle endUser defined Exception : SQL> declare tesdb_exp exception; begin update emp set ename=‘Martin' where empno=10000; if sql%notfound then raise tesdb_exp; end if; exception when tesdb_exp then dbms_output.put_line(‘Conditional record not matched'); end; / Conditional record not matched PL/SQL procedure successfully completed.User defined Exception as Procedure : SQL> create or replace procedure tesdb_prn( v_id number, v_name varchar2, v_sal number) as begin if (v_sal < 5000) then raise_application_error (-20001,'the salary must be above the 8000'); else insert into tesdb_tab (id, name, sal) values(v_id, v_name, v_sal); end if; end; / Procedure created.User defined Exception as Procedure cont… Call the Procedure SQL> begin tesdb_prn (1,'scott',2000); end; / begin * ERROR at line 1: ORA-20001: the salary must be above the 5000 ORA-06512: at “TESDB.PRN", line 6 ORA-06512: at line 2Trapping an Exception : Create a table to trap another table SQL> create table tesdb_exp_trap( codd number(8), emessage varchar2(100), time_detail timestamp); Table created.To describe the table : SQL> desc sdbt_exp_trap; Name Null? Type ------------------------------ -------- ------------------- CODD NUMBER(8) EMESSAGE VARCHAR2(100) TIME_DETAIL TIMESTAMP(6) PL/SQLTrapping an Exception :
SQL> declare
a emp.empno%type;
b emp.ename%type;
c emp.sal%type;
e1 exp_trap.codd%type;
e2 exp_trap.emessage%type;
e3 exp_trap.time_detail%type;
begin
select empno, ename, sal into a, b, c
from emp where sal < 3000;
dbms_output.put_line(a||' '||b||' '||c);
exception
when no_data_found then
dbms_output.put_line
('the given specific value not found');
when cursor_already_open then
dbms_output.put_line
('cursor already open so close it');
Create a program to trap the error into the table.
PL/SQL
when others then rollback;
e1 :=sqlcode;
e2 :=sqlerrm;
e3 :=systimestamp;
insert into tesdb_exp_trap values(e1,e2,e3);
end;
PL/SQL procedure successfully completed.
The error information stored into the table.
SQL> select * from tesdb_exp_trap;
CODD EMESSAGE TIME_ DETAIL
-1422 ORA-01422: exact fetch return 06-MAR-12 02.59.47.673199 AM
more than requested number of rows
« Previous Next Topic » (PL/SQL - Database Triggers) |