Exception types

1. Exception types In Oracle PL/SQL

2. Types of exceptions • Named system exceptions • Raised as a result of an error in PL/SQL or RDBMS processing. • Named programmer-defined exceptions • Raised as a result of errors expected in the application code. • Unnamed system exceptions • Raised as a result of an error in PL/SQL or RDBMS processing, with codes, but no names. • Unnamed programmer-defined exceptions. • These are raised in the server by the programmer.

3. 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.

4. 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: EXCEPTION When DUP_VAL_ON_INDEX dbms_output.put_line(‘record already there’); When OTHER dbms_output.put_line(‘error occurred’); END;

5. 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

6. Example PROCEDURE 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;

7. create or replace procedure add_corderline (onum in builder.corderline.corderno%type, scode in builder.corderline.stock_code%type, qtyreq in builder.corderline.quantityrequired%type) as invalid_quantity exception; begin if (qtyreq <= 0) then raise invalid_quantity; end if; insert into corderline values(qtyreq, onum, scode); exception when dup_val_on_index then dbms_output.put_line('primary key violation'); dbms_output.put_line(sqlcode||'--'|| sqlerrm); when invalid_quantity then dbms_output.put_line('quantity is invalid'); when others then dbms_output.put_line('unexpected error'); dbms_output.put_line(sqlcode||'--'|| sqlerrm); end;

8. 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.

9. 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.

10. 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;

11. Unnamed Programmer-Defined Exceptions • This is where the server (i.e. PL/SQL code) has declared and raised an application error and wants the client to be able to recognise it. • When the server encounters an error, it returns the error code to the client. • The client must declare the exception name and check for it after control has returned from the server.

12. Pragma EXCEPTION_INIT • To handle error conditions that have no predefined name, you must use • the OTHERS handler • or • the pragma EXCEPTION_INIT. • A pragma • is a compiler directive that is processed at compile time, not at run time. • In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. • That lets you refer to any internal exception by name and to write a specific handler for it. • When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.

13. Pragma exception_init • Coding • You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number); • where exception_name is the name of a previously declared exception • and the number is a negative value corresponding to an ORA- error number. • The pragma must appear somewhere after the exception declaration in the same declarative section.

14. 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 WHEN OTHER -- handle errors END;

15. Defining Your Own Error Messages: • Procedure RAISE_APPLICATION_ERROR • lets you issue user-defined ORA- error messages from stored subprograms. • That way, you can report errors to your calling application and avoid returning unhandled exceptions.

16. Raise_application_error • To call RAISE_APPLICATION_ERROR: • raise_application_error(error_number, message[, {TRUE | FALSE}]); • where error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048 bytes long. • If the optional third parameter is TRUE, • the error is placed on the stack of previous errors. • If the parameter is FALSE (the default), • the error replaces all previous errors.

17. How to handle errors