exception types
Download
Skip this Video
Download Presentation
Exception types

Loading in 2 Seconds...

play fullscreen
1 / 17

Exception types - PowerPoint PPT Presentation


  • 114 Views
  • Uploaded on

Exception types. In Oracle PL/SQL. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Exception types' - morela


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
exception types

Exception types

In Oracle PL/SQL

types of exceptions
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.
named system exceptions
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
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;

named programmer defined exceptions
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
example
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;

slide7
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;

unnamed system exceptions
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 exceptions9
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.
example10
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;

unnamed programmer defined exceptions
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.
pragma exception init
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.
pragma exception init13
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.
example14
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;

defining your own error messages
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.
raise application error
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.
ad