CSIT 313 DB PROGRAMMING

EXCEPTION HANDLING

• In PL/SQL, an error condition is called an exception.

• An exception can be either – internally defined (by the run-time system)

or – user-defined.

Examples of System/Oracle exceptions

• ORA-22056 (value string is divided by zero) and

• ORA-27102 (out of memory).

• Some common oracle exceptions have predefined names, such as – ZERO_DIVIDE and STORAGE_ERROR.

• The other oracle exceptions can be given names at the declaration section.

User Exceptions

• You can define your own exceptions in the declarative part of any PL/SQL block, subprogram, or package.

• For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts.

• User-defined exceptions must be given names at the declaration section.

What happens when an error occurs?

• When an error occurs, an exception is raised. – Normal execution stops and control transfers

to the exception-handling part of the PL/SQL block or subprogram.

– Oracle/System exceptions are raised implicitly (automatically) by the run-time system.

– User-defined exceptions must be raised explicitly by RAISE statements

Named system exceptions

• Oracle can handle:– CURSOR_ALREADY_OPENED (sqlcode = -6511)– DUP_VAL_ON_INDEX (sqlcode = -1)– INVALID_CURSOR (sqlcode = -1001)– INVALID_NUMBER (sqlcode = -1722)– LOGIN_DENIED (sqlcode = -1017)– NO_DATA_FOUND (sqlcode = +100)– TOO_MANY_ROWS (sqlcode = -1422)– …etc…

• These are named in the ‘standard’ package in pl/sql.

To handle these exceptions explicitly:

• These exception names do not need to be declared.• To handle them explicitly, put a clause in the exception

section:EXCEPTIONWhen DUP_VAL_ON_INDEX dbms_output.put_line(‘record already there’);

When OTHERdbms_output.put_line(‘error

occurred’);END;

Unnamed system exceptions

• These errors are not pre-named, but have a number.

• They will be raised automatically by the RDBMS.• The EXCEPTION section handles them in the

WHEN OTHER clause.• To name an unnamed error in your application:

– Give the error a name using a PRAGMA, or compiler directive, called EXCEPTION_INIT.

– PL/SQL or RDBMS raise the error automatically.– Handle the error in a specially written WHEN clause

in the exception section.

Unnamed system exceptions

• We’ve all seen errors that Oracle throws at us:– ERROR: ORA=12170: TNS: Connect timeout

occurred – TNS Listener does not currently know of service

requested in connect descriptor

• Note: – All of these errors have an error number: – e.g. ORA = 12170 means that the connection timeout

occurred.

• These errors are RAISED automatically by the system, because they are system errors.

Example

DECLARE deadlock_detected EXCEPTION; PRAGMA EXCEPTION_INIT(deadlock_detected, -60); BEGIN ... -- Some operation that causes an ORA-00060 -- error (see locking) EXCEPTION WHEN deadlock_detected THEN -- handle the error END;

Named Programmer-Defined Exceptions

• Application-specific exceptions– E.g.

• Negative balance in account• Team cannot play against itself• Cannot stock a negative number of items

• Programmer can trap these errors and handle them.

• To do this:– Name the error– Check for the error and raise it– Handle the error in the EXCEPTION section

ExamplePROCEDURE calc_annual_sales

(company_id_in IN company.company_id%TYPE)IS no_sales_for_company EXCEPTION;BEGIN-- Code here to check the number of sales-- a company has made. If none:

raise no_sales_for_company;

-- any other code

EXCEPTION WHEN no_sales_for_company THEN dbms_output.put_line(company_id||’ has made no

sales’); WHEN other THEN rollback work;END;

Example

SET SERVEROUTPUT ONDECLARE lname VARCHAR2(15);BEGIN SELECT last_name INTO lname FROM employees WHERE first_name='John'; DBMS_OUTPUT.PUT_LINE ('John''s last name is : ' ||lname);END;/

Example

SET SERVEROUTPUT ONDECLARE lname VARCHAR2(15);BEGIN SELECT last_name INTO lname FROM employees WHERE first_name='John'; DBMS_OUTPUT.PUT_LINE ('John''s last name is : ' ||lname);EXCEPTION WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE (' Your select statement retrieved multiple rows. Consider using a cursor.');END;/

Handling Exceptions with PL/SQL

– An exception is an error PL/SQL that is raised during program execution.

– An exception can be raised:• Implicitly by the Oracle server

• Explicitly by the program

