1 / 21

PL/SQL Exceptions

PL/SQL Exceptions. Session - III. ERROR HANDLING. Runtime errors arise from design faults, Coding mistakes , Hardware failures and many other sources. We can plan to handle certain kinds of errors meaningful to your program. Exception.

salvador-huerta
Download Presentation

PL/SQL 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. 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. PL/SQL Exceptions Session - III

  2. ERROR HANDLING • Runtime errors arise from design faults, Coding mistakes , Hardware failures and many other sources. • We can plan to handle certain kinds of errors meaningful to your program.

  3. Exception • In PL/SQL , warning or error message is called an Exception. • Exception can be Internally defined or User defined

  4. Types of Exceptions • Predefined Exceptions • User-defined Exceptions

  5. Predefined Exception An Internal exception is raised implicitly whenever your PL/SQL program violates an ORACLE rule or exceeds a System-dependent limit.

  6. Every ORACLE error has a number , but exceptions must be handled by name. • So PL/SQL predefines some common ORACLE errors as Exceptions.

  7. Exception Names • NO_DATA_FOUND • TOO_MANY_ROWS • VALUE_ERROR • CURSOR_ALREADY_OPEN • INVALID_NUMBER

  8. Exception Names • INVALID_CURSOR • STORAGE_ERROR • PROGRAM_ERROR • DUP_VAL_ON_INDEX • ZERO_DIVIDE

  9. No_Data_Found This exception will be raised if the PL/SQL select statement fetches no records

  10. Too_many_Rows This exception is raised when the PL/SQL select statement fetches more than One record

  11. Invalid Cursor This exception will fire when trying to fetch records or close the cursor which is not opened.

  12. Cursor_Already_Open This exception is raised when trying to the open the same cursor without closing it.

  13. Value_Error This exception will be raised if there is a data type mismatch or sizing error in the PL/SQL program.

  14. Invalid_Number This exception will be raised when improper conversion of data type takes place in the SQL.

  15. STORAGE_Error This exception will be raised only if there is a RAM Overflow. 99% this error will not occur

  16. No Data Found Declare enam emp.ename%type; Begin Select ename INTO enam from emp where empno=7368; Exception When No_Data_Found then Dbms_output.put_line(‘No Such Record’); End;

  17. Too_many_rows Declare enam emp.ename%type; Begin Select ename INTO enam from emp where empno In (7368,7369) ; Exception When too_many_rows then Dbms_output.put_line(‘More than 1 row fetched’); End;

  18. User- Defined Exceptions • PL/SQL lets you define exceptions of your own. • Must be declared and must be raised explicitly by RAISE Statement

  19. Declaring Exceptions • Can be declared only in the Declarative part of a PL/SQL block, Sub program. • Declare an Exception by a Name followed by the keyword EXCEPTION Declare first Exception; ename Varchar(15); Begin

  20. How Exceptions are Raised PredefinedException are Raised Implicitly by the Runtime. User-Defined must be raised Explicitly by RAISE Statement.

  21. Declare first Exception; ename Varchar(15); Begin Update emp set sal=sal+1000 where empno=90; if SQL%notfound then Raise first; End if; Exception When First then Dbms_output.put_line(‘no match ‘); End ;

More Related