PL/SQL exceptions

PL/SQL exceptions

The pre-defined exceptions and codes of PL/SQL are as follows:
Exception Oracle error sqlcode Value Meaning
Access_into_null ORA-06530-6530 the referenced object is assigned directly to the object without Initialization
Case_not_found ORA-06592-6592 if no case in the when clause contains no required branch and no else clause
Collection_is_null ORA-06531-6531 set element (nested table or varray) must be initialized first, if not, this error is triggered
Cursor_already_open ORA-06511-6511 reopen the opened cursor
Dup_val_on_index ORA-00001-1 when you type duplicate values in a unique index Column
Invalid_cursor ORA-01001-1001 when attempting to operate on an invalid cursor
Invalid_number ORA-01722-1722 embedded SQL statements cannot effectively convert characters into numbers, such as 100 written as 1OO
Login_denied ORA-01017-1017 when executing a PL/SQL program, You need to log on to Oracle, while the provided user password is incorrect
No_data_found ORA-01403 + 100 when the select into operation does not return rows
Not_logged_on ORA-01012-1012 was not logged on to the database when the PLSQL application was executed
Program_error ORA-06501-6501 indicates a problem in PL/SQL
Rowtype_mismatch ORA-06504-6504 when assigning values, the type returned by the host cursor variable and PL/SQL cursor variable does not match
Self_is_null ORA-30625-30625 when using an object type, when calling a member on a null instance (must be initialized first)
Storage_error ORA-06500-6500 PL/SQL block run when memory is insufficient or memory is corrupted
Subscript_beyond_count ORA-06533-6533 when using a nested table or varray, its element subscript is out of the element range
Subscript_outside_limit ORA-06532-6532 when using a nested table or varray, its element subscript is assigned a value
When sys_invalid_rowid ORA-01410-1410 converts a string to rowid, a valid string must be used
Timeout_on_resource ORA-00051-51 wait timeout
Too_many_rows ORA-01422-1422 when the select into operation returns multiple rows
Value_error ORA-06502-6502 when assigning values to PL/SQL blocks, variable lengths are insufficient to accommodate actual data
Zero_divide ORA-01476-1476 when using PL/SQL blocks, use numbers except 0

For non-defined exceptions:
For custom exceptions, you can define Pragma exception_init to define the defined exception name and exception sqlcode.
Declare
E_exception exception;
Pragma exception_init (e_exception,-2291 );
E_no_employ exception;
Begin
Update EMP set deptno = & DNO where empno = & Eno;
If SQL % notfound then
Raise e_no_employ;
End if;
Exception
When e_exception then
Dbms_output.put_line ('this department does not exist ');
When e_no_employ then
Dbms_output.put_line ('this employee does not exist ');
End;

Use of exceptional functions:
1)SqlcodeThe function is used to obtain the Oracle error number;
2)SqlerrmThe function is used to obtain the relevant error message;
3) You can use raise_application_error in Stored Procedures, functions, and packages to customize error numbers and error messages.1. sqlcode and sqlerrm

Sqlcode is used to return the Oracle error code, and sqlerrm is used to return the error message corresponding to the error code. To handle other unexpected Oracle errors in PL/SQL applications, You can reference these two functions after the when others clause in the Exception Handling Section to obtain related Oracle errors. Example:
UNDEF v_sal
Declare
V_ename EMP. ename % type;
Begin
Select ename into v_ename from EMP where sal = & v_sal;
Dbms_output.put_line ('employee name: '| v_ename );
Exception
When no_data_found then
Dbms_output.put_line ('employee not found: '| & v_sal |' employee ');
When others then
Dbms_output.put_line ('error code: '| sqlcode );
Dbms_output.put_line (sqlerrm );
End;

