Oracle exception examples

Oracle exception examples

This example describes Oracle exception usage. We will share this with you for your reference. The details are as follows:

I. Exception Classification

Oracle classifies exceptions into predefined exceptions, non-predefined exceptions, and custom exceptions.

1) pre-defined exceptions are used to handle common oracle errors.

2) Non-pre-defined exceptions are used to process exceptions that cannot be handled by pre-defined exceptions.

3) custom exceptions are used to handle other situations unrelated to oracle errors.

The following is a small case to show what will happen if exceptions are not handled?

Write a stored procedure that receives employee numbers and displays the employee's name.

The SQL code is as follows:

Set serveroutput on; DECLARE V_ENAME EMP. ENAME % TYPE; begin select ename into V_ENAME from emp where empno = & GNO; DBMS_OUTPUT.PUT_LINE ('name: '| V_ENAME); END ;/

Enter a nonexistent ID. Press enter to throw the following exception:

ORA-01403: No data found
ORA-06512: In line 6

The SQL code captured with exceptions is as follows:

Set serveroutput on; DECLARE V_ENAME EMP. ENAME % TYPE; begin select ename into V_ENAME from emp where empno = & GNO; DBMS_OUTPUT.PUT_LINE ('name: '| V_ENAME); exception when no_data_found THEN DBMS_OUTPUT.PUT_LINE (' No! '); END ;/

Enter a non-existent number. Press enter and a friendly prompt will be displayed: The number is not found!

Ii. handling pre-defined exceptions

Predefined exceptions are system exceptions provided by pl/SQL. When a pl/SQL application violates oracle restrictions, an internal exception is implicitly triggered. Pl/SQL provides more than 20 predefined exceptions for developers. We will introduce common exceptions.

1). pre-defined exceptions of case_not_found

When compiling case statements in pl/SQL blocks, if the when clause does not contain necessary condition branches, the case_not_found exception is triggered:

Set serveroutput on; create or replace procedure SP_PRO6 (spno number) IS V_SAL EMP. SAL % TYPE; begin select sal into V_SAL from emp where empno = SPNO; case when V_SAL <1000 then update emp set sal = SAL + 100 where empno = SPNO; WHEN V_SAL <2000 then update emp set sal = SAL + 200 where empno = SPNO; end case; exception when CASE_NOT_FOUND THEN DBMS_OUTPUT.PUT_LINE ('case statement does not match '| V_SAL |' matched condition '); END;/-- call Stored Procedure SQL> EXEC SP_PRO6 (7369 );

The case statement does not match 4444.

2) pre-defined exceptions of cursor_already_open

When you reopen an opened cursor, The cursor_already_open exception is implicitly triggered.

Declare cursor EMP_CURSOR is select ename, sal from emp; begin open EMP_CURSOR; -- the cursor is opened when it IS declared, so it IS not necessary to open for EMP_RECORD1 IN EMP_CURSOR LOOP values (EMP_RECORD1.ENAME); end loop; exception when CURSOR_ALREADY_OPEN THEN DBMS_OUTPUT.PUT_LINE ('cursor opened '); END ;/

3) pre-defined exception of dup_val_on_index

When duplicate values are inserted in the columns corresponding to a unique index, the exception is implicitly triggered.

Begin insert into dept values (10, 'public relations ', 'beijing'); exception when DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE ('repeated VALUES cannot appear in the deptno column'); END ;/

4). invalid_cursorn pre-defined exceptions

This exception is triggered when an operation is attempted on an invalid cursor.

For example, you can try to extract data from an unopened cursor or close a unopened cursor. This exception is triggered.

Declare cursor EMP_CURSOR is select ename, sal from emp; EMP_RECORD EMP_CURSOR % ROWTYPE; BEGIN -- open emp_cursor; -- open the cursor fetch EMP_CURSOR INTO EMP_RECORD; CURSOR (CURSOR); CLOSE EMP_CURSOR; exception when INVALID_CURSOR THEN DBMS_OUTPUT.PUT_LINE ('check whether the cursor is opened '); END ;/

5). invalid_number pre-defined exceptions

This exception is triggered when the input data is incorrect.

For example, if the number 100 is written as loo, this exception is triggered.

Set serveroutput on; begin update emp set sal = SAL + 'aaa'; exception when INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE ('incorrect number entered '); END ;/

6) no_data_found pre-defined exceptions

The following is a pl/SQL block. When select into is executed, the exception is triggered.

SET serveroutput ON; DECLARE V_SAL EMP. SAL % TYPE; begin select sal into V_SAL from emp where ename = 'ljq'; exception when NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('this employee does not exist '); END ;/

7). too_many_rows pre-defined exceptions

When the select into statement is executed, if more than one row is returned, this exception is triggered.

DECLARE V_ENAME EMP. ENAME % TYPE; begin select ename into V_ENAME from emp; exception when TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE ('multiple rows returned'); END ;/

8) zero_divide pre-defined exceptions

This exception is triggered when the 2/0 statement is executed.

9) value_error pre-defined exceptions

When the value assignment operation is performed, if the variable length is insufficient to accommodate actual data, the exception value_error will be triggered.

Other pre-defined exceptions (these exceptions are not triggered in pl/SQL, but are triggered when oracle is used, so they are called other pre-defined exceptions)

1. login_denied

This exception is triggered when a user logs on illegally.

2. not_logged_on

This exception is triggered if you do not log on to dml.

3. storage_error

This exception is triggered if the memory space is exceeded or the memory is damaged.

4. timeout_on_resource

This exception is triggered if oracle times out while waiting for resources.

Iii. Non-predefined exceptions

Non-pre-defined exceptions are used to handle oracle errors unrelated to the pre-defined exceptions. With a predefined exception, only 21 oracle errors can be handled. When using pl/SQL to develop applications, other oracle errors may occur. For example, when you execute dml statements in pl/SQL blocks, the constraints are violated. In this case, you can also handle oracle exceptions, because there are not many non-predefined exceptions, so I will not give an example here.

4. Handling custom exceptions

Pre-defined and custom exceptions are related to oracle errors, and oracle errors implicitly trigger exceptions. Custom exceptions are not associated with oracle errors, it is an exception defined by developers for specific situations.

Question: Please write a pl/SQL block to receive an employee's number and increase the employee's salary by 1000 yuan. If the employee does not exist, please prompt.

Create or replace procedure EX_TEST (spno number) isbegin update emp set sal = SAL + 1000 where empno = SPNO; END;/-- call the stored PROCEDURE, EXEC EX_TEST (56 );

Here, the number 56 does not exist. The exception was reported just now. Why not report the exception now?
This is because the select statement is used.

How can this problem be solved? Modify the Code as follows:

-- Custom exception create or replace procedure EX_TEST (spno number) IS -- defines an exception myex exception; BEGIN -- updates the user salUPDATE emp set sal = SAL + 1000 where empno = SPNO; -- SQL % notfound this indicates no update -- raise myex; trigger myexIF SQL % NOTFOUND THEN RAISE MYEX; END IF; EXCEPTIONWHEN MYEX THEN DBMS_OUTPUT.PUT_LINE ('no user updated '); END ;/

Now test again:

SQL> exec ex_test(56);

No user updated

I hope this article will help you with Oracle database programming.

Articles you may be interested in:

• Oracle trigger usage example
• Oracle automatic backup script
• Steps for Oracle automatic backup and automatic backup
• Two methods for creating Database Link in Oracle
• Oracle Variable Length array varray, nested table, set usage
• Oracle RETURNING clause usage
• How to create an index in an Oracle database
• Oracle Database foreign key operations