1 / 18

Exceptions Oracle Database PL/SQL 10g Programming

Learn about exception handling in Oracle PL/SQL, including declaring and raising exceptions, mapping predefined exceptions, and handling errors in your code.

vincentcoleman
Download Presentation

Exceptions Oracle Database PL/SQL 10g Programming

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ExceptionsOracle Database PL/SQL 10g Programming Chapter 7

  2. Exceptions • Exception Handling • Declaring Exceptions • Raising Exceptions • Handling Exceptions • Error Stack Management Oracle Database PL/SQL 10g Programming (Chapter 7)

  3. ExceptionsException Handling: Definition • Exceptions are failures in a programs compilation or execution. • Exceptions can be critical or non-critical failures, the former should stop execution of the program and undo changes while the latter may be recorded and examined later. Oracle Database PL/SQL 10g Programming (Chapter 7)

  4. ExceptionsException Handling: Error Types • Compile-time Errors • Raised errors due to syntax mistakes • Raised errors due to reserved word use • Run-time Errors • Raised by logical programming mistakes • Raised by unexpected data states Oracle Database PL/SQL 10g Programming (Chapter 7)

  5. ExceptionsDeclaring Exceptions: Types • Predefined Exceptions • Standard event exceptions are defined, like: • NO_DATA_FOUND • NOT_LOGGED_ON • TOO_MANY_ROWS • Package event exceptions are defined, like: • INVALID_DIRECTORY (DBMS_LOB package) • INCONSISTENT_TYPE (DBMS_SQL package) • User-defined Exceptions • Are defined in anonymous or named blocks • Are best placed in package specifications Oracle Database PL/SQL 10g Programming (Chapter 7)

  6. ExceptionsDeclaring Exceptions: Predefined • Predefined Exceptions • Have an assigned Oracle error number. • ORA-0001 • Have an assigned Oracle exception name. • DUP_VAL_ON_INDEX • Have an assigned Oracle description. • Unique constraint violated. Oracle Database PL/SQL 10g Programming (Chapter 7)

  7. ExceptionsDeclaring Exceptions: Syntax DECLARE user_defined_errorEXCEPTION; BEGIN … shown_in_later_example … END; / Oracle Database PL/SQL 10g Programming (Chapter 7)

  8. ExceptionsDeclaring Exceptions: Mapping Predefined • Mapping Predefined Exceptions • Declare an EXCEPTION variable. • Use the PRAGMA (precompiler instruction) EXCEPTION_INIT to map a user-defined EXCEPTION to a predefined error number. • Can increase readability of programs. Oracle Database PL/SQL 10g Programming (Chapter 7)

  9. ExceptionsDeclaring Exceptions: Mapping Syntax DECLARE user_defined_error EXCEPTION; PRAGMA EXCEPTION_INIT(user_defined_error,-1400); BEGIN -- Assuming NOT NULL constraints this raises an error. INSERT INTO a_table (id, name) VALUES (NULL,NULL); EXCEPTION WHEN user_defined_error THEN INSERT INTO a_log_table VALUES ('ORA-01400 against A_TABLE'); END; / Oracle Database PL/SQL 10g Programming (Chapter 7)

  10. ExceptionsRaising Exceptions: Types • Raising a user-defined exception: • Declare a variable as an EXCEPTION data type in the declaration section. • Call the RAISE command and variable name in the execution section. • User-defined exceptions are called explicitly as opposed to predefined exceptions that are raised implicitly. • Raising a user-defined application exception: • Declare and raise simultaneously an error in the execution section by using the: RAISE_APPLICATION_EXCEPTION() Oracle Database PL/SQL 10g Programming (Chapter 7)

  11. ExceptionsRaising Exceptions: Simple Syntax DECLARE user_defined_errorEXCEPTION; BEGIN IF condition_1 <> condition_2 THEN RAISE user_defined_error; END IF; EXCEPTION … shown_in_later_example … END; / Oracle Database PL/SQL 10g Programming (Chapter 7)

  12. ExceptionsRaising Exceptions: Application Syntax BEGIN IF 1 <> 2 THEN RAISE_APPLICATION_ERROR(-20001,'user message'); END IF; EXCEPTION … shown_in_later_example … END; / Oracle Database PL/SQL 10g Programming (Chapter 7)

  13. ExceptionsHandling Exceptions: Definition • The EXCEPTION section handles exceptions. • The WHENerror_name or OTHERS captures thrown exceptions. • Further handling can occur inside the WHEN block, like validating an error number to branch handling procedures. • The RETURN command can be used after handling the exception to return the line below where the error occurred. • Unhandled exceptions will raise the thrown exception to the calling program. Oracle Database PL/SQL 10g Programming (Chapter 7)

  14. ExceptionsHandling Exceptions: Simple Syntax DECLARE user_defined_errorEXCEPTION; BEGIN IF condition_1 <> condition_2 THEN RAISE user_defined_error; END IF; EXCEPTION WHEN user_defined_error THEN INSERT INTO a_log_table VALUES ('ORA-01400 against A_TABLE'); END; / Oracle Database PL/SQL 10g Programming (Chapter 7)

  15. ExceptionsHandling Exceptions: Application Syntax BEGIN IF 1 <> 2 THEN RAISE_APPLICATION_ERROR(-20001,'user message'); END IF; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -20001 THEN dbms_output.put_line('Expected.'); RETURN; ELSE RAISE_APPLICATION_ERROR(-20002,'Unanticipated!'); END IF; END; / Oracle Database PL/SQL 10g Programming (Chapter 7)

  16. ExceptionsError Stack Management: Definition • The error stack is the sequencing of errors from the triggering event to the calling block of code, which can be a SQL statement in SQL*Plus. • The DBMS_UTILITY package now has a FORMAT_ERROR_BACKTRACE procedure that enables you to capture the complete error stack. Oracle Database PL/SQL 10g Programming (Chapter 7)

  17. ExceptionsError Stack Management: Stack Dependency Oracle Database PL/SQL 10g Programming (Chapter 7)

  18. Summary • Exception Handling • Declaring Exceptions • Raising Exceptions • Handling Exceptions • Error Stack Management Oracle Database PL/SQL 10g Programming (Chapter 7)

More Related