2. raise_application_error
This process is used to customize error messages in PL/SQL applications. Note that this process can only be used in database subprograms (processes, functions, packages, and triggers), but not in anonymous blocks and
Used in the sub-Program of the client.
The raise_application_error syntax is as follows:
Raise_application_error (error_number, message [, {true | false}]);
Where:
Error_number indicates a defined error number, which must be a negative integer between-20000 and-20999;
Message is used to specify an error message. The length cannot exceed 2048 bytes;
The third parameter is an optional parameter. If it is true, the error will be placed in the previous error stack. If it is set to false (default), all previous errors will be replaced.
Create or replace procedure raise_comm (ENO number, Commission number) is
V_comm EMP. Comm % type;
Begin
Select comm into v_comm from EMP where empno = Eno;
If v_comm is null then
Raise_application_error (-20001, 'this employee has no subsidization ');
End if;
Exception
When no_data_found then
Dbms_output.put_line ('this employee does not exist ');
End;
/* 2011-6-2 add exceptions

 How to catch exceptions in a loop and continue execution-- Solution: add another block in the loop, as shown in the following code:

If the exception is placed directly at the end, the record that encounters an error jumps out and cannot be executed.

Create or replace procedure update_district is
Type v_fid_varray is table of l_interest_s.g3e_fid % type;
V_fid v_fid_varray;
V_name varchar2 (10 );
Cursor fid_cur is
Select g3e_fid
From gc_netelem
Where g 3e_fno = 15000
And district is null;
Begin
Open fid_cur;
Loop
Fetch fid_cur bulk collect
Into v_fid limit 10000;
If v_fid is not null and v_fid.count> 0 then
For I in v_fid.first... v_fid.last Loop
Begin
Select a. Name
Into v_name
From p_region_s A, l_interest_s B
Where sdo_relate (A. gwm_geometry,
B. gwm_geometry,
'Mask = anyinteract ') = 'true'
And B. g3e_fid = v_fid (I );
Exception
When no_data_found then
V_name: = NULL;
Update gc_netelem t
Set T. District = v_name,
T. district_id = pkg_public.f_getdistinctid (v_name ,'')
Where T. g3e_fid = v_fid (I );
End;
End loop;
Commit;
End if;
Exit when fid_cur % notfound;
End loop;
Close fid_cur;
End;

*/

 

 

 

PLSQL compilation warning
1. PLSQL warning Classification
1) severe: this warning is used to check possible unexpected results or error results, such as parameter alias problems.
2) Performance: This type of warning is used to check possible performance problems. For example, varchar2 type data is provided for the number column during the insert operation.
3) Informational: This warning class is used to check the dead code in the subroutine.
4) All: This keyword is used to check all warnings (severe, performance, informational ).
2. Control PL/SQL warning information
Whether the preceding warning types are displayed during compilation, which is controlled by the plsql_warnings parameter. This initialization parameter can be set at the system level or session level, or in the alter procedure command.
Alter system set plsql_warnings = 'Enable: all ';
Alter session set plsql_warnings = 'Enable: Performance ';
Alter procedure Hello compile plsql_warnings = 'Enable: Performance ';
Alter session set plsql_warnings = 'Disable: all ';
Alter session set plsql_warnings = 'Enable: severe', 'Disable: Performance ', 'error: 06002 ';
Activate or disable PL/SQL compilation warnings. You can also use the PL/SQL System package dbms_warnings.
Call dbms_warning.set_warning_setting_string ('Enable: all', 'session ');
 3. Compile warning using PL/SQL
1) detect dead code
-- The following is a process containing dead code (Dead Code refers to the code that is never executed in the Program)
Create or replace procedure dead_code
X number: 10;
Begin
If X = 10 then
X: 20;
Else
X: 100; -- else will never execute
End if;
End dad_code;
To check whether the subroutine contains dead code, you must activate the warning check, re-compile the subroutine, and use the show errors command to display a warning error.
Alter session set plsql_warnings = 'Enable: ionional ';
Alter procedure dead_code compile;
Show errors;

2) code for detecting performance problems
Create or replace procedure update_sal (name varchar2, salary varchar2) is
Begin
Update EMP set sal = salary where ename = Name;
End;
SQL> alter session set plsql_warnings = 'Enable: Performance ';
Session altered
SQL> alter procedure update_sal compile;
Procedure altered
SQL> show errors;
Errors for procedure Scott. update_sal:
Line/COL Error
-----------------------------------------------------------
3/26 PLW-07202: Binding type may cause conversion from column type to another type