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_idTYPE)
• 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.cordernotype,
• scode in builder.corderline.stock_codetype,
• qtyreq in builder.corderline.quantityrequiredty
  pe) as
• invalid_quantity exception
• begin
• if (qtyreq lt 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')

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

• Weve all seen errors that Oracle throws at us
• ERROR ORA12170 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
  Declare to server Tell compiler (pragma exception init) Raise Handle
Named System Errors x x x v
Named Application Errors v x v v
Unnamed System errors v v x v
Unnamed Application Errors v v v v