Oracle PL/SQL non-predefined exceptions, custom exception handling, RAISE_APPLICATION_ERROR

Oracle PL/SQL non-predefined exceptions, custom exception handling, RAISE_APPLICATION_ERROR throwing exceptions Oracle has three types of exception errors: 1. there are about 24 Predefined ORACLE exceptions. To handle this exception, you do not need to define it in the program. ORACLE will automatically throw it. 2. Non-Predefined (Predefined) exceptions are other standard ORACLE errors. To handle this exception, you need to define it in the program, and then the ORACLE will automatically cause it. 3. During the execution of a user-defined (User_define) exception program, exceptions are deemed by programmers. To handle this exception, you need to define it in the program and then explicitly raise it in the program. There are three methods to throw an exception in PL/SQL through the PL/SQL runtime engine -- throw an Oracle exception using the RAISE statement -- throw a user-defined exception to call the RAISE_APPLICATION_ERROR stored procedure -- throw a user-defined exception that is not predefined exception because a non-predefined exception only has a number, no name, so it cannot be processed directly. 1. Define exceptions in the definition of PL/SQL blocks: <exceptions> exceptions; 2. Associate the defined exceptions with standard ORACLE errors, use the prediction_init statement PRAGMA prediction_init (<exception situation>, <error code>); 3. handle exceptions in PL/SQL blocks. [SQL] INSERT INTO attributes VALUES (50, 'Finance ', 'Chicago'); DECLARE v_deptno attributes. department_id % TYPE: = & deptno; deptno_remaining EXCEPTION; -- 1. Define PRAGMA EXCEPTION_INIT (deptno_remaining,-2292 ); -- 2. Association ---2292 is an error code that violates consistency constraints. begin delete from orders WHERE department_id = v_deptno; exception when deptno_remaining THEN -- 3. Processing DBMS_OUTPUT.PUT_LINE ('violates data integrity constraints! '); When others then DBMS_OUTPUT.PUT_LINE (SQLCODE |' --- '| SQLERRM); END; user-defined exception handling user-defined exception errors are explicitly triggered using RAISE statements. 1. Define exceptions in the definition of PL/SQL blocks; 2. RAISE <exceptions>; 3. handle exceptions in PL/SQL blocks. [SQL] DECLARE v_empno employees. employee_id % TYPE: = & empno; no_result EXCEPTION; -- 1. Define begin update employees SET salary = salary + 100 WHERE employee_id = v_empno; if SQL % NOTFOUND THEN RAISE no_result; -- 2. Throw end if; exception when no_result THEN -- 3. Process DBMS_OUTPUT.PUT_LINE ('your data update statement has failed! '); When others then DBMS_OUTPUT.PUT_LINE (SQLCODE |' --- '| SQLERRM); END; RAISE_APPLICATION_ERROR calls the RAISE_APPLICATION_ERROR process defined in the DBMS_STANDARD (ORACLE package) package, you can redefine the error message and convert the application-specific errors from the server to the client application. It provides an Interactive Method for applications to interact with ORACLE. Syntax: RAISE_APPLICATION_ERROR (error_number, error_message, [keep_errors]); error_number is a parameter between-20,000 and-20,999, in this way, there will be no conflict with any error code in ORACLE, error_message is the corresponding prompt information (<2048 bytes), keep_errors is optional, if keep_errors = TRUE, the new error is added to the list of errors that have been thrown. If keep_errors = FALSE (default), the new error will replace the current error list. There are two uses for RAISE_APPLICATION_ERROR. the first is to replace generic Oracle exception messages with our own, more meaningful messages. the second is to create exception conditions of our own, when Oracle wocould not throw them. [SQL] create or replace procedure new_emp (p_name in emp. ename % type, p_sal in emp. sal % type, p_job in emp. job % type, p_dept in emp. deptno % type, p_mgr in emp. mgr % type, p_hired in emp. hiredate % type: = sysdate) is invalid_manager exception; -- 1. Define PRAGMA EXCEPTION_INIT (invalid_manager,-2291); -- 2. Associate dummy varchar2 (1 ); begin if trunc (p_hired)> trunc (sysdate) then raise_application_error (-20000, 'new _ EMP: hiredate cannot be in the ure '); -- 3. Throw a custom exception: end if; insert into emp (ename, sal, job, deptno, mgr, hiredate) values (p_name, p_sal, p_job, p_dept, p_mgr, trunc (p_hired); exception when dup_val_on_index then raise_application_error (-20001, 'new _ EMP: employee called' | p_name | 'already exists', true ); -- 3. Packaging Oracle exception when invalid_manager then raise_application_error (-20002, 'new _ EMP: '| p_mgr |' is not a valid manager '); -- 3. Wrap the custom exception end;/the client will prompt detailed exception information when calling: [SQL] -- test the RAISE_APPLICATION_ERROR custom exception SQL> exec new_emp ('duggan ', 2500, 'sales', 10,778 2, sysdate + 1) BEGIN new_emp ('duggan ', 2500, 'sales', 10,778 2, sysdate + 1); END; * ERROR at line 1: ORA-20000: NEW_EMP: hiredate cannot be in the future -- ORA-20000: at "APC. NEW_EMP ", line 16 ORA-06512: at line 1 -- Test RAISE_APPLICATION_ERROR wrap custom exception SQL> exec new_emp ('duggan ', 2500, 'sales', 10,888 8, sysdate) BEGIN new_emp ('duggan ', 2500, 'sales', 10,888 8, sysdate); END; * ERROR at line 1: ORA-20002: NEW_EMP: 8888 is not a valid manager ORA-06512: at "APC. NEW_EMP ", line 42 ORA-06512: at line 1 -- Test RAISE_APPLICATION_ERROR wrap Oracle exception SQL> exec new_emp ('duggan ', 2500, 'sales', 10,778 2, sysdate) PL/SQL procedure successfully completed. SQL> exec new_emp ('duggan ', 2500, 'sales', 10,778 2, sysdate) BEGIN new_emp ('duggan', 2500, 'sales', 10,778 2, sysdate); END; * ERROR at line 1: ORA-20001: NEW_EMP: employee called DUGGAN already exists ORA-06512: at "APC. NEW_EMP ", line 37 ORA-00001: unique constraint (APC. EMP_UK) violated -- print the original stack ORA-06512 simultaneously: at line 1