handling exceptions
Download
Skip this Video
Download Presentation
Handling Exceptions

Loading in 2 Seconds...

play fullscreen
1 / 25

Handling Exceptions - PowerPoint PPT Presentation


  • 102 Views
  • Uploaded on

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

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 ' Handling Exceptions' - jalen


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
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_remaining EXCEPTION;

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

UPDATE product

SET descrip = \'&product_description\'

WHERE prodid = &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;
slide18

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

Oracle Developer Forms

Precompiler application

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_rows exception;

e_integrity exception;

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
slide23

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
ad