No_data_Found

When Pl sql block detects an error during the  program execution
normal execution  stops and an exception block is raised.

It is an pl sql error occur is raised during program execution, either implicitly by oracle server or explicitly by your program.

Example 1. Write a  Pl sql Block that to find an Employee Number   using Exception.

DECLARE
  lNAME EMPLOYEES.LAST_NAME%TYPE;
  SAL  EMPLOYEES.SALARY%TYPE;
  ANNSAL SAL%TYPE;
BEGIN
  SELECT LAST_NAME,SALARY
  INTO   lNAME,SAL
  FROM   EMPLOYEES
  WHERE  EMPLOYEE_ID=&EMPNO;
  ANNSAL := SAL*12;
  DBMS_OUTPUT.PUT_LINE(lNAME||' GETS '||SAL||' ANNUALLY '||ANNSAL);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('NO SUCH EMPLOYEE...');
END;
/

Output:-

Enter value for empno: 100

old   9:   WHERE  EMPLOYEE_ID=&EMPNO;

new   9:   WHERE  EMPLOYEE_ID=100;

King GETS 24000 ANNUALLY 288000

PL/SQL procedure successfully completed.


The above program displays the name and address of a Employees Whose ID is given.
Since there is no Employees with ID value  100 in our database,
 the program raises the run-time exception NO_DATA_FOUND , which is caught  in EXCEPTION block.


Example :-  Exception Using Boolean Variables.

declare
   v_foundrows boolean := false;
begin
   for r in ( select * from sanemp where employee_id= &dno )
   loop
      dbms_output.put_line(r.last_name || ' ' || r.salary);
      v_foundrows := true;
   end loop;

   if not v_foundrows then
      raise no_data_found;
   end if;

exception
   when no_data_found then
      dbms_output.put_line('hello');
end;