Handling exceptions
This presentation is the property of its rightful owner.
Sponsored Links
1 / 25

Handling Exceptions PowerPoint PPT Presentation


  • 57 Views
  • Uploaded on
  • Presentation posted in: General

Handling Exceptions. Objectives. After completing this lesson, you should be able to do the following: Define PL/SQL exceptions Recognize unhandled exceptions List and use different types of PL/SQL exception handlers Trap unanticipated errors

Download Presentation

Handling Exceptions

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


Handling exceptions

Handling Exceptions


Objectives

Objectives

  • After completing this lesson, you should be able to do the following:

    • Define PL/SQL exceptions

    • Recognize unhandled exceptions

    • List and use different types of PL/SQL exception handlers

    • Trap unanticipated errors

    • Describe the effect of exception propagation in nested blocks

    • Customize PL/SQL exception messages


Handling exceptions with pl sql

Handling Exceptions with PL/SQL

  • What is an exception?Identifier in PL/SQL that is raised during execution

  • How is it raised?

    • An Oracle error occurs.

    • You raise it explicitly.

  • How do you handle it?

    • Trap it with a handler.

    • Propagate it to the calling environment.


Handling exceptions1

DECLARE

DECLARE

BEGIN

BEGIN

EXCEPTION

EXCEPTION

END;

END;

Handling Exceptions

Propagate the exception

  • Trap the exception

Exception is raised

Exception is raised

Exception

is trapped

Exception is

not trapped

Exception propagates to calling environment


Exception types

}

Implicitly raised

Exception Types

  • Predefined Oracle Server

  • Non-predefined Oracle Server

  • User-defined

Explicitly raised


Trapping exceptions

Trapping Exceptions

  • Syntax

EXCEPTION

WHEN exception1 [OR exception2 . . .] THEN

statement1;

statement2;

. . .

[WHEN exception3 [OR exception4 . . .] THEN

statement1;

statement2;

. . .]

[WHEN OTHERS THEN

statement1;

statement2;

. . .]


Trapping exceptions guidelines

Trapping Exceptions Guidelines

  • WHEN OTHERS is the last clause.

  • EXCEPTION keyword starts exception-handling section.

  • Several exception handlers are allowed.

  • Only one handler is processed before leaving the block.


Trapping predefined oracle server errors

Trapping Predefined Oracle Server Errors

  • Reference the standard name in the exception-handling routine.

  • Sample predefined exceptions:

    • NO_DATA_FOUND

    • TOO_MANY_ROWS

    • INVALID_CURSOR

    • ZERO_DIVIDE

    • DUP_VAL_ON_INDEX


When there is no data returned by row

When there is no data returned by row

  •  declare price item.actualprice%type; begin Select actual price into price from item where qty=888; when no-data-found then dbms_output.put_line('item missing'); end;


Predefined exception

Predefined Exception

  • Syntax

BEGIN

EXCEPTION

WHEN NO_DATA_FOUND THEN

statement1;

statement2;

WHEN TOO_MANY_ROWS THEN

statement1;

WHEN OTHERS THEN

statement1;

statement2;

statement3;

END;


Trapping non predefined oracle server errors

Declare

Associate

Reference

Trapping Non-Predefined Oracle Server Errors

Declarative section

Exception-handling

section

  • Name the exception

  • Code the PRAGMA EXCEPTION_INIT

  • Handle the raised exception


Non predefined error

e_emps_remaining EXCEPTION;

PRAGMA EXCEPTION_INIT (

e_emps_remaining, -2292);

e_emps_remaining

Non-Predefined Error

  • Trap for Oracle Server error number –2292, an integrity constraint violation.

DECLARE

e_emps_remainingEXCEPTION;

PRAGMA EXCEPTION_INIT (

e_emps_remaining, -2292);

v_deptno dept.deptno%TYPE := &p_deptno;

BEGIN

DELETE FROM dept

WHERE deptno = v_deptno;

COMMIT;

EXCEPTION

WHEN e_emps_remaining THEN

DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' ||

TO_CHAR(v_deptno) || '. Employees exist. ');

END;

1

2

3


Functions for trapping exceptions

Functions for Trapping Exceptions

  • SQLCODEReturns the numeric value for the error code

  • SQLERRMReturns the message associated with the error number


Functions for trapping exceptions1

SQLCODE

SQLERRM

Functions for Trapping Exceptions

DECLARE

v_error_code NUMBER;

v_error_message VARCHAR2(255);

BEGIN

...

EXCEPTION

...

WHEN OTHERS THEN