– An exception can be handled:• By trapping it with a handler

• By propagating it to the calling environment

Handling Exceptions

Exception raised

Is theexception trapped?

yes

Execute statementsin the EXCEPTION

section

Terminate gracefully

noTerminate

abruptly

Propagate the exception

Program

Exception Types

– Predefined Oracle Server– Non-predefined Oracle Server

– User-defined

} Implicitly raised

Explicitly raised

Trapping Exceptions

•Syntax:

EXCEPTION WHEN exception1 [OR exception2 . . .] THEN statement1; statement2; . . . [WHEN exception3 [OR exception4 . . .] THEN statement1; statement2; . . .] [WHEN OTHERS THEN statement1; statement2; . . .]

Guidelines for Trapping Exceptions

– The EXCEPTION keyword starts the exception handling section.

– Several exception handlers are allowed.– Only one handler is processed before leaving the

block.– WHEN OTHERS is the last clause.

Trapping Predefined Oracle Server Errors

– Reference the predefined name in the exception handling routine.

– Sample predefined exceptions: • NO_DATA_FOUND• TOO_MANY_ROWS• INVALID_CURSOR• ZERO_DIVIDE• DUP_VAL_ON_INDEX

Trapping Non-Predefined Oracle

Server Errors

Declarative section

Declare

Name theexception

Code PRAGMA EXCEPTION_INIT

EXCEPTION section

Handle the raised exception

Associate Reference

SET SERVEROUTPUT ON

DECLARE

insert_excep EXCEPTION;

PRAGMA EXCEPTION_INIT (insert_excep, -01400);

BEGIN

INSERT INTO departments (department_id, department_name) VALUES (280, NULL);

EXCEPTION

WHEN insert_excep THEN

DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED');

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;/

Non-Predefined Error•Trap Oracle server error number –01400, cannot insert NULL.

1

2

3

Functions for Trapping Exceptions

– SQLCODE: Returns the numeric value for the error code

– SQLERRM: Returns the message associated with the error number

Functions for Trapping Exceptions•Example:

DECLARE error_code NUMBER; error_message VARCHAR2(255);BEGIN...EXCEPTION... WHEN OTHERS THEN ROLLBACK; error_code := SQLCODE ; error_message := SQLERRM ;

INSERT INTO errors (e_user, e_date, error_code,

error_message) VALUES(USER,SYSDATE,error_code,

error_message);END;/

Trapping User-Defined Exceptions

Declarativesection

Name theexception.

Executablesection

Explicitly raise the exception by using the RAISE

statement.

Exception-handlingsection

Handle the raised exception.

Raise Reference Declare

Trapping User-Defined Exceptions

...ACCEPT deptno PROMPT 'Please enter the department number:' ACCEPT name PROMPT 'Please enter the department name:'DECLARE invalid_department EXCEPTION; name VARCHAR2(20):='&name'; deptno NUMBER :=&deptno;BEGIN UPDATE departments SET department_name = name WHERE department_id = deptno; IF SQL%NOTFOUND THEN RAISE invalid_department; END IF; COMMIT;EXCEPTION WHEN invalid_department THEN DBMS_OUTPUT.PUT_LINE('No such department id.');END;/

1

2

3

Propagating Exceptions in a Subblock

DECLARE . . . no_rows exception; integrity exception; PRAGMA EXCEPTION_INIT (integrity, -2292);BEGIN FOR c_record IN emp_cursor LOOP BEGIN SELECT ... UPDATE ... IF SQL%NOTFOUND THEN RAISE no_rows; END IF; END; END LOOP;EXCEPTION WHEN integrity THEN ... WHEN no_rows THEN ...END;/

Subblocks can handle an exception or pass the exception to the enclosing block.

The RAISE_APPLICATION_ERRORProcedure

•Syntax:

– You can use this procedure to issue user-defined error messages from stored subprograms.

– You can report errors to your application and avoid returning unhandled exceptions.

raise_application_error (error_number,message[, {TRUE | FALSE}]);

The RAISE_APPLICATION_ERRORProcedure

– Used in two different places:• Executable section• Exception section

– Returns error conditions to the user in a manner consistent with other Oracle server errors.

RAISE_APPLICATION_ERROR

BEGIN... DELETE FROM employees WHERE manager_id = v_mgr; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20202, 'This is not a valid manager'); END IF; ...

Executable section:

Exception section:...EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201, 'Manager is not a valid employee.');END;/