PL/SQL Exception Handling Method

PL/SQL Exception Handling Method 1: What is Exception Handling? PL/SQL provides a function to handle exceptions, which is called Exception Handling in PL/SQL blocks, with exception handling, we can test the code and avoid abnormal exit. The PL/SQL exception information consists of three parts: 1: exception type 2: Error Code 3: handling error information we can ensure that PL/SQL blocks do not exit unexpectedly. 2: Structure of Exception handling DECLARE Declaration section begin exception section Exception WHEN ex_name1 THEN-Error handling statements WHEN ex_name2 THEN-Error handling statements WHEN Others THEN-Error handling statements END; example: when others can intercept all exceptions, usually at the end of exception handling. The above Code explains: when an exception occurs, oracle looks for an appropriate when exception type execution exception. A good example of Exception Handling: Except for 0: [SQL] SQL> DECLARE 2 stock_price NUMBER: = 9.73; 3 net_earnings NUMBER: = 0; 4 pe_ratio NUMBER; 5 BEGIN 6 -- Calculation might cause division-by-zero error. 7 pe_ratio: = stock_price/net_earnings; 8 DBMS_OUTPUT.PUT_LINE ('price/earnings ratio = '| pe_ratio); 9 EXCEPTION -- exception handlers begin 10 -- Only one of the WHEN blocks is executed. 11 WHEN ZERO_DIVIDE THEN -- handles 'Division by 0' error 12 DBMS_OUTPUT.PUT_LINE ('Company must have had zero earnings. '); 13 pe_ratio: = NULL; 14 when others then -- handles all other errors 15 DBMS_OUTPUT.PUT_LINE ('some other kind of error occurred. '); 16 pe_ratio: = NULL; 17 END; -- exception handlers and block end here 18/PL/SQL process completed successfully. SQL> set serveroutput on SQL>/Company must have had zero earnings. to avoid division by 0, you can do this: [SQL] DECLARE stock_price NUMBER: = 9.73; net_earnings NUMBER: = 0; pe_ratio NUMBER; BEGIN pe_ratio: = CASE net_earnings WHEN 0 then null else stock_price/net_earnings end; END;/if there is a nested PL/SQL block like this: DELCARE Declaration section begin declare Declaration section BEGIN Execution section EXCEPTION Exception section END; EXCEPTION Exception section END; in the preceding example: if an EXCEPTION occurs in an internal block, the internal Exception processing block should handle this EXCEPTION. If the internal processing block does not handle this Exception, the control will transfer the PL/SQL block at the upper level. If the upper level does not have the corresponding exception processing block, the program will end the error. 3: exception type: There are three types of exceptions: a: System naming exceptions B: System naming exceptions c: User-defined exceptions (this has never been used): system naming exception: when a program violates Relational Database Rules, oracle system exceptions automatically occur. Some system exceptions frequently occur, which are predefined exceptions, such exceptions have a name. For example, a system exception occurs when NO_DATA_FOUND and ZERO_DIVIDE both have names. Common Oracle Exception Name Oracle Error ExplanationDUP_VAL_ON_INDEX ORA-00001 You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index. TIMEOUT_ON_RESOURCE ORA-00051 You were waiting for a resource and you timed out. TRANSACTION_BACKED_OUT ORA-00061 The remote portion of a transaction has rolled back. INVALID_CURSOR ORA-01001 You tri Ed to reference a cursor that does not yet exist. this may have happened because you 've executed a FETCH cursor or CLOSE cursor before OPENing the cursor. NOT_LOGGED_ON ORA-01012 You tried to execute a call to Oracle before logging in. LOGIN_DENIED ORA-01017 You tried to log into Oracle with an invalid username/password combination. NO_DATA_FOUND ORA-01403 You tried one of the following: You executed A select into statement and no rows were returned. you referenced an uninitialized row in a table. you read past the end of file with the UTL_FILE package. TOO_MANY_ROWS ORA-01422 You tried to execute a select into statement and more than one row was returned. ZERO_DIVIDE ORA-01476 You tried to divide a number by zero. INVALID_NUMBER ORA-01722 You tried to execute an SQL statement that tried to convert A string to a number, but it was unsuccessful. STORAGE_ERROR ORA-06500 You ran out of memory or memory was upted. PROGRAM_ERROR ORA-06501. This is a generic "Contact Oracle support" message because an internal problem was encountered. VALUE_ERROR ORA-06502 You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data. CURS OR_ALREADY_OPEN ORA-06511 You tried to open a cursor that is already open. The above example shows how to use naming exceptions. I will not talk about it here. BEGIN Execution sectionEXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line ('a SELECT... INTO did not return any row. '); END; B: unnamed system exceptions: These system exceptions have no names, and these exceptions do not often occur. These exceptions have error code and association information. There are two ways to handle unnamed exceptions: Method 1: use when others to handle exceptions Method 2: Associate the Exception Code and name with an exception and use it like a naming exception. Method 1 has no targets. method 2 is described below: Use Pragma to call EXCEPTION_INIT to associate a predefined oracle error number with a program-defined EXCEPTION. Below is a demo: DECLARE exception_name EXCEPTION; PRAGMA EXCEPTION_INIT (exception_name, Err_code); BEGIN Execution sectionEXCEPTION WHEN exception_name THEN handle the exceptionEND; declare the Exception name and call the EXCEPTION_INIT function to bind the error number and Exception name, after binding, the exception name can be used as if it were a naming exception. For example: [SQL] SQL> DECLARE 2 e_insert_excep EXCEPTION; -- Define the EXCEPTION name 3 PRAGMA EXCEPTION_INIT (e_insert_excep,-01400 ); -- join Exception name and abnormal number 4 BEGIN 5 insert into orders ments (department_id, department_name) VALUES (280, n ull ); 6 EXCEPTION 7 WHEN e_insert_excep THEN 8 DBMS_OUTPUT.PUT_LINE ('insert OPERATION failed'); 9 DBMS_OUTPUT.PUT_LINE (SQLERRM); 10 END; 11/insert operation failed ORA-01400: NULL cannot be inserted ("HR ". "DEPA RTMENTS "." DEPARTMENT_NAME ") the PL/SQL process has been completed successfully. Define an e_insert_excep Exception name, call the EXCEPTION_INIT function to bind the Exception name, and output an error message. SQLERRM information: ORA-01400: cannot insert NULL ("HR ". "Administrative ments ". "DEPARTMENT_NAME") must be associated with numbers and custom error names; two variables: SQLCODE error code SQLERRM error content when an exception is thrown, these two variables will be automatically filled, these two values can be obtained to determine the error: 3: User-defined exception: first look at three examples and then explain: ex1: [SQL] SQL> DECLARE 2 v_deptno NUMBER: = 500; 3 v_name VARCHAR2 (20): = 'testing '; 4 e_invalid_department EXCEPTION; 5 BEGIN 6 UPDATE configurations SET department_name = v_name 7 WHERE department_id = v_deptno; 8 9 IF S QL % notfound then 10 RAISE e_invalid_department; 11 end if; 12 COMMIT; 13 EXCEPTION 14 WHEN e_invalid_department THEN 15 DBMS_OUTPUT.PUT_LINE ('no such department id'); 16 END; 17/No such department id PL/SQL process completed successfully. About the RAISE_APPLICATION_ERROR process. Syntax: raise_application_error (error_number, message [, {TRUE | FALSE}]). You can use this process to define your own exception number and exception information. Note: error_number is: -20000 .. -a number of 20999. message is a string with a maximum length of 2 k [SQL] SQL> DECLARE 2 v_deptno NUMBER: = 500; 3 v_name VARCHAR2 (20): = 'testing '; 4 e_invalid_department EXCEPTION; -- Define an EXCEPTION 5 PRAGMA EXCEPTION_INIT (e_invalid_department,-20188); -- bind the EXCEPTION to the EXCEPTION number; 6 BEGIN 7 UPDATE lifecycle ments 8 S ET department_name = v_name 9 WHERE department_id = v_deptno; 10 11 if SQL % notfound then 12 13 RAISE_APPLICATION_ERROR (-20188, 'I write my error message here! '); 14 end if; 15 COMMIT; 16 EXCEPTION 17 WHEN e_invalid_department THEN 18 DBMS_OUTPUT.PUT_LINE (SQLCODE |' ---> '| SQLERRM); 19 END; 20 21/-20188 ---> ORA-20188: I write my error message here! The PL/SQL process is successfully completed. Note: RAISE_APPLICATION_ERROR () RAISE_APPLICATION_ERROR is a built-in stored procedure that displays user-defined error messages and error numbers. These error numbers are open to developers in Oracle, the range is-20000 and-20999. When RAISE_APPLICATION_ERROR is used, the previous transactions are not committed and will be automatically rolled back. Syntax format: RAISE_APPLICATION_ERROR (error_number, error_message); Use RAISE_APPLICATION_ERROR to perform the following steps: 1: first define an exception in the Declaration area, 2: in a specific logic rule, the custom prediction3 of Raise occurs: catch this exception. After capturing the exception, use RAISE_APPLICATION_ERROR to link the custom error code and error information. Important: About the propagation mechanism of exceptions: Compare the following three examples to understand the propagation mechanism of exceptions: ex1: [SQL] CREATE OR REPLACE PROCEDURE add_more_administrative ments (p_name1 VARCHAR2, p_mgr1 NUMBER, p_loc1 NUMBER, p_name2 VARCHAR2, p_mgr2 NUMBER, p_loc2 NUMBER) is begin insert into values (VALUES, department_name, manager_id, location_id) VALUES (VALUES, p_name1, p_mgr1, p_loc1 ); DBMS_OUTPUT.PUT_LINE ('add Dept: '| p_name1); insert into orders (Department_id, department_name, manager_id, location_id) VALUES (VALUES, p_name2, p_mgr2, p_loc2); VALUES ('add Dept: '| p_name2); exception when others then evaluate ('err: adding dept: '); END;/create or replace procedure create_more_administrative ments is begin add_more_administrative ments ('Media', 1800, 'editing', 99,1800); END;/BEGIN create_more_administrative ments; END; in this example 99 this record already exists in the table. If you insert the record again, the execution result will conflict with the following: the two insert statements are inserted only first, and the second one fails to be inserted. The execution result is as follows: SQL>/Add Dept: MediaErr: adding dept: PL/SQL. DEPARTMENT_ID DEPARTMENT_NAME ------------- -------------------------------- 230 IT Helpdesk 240 Government Sales 250 Retail Sales 260 Recruiting 270 Payroll 340 Media has selected 28 rows. Only the first entry is inserted with ex2: delete the result of the previous experiment before doing the second example: delete from orders ments where department_id> 270; select department_id, department_name from orders ments order by 1; [SQL] CREATE OR REPLACE PROCEDURE tables (p_name1 VARCHAR2, p_mgr1 NUMBER, p_loc1 NUMBER, p_name2 VARCHAR2, p_mgr2 NUMBER, p_loc2 NUMBER) IS BEGIN INSERT INTO orders (tables, department_name, manager_id, location_id) VALUES (department VALUES, p_name1, p_mgr1, p_loc1); VALUES ('add Dept: '| p_name1); insert into values (department_id, department_name, manager_id, location_id) VALUES (VALUES, p_name2, p_mgr2, p_loc2); values ('add Dept: '| p_name2); END;/create or replace procedure create_more_administrative ments is begin add_more_administrative ments ('Media', 1800, 'editing ', 99,1800); END;/BEG IN create_more_statements; END; execution result: SQL> BEGIN 2 create_more_statements; 3 END; 4/Add Dept: MediaBEGIN * 1st Line Error: ORA-02291: violation of complete constraints (HR. DEPT_MGR_FK)-the parent keyword ORA-06512 is not found: In "HR. add_more_administrative ments ", line 6ORA-06512: In" HR. create_more_levels ", line 4ORA-06512: query result in line 2: DEPARTMENT_ID DEPARTMENT_NAME --------------- ------------------------------ 230 IT Helpdesk 240 Government Sales 25 0 Retail Sales 260 Recruiting 270 Payroll no exception handling this time, removing the exception handling. Result of this execution: neither of the two records is inserted. Ex3: delete from orders where department_id> 270; select department_id, department_name from orders ments order by 1; [SQL] CREATE OR REPLACE PROCEDURE add_more_orders ments (p_name1 VARCHAR2, p_mgr1 NUMBER, p_loc1 NUMBER, p_name2 VARCHAR2, p_mgr2 NUMBER, p_loc2 NUMBER) is begin insert into orders (department_id, department_name, manager_id, location_id) VALUES (departments_seq.NEXTVAL, p_name1, p_mgr1, P_loc1); VALUES ('add Dept: '| p_name1); insert into values (department_id, department_name, manager_id, location_id) VALUES (VALUES, p_name2, p_mgr2, p_loc2 ); DBMS_OUTPUT.PUT_LINE ('add Dept: '| p_name2); END;/create or replace procedure create_more_administrative ments is begin add_more_administrative ments ('Media', 100,1800, 'editing', 99,1800 ); exception when others then DBMS_OUTPUT. PUT_LINE ('errors have happend'); END;/BEGIN create_more_departments; END; execution result: SQL> BEGIN 2 create_more_departments; 3 END; 4/Add Dept: the MediaErrors have happend PL/SQL process has been completed successfully. Query Result: DEPARTMENT_ID DEPARTMENT_NAME ------------- wait 230 IT Helpdesk 240 Government Sales 250 Retail Sales 260 Recruiting 270 Payroll 400 Media puts Exception Handling on the outermost side of the call: The execution result is as follows: the first statement successfully executed is successfully inserted, and the second statement is captured.