ROLLBACK;

v_error_code := SQLCODE ;

v_error_message := SQLERRM ;

INSERT INTO errors

VALUES(v_error_code, v_error_message);

END;

  • Example


Trapping user defined exceptions

Declare

Raise

Reference

  • Name the exception

  • Explicitly raise the exception by using the RAISE statement

  • Handle the raised exception

Trapping User-Defined Exceptions

Declarative

section

Executable

section

Exception-handling

section


User defined exception

e_invalid_product EXCEPTION;

RAISE e_invalid_product;

e_invalid_product

User-Defined Exception

Example

DECLARE

e_invalid_product EXCEPTION;

BEGIN

UPDATEproduct

SETdescrip = '&product_description'

WHEREprodid = &product_number;

IF SQL%NOTFOUND THEN

RAISE e_invalid_product;

END IF;

COMMIT;

EXCEPTION

WHEN e_invalid_product THEN

DBMS_OUTPUT.PUT_LINE('Invalid product number.');

END;

1

2

3


Example of user defined exception

EXAMPLE OF USER DEFINED EXCEPTION

  •  DECLARE  e_rec emp%ROWTYPE;  e1 EXCEPTION;  sal1 emp.sal%TYPE; BEGIN  SELECT sal INTO sal1 FROM emp WHERE deptno = 30 AND ename = 'John';  IF sal1 < 5000 THEN   RAISE e1;   sal1 := 8500;   UPDATE emp SET sal = sal1 WHERE deptno = 30 AND ename = 'John';  END IF;

  • EXCEPTION   WHEN no_data_found THEN    RAISE_APPLICATION_ERROR (-20001, 'John is not there.');   WHEN e1 THEN    RAISE_APPLICATION_ERROR (-20002, 'Less Salary.'); END;


Handling exceptions

INTERESTING EG OF USER DEFINED EXCEPTIONS Declare zero-price exception; price number(8); begin select actualprice into price from item where ordid =400; if price=0 or price is null then raise zero-price; end if; exception when zero-price then dbms_output.put_line('raised xero-price exception'); end;


Calling environments

Calling Environments

SQL*Plus

Procedure Builder

OracleDeveloperForms

Precompilerapplication

An enclosing PL/SQL block

Displays error number and message to screen

Displays error number and message to screen

Accesses error number and message in a trigger by means of the ERROR_CODE and ERROR_TEXT packaged functions

Accesses exception number throughthe SQLCA data structure

Traps exception in exception-handling routine of enclosing block


Propagating exceptions

Propagating Exceptions

DECLARE

. . .

e_no_rowsexception;

e_integrityexception;

PRAGMA EXCEPTION_INIT (e_integrity, -2292);

BEGIN

FOR c_record IN emp_cursor LOOP

BEGIN

SELECT ...

UPDATE ...

IF SQL%NOTFOUND THEN

RAISE e_no_rows;

END IF;

EXCEPTION

WHEN e_integrity THEN ...

WHEN e_no_rows THEN ...

END;

BEGIN

SELECT ...

UPDATE ...

IF SQL%NOTFOUND THEN

RAISE e_no_rows;

END IF;

EXCEPTION

WHEN e_integrity THEN ...

WHEN e_no_rows THEN ...

END;

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

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND THEN . . .

WHEN TOO_MANY_ROWS THEN . . .

END;


Raise application error procedure

RAISE_APPLICATION_ERRORProcedure

  • Syntax

    • A procedure that lets you issue user-defined error messages from stored subprograms

    • Called only from an executing stored subprogram

raise_application_error (error_number,

message[, {TRUE | FALSE}]);


Raise application error procedure1

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


Handling exceptions

EXAMPLE OF RAISE-APPLICATION-ERROR... THIS IS YOUR OWN ERROR STATEMENT...U RAISE --YOUR OWN ERROR Declare s1 emp.sal %type; begin select sal into s1 from emp where ename='SOMDUTT'; if(no-data-found) then raise_application_error(-20001, 'somdutt is not there'); end if; if(s1 > 10000) then raise_application_error(-20002, 'somdutt is earing a lot'); end if; update emp set sal=sal+500 where ename='SOMDUTT'; end;


Summary

Summary

  • Exception types:

    • Predefined Oracle Server error

    • Non-predefined Oracle Server error

    • User-defined error

  • Exception trapping

  • Exception handling:

    • Trap the exception within the PL/SQL block.

    • Propagate the exception.


Practice overview

Practice Overview

  • Handling named exceptions

  • Creating and invoking user-defined exceptions


  • Login