NO data found : Handle Exception « PL SQL Programming « Oracle PL/SQL Tutorial

Home
Oracle PL/SQL Tutorial
1.Introduction
2.Query Select
3.Set
4.Insert Update Delete
5.Sequences
6.Table
7.Table Joins
8.View
9.Index
10.SQL Data Types
11.Character String Functions
12.Aggregate Functions
13.Date Timestamp Functions
14.Numerical Math Functions
15.Conversion Functions
16.Analytical Functions
17.Miscellaneous Functions
18.Regular Expressions Functions
19.Statistical Functions
20.Linear Regression Functions
21.PL SQL Data Types
22.PL SQL Statements
23.PL SQL Operators
24.PL SQL Programming
25.Cursor
26.Collections
27.Function Procedure Packages
28.Trigger
29.SQL PLUS Session Environment
30.System Tables Data Dictionary
31.System Packages
32.Object Oriented
33.XML
34.Large Objects
35.Transaction
36.User Privilege
Oracle PL/SQL Tutorial » PL SQL Programming » Handle Exception 




SQL>
SQL>
SQL> create table employee (
  2  id                             number,
  3  employee_type_id               number,
  4  external_id                    varchar2(30),
  5  first_name                     varchar2(30),
  6  middle_name                    varchar2(30),
  7  last_name                      varchar2(30),
  8  name                           varchar2(100),
  9  birth_date                     date  ,
 10  gender_id                      number );

Table created.

SQL>
SQL>
SQL> create table gender (
  2  id                             number,
  3  code                           varchar2(30),
  4  description                    varchar2(80),
  5  active_date                    date          default SYSDATE  not null,
  6  inactive_date                  date );

Table created.

SQL>
SQL>
SQL>
SQL> insert into gender id, code, description values 1'F''Female' );

row created.

SQL> insert into gender id, code, description values 2'M''Male' );

row created.

SQL> insert into gender id, code, description values 3'U''Unknown' );

row created.

SQL>
SQL>
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
  2
  3  d_birth_date                          employee.birth_date%TYPE;
  4  n_gender_id                           employee.gender_id%TYPE;
  5  n_selected                            number := -1;
  6  n_id                                  employee.id%TYPE;
  7  v_first_name                          employee.first_name%TYPE;
  8  v_last_name                           employee.last_name%TYPE;
  9  v_middle_name                         employee.middle_name%TYPE;
 10  v_name                                employee.name%TYPE;
 11
 12  begin
 13    v_first_name  := 'JOHN';
 14    v_middle_name := 'J.';
 15    v_last_name   := 'DOUGH';
 16    v_name        := rtrim(v_last_name||', '||v_first_name||' '||v_middle_name);
 17    d_birth_date  := to_date('19800101', 'YYYYMMDD');
 18
 19    begin
 20      select id into n_gender_id from gender where code = 'M';
 21    exception
 22      when OTHERS then
 23        raise_application_error(-20001, SQLERRM||' oselect gender');
 24    end;
 25
 26    begin
 27      select id
 28      into   n_id
 29      from   employee
 30      where  name       = v_name
 31      and    birth_date = d_birth_date
 32      and    gender_id  = n_gender_id;
 33
 34      n_selected := sql%rowcount;
 35    exception
 36      when NO_DATA_FOUND then
 37        n_selected := sql%rowcount;
 38        DBMS_OUTPUT.PUT_LINE('Caught raised exception NO_DATA_FOUND');
 39      when OTHERS then
 40        raise_application_error(-20002, SQLERRM||' oselect employee');
 41    end;
 42
 43    DBMS_OUTPUT.PUT_LINE(to_char(n_selected)||' row(sselected.');
 44  end;
 45  /
Caught raised exception NO_DATA_FOUND
row(sselected.

PL/SQL procedure successfully completed.

SQL>
SQL> drop table gender;

Table dropped.

SQL>
SQL> drop table employee;

Table dropped.

SQL>














24.15.Handle Exception
24.15.1.Code with No Exception Handler
24.15.2.Code with Conditional Control to Avoid an Exception
24.15.3.Code with Explicit Handler for Predefined Exception
24.15.4.Handling an Unnamed Exception
24.15.5.Handling a custom exception
24.15.6.An example showing continuing program execution after handling exception
24.15.7.The OTHERS Exception Handler
24.15.8.Assigning a Name to Predefined Exception Code
24.15.9.Using SQLCODE for error code and SQLERRM for error message
24.15.10.Catch custom exception
24.15.11.Handling exceptions without halting the program
24.15.12.Select into statement with exception catch statement
24.15.13.Check OTHERS exception
24.15.14.Error message code and text
24.15.15.Using PRAGMA EXCEPTION_INIT
24.15.16.NO data found
24.15.17.TOO_MANY_ROWS Exception
24.15.18.Use a nested block to catch exceptions from singleton SELECT.
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.