Pl/SQL Block Problem: No Data Found Error

PL/SQL block problem: No data found error

When you are selecting INTO a variable and there are no records returned you should get a NO DATA FOUND error. I believe the correct way to write the above code would be to wrap the SELECT statement with it's own BEGIN/EXCEPTION/END block. Example:

...
v_final_grade NUMBER;
v_letter_grade CHAR(1);
BEGIN

    BEGIN
    SELECT final_grade
      INTO v_final_grade
      FROM enrollment
     WHERE student_id = v_student_id
       AND section_id = v_section_id;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_final_grade := NULL;
    END;

    CASE -- outer CASE
      WHEN v_final_grade IS NULL THEN
      ...

PL/SQL no data found exception handling

Try this:

PROCEDURE myproc AS
    NO_EMAIL_FOUND EXCEPTION;
    e EMAILS%ROWTYPE;
    x NUMBER;
BEGIN

    BEGIN
        --fetch data
        SELECT x INTO x FROM <...>;

        --fetch emails
        BEGIN
            SELECT * INTO e FROM emails WHERE routine='FRS_WEEKLY';
        EXCEPTION WHEN NO_DATA_FOUND THEN
            RAISE NO_EMAIL_FOUND;
        END;
        IF e.emails IS NULL AND e.copies IS NULL THEN
            RAISE NO_EMAIL_FOUND;
        END IF;

        <send mail code using UTL_MAIL>;

    EXCEPTION
        WHEN NO_EMAIL_FOUND THEN <code1>;
        WHEN NO_DATA_FOUND THEN <code2>;
        WHEN OTHERS THEN NULL; --RAISE;
    END;

END myproc;

PL/SQL - No Data Found error

Oracle, when sees No data for the Selected query, and you attempt to put it into a variable, will end up throwing an Exception. Because, the INTO variable now is left untouched, and the developer had to handle it accordingly !

DECLARE
  TYPE dept_table_type IS TABLE OF departments.department_name%TYPE
  INDEX BY PLS_INTEGER;
  my_dept_table dept_table_type;
  f_loop_count NUMBER(2) :=10;
  v_deptno NUMBER(5) :=0;
BEGIN
  FOR i IN 1..f_loop_count LOOP
      v_deptno := v_deptno + 10;
      BEGIN
        SELECT department_name
        INTO my_dept_table(i)
        FROM departments
        WHERE department_id = v_deptno;
      EXCEPTION WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('No Data for ' || v_deptno);
         my_dept_table(i) := NULL;
         --Flow continues
      END;
  END LOOP;
  FOR i IN 1..f_loop_count LOOP
    DBMS_OUTPUT.PUT_LINE(my_dept_table(i));
  END LOOP;
END;
/

PL/SQL no data found

in your code you didn't assign any values to variable v_ename
what you need is add some logic to assign it.

additionally you need check that the values exists in your table or instead of the check catch exception.

your example with this modifications below:

SET VERIFY OFF
DECLARE
   v_ename    newemp.ename%TYPE; 
   v_sal      newemp.sal%TYPE;
   v_newgoal  newemp.sal%TYPE;
   v_deptno   newemp.deptno%TYPE;
BEGIN
   --assign some value
   v_ename := null; --if null - it updates all records in table

   for rc in (SELECT ename, sal, deptno
     FROM newemp
     WHERE ename = nvl(v_ename, ename)) loop

   IF rc.deptno = 10 then 
           v_newgoal := rc.sal * 1.05;
         else  
          if rc.deptno = 20 then 
             v_newgoal := rc.sal * 1.075;
           else 
             v_newgoal := rc.sal * 1.1;
           END IF;
   END IF;

   UPDATE newemp
     SET sal = v_newgoal
   WHERE ename = rc.ename;

   end loop;

COMMIT;
END;
/
SET VERIFY ON

same logic but more simple code

declare
  v_ename    newemp.ename%TYPE;
begin
  v_ename := 'employee_name'; --use null if you need update all employees
UPDATE newemp
   SET sal = 
     case 
       when v_deptno = 10 then sal * 1.05
       when v_deptno = 20 then sal * 1.075
       else sal * 1.1     
     end          
   WHERE ename = nvl(v_ename,ename);
end;   

Oracle PL/SQL - Are NO_DATA_FOUND Exceptions bad for stored procedure performance?

I would not use an explicit cursor to do this. Steve F. no longer advises people to use explicit cursors when an implicit cursor could be used.

The method with count(*) is unsafe. If another session deletes the row that met the condition after the line with the count(*), and before the line with the select ... into, the code will throw an exception that will not get handled.

The second version from the original post does not have this problem, and it is generally preferred.

That said, there is a minor overhead using the exception, and if you are 100% sure the data will not change, you can use the count(*), but I recommend against it.

I ran these benchmarks on Oracle 10.2.0.1 on 32 bit Windows. I am only looking at elapsed time. There are other test harnesses that can give more details (such as latch counts and memory used).

SQL>create table t (NEEDED_FIELD number, COND number);

Table created.

SQL>insert into t (NEEDED_FIELD, cond) values (1, 0);

1 row created.

declare
  otherVar  number;
  cnt number;
begin
  for i in 1 .. 50000 loop
     select count(*) into cnt from t where cond = 1;

     if (cnt = 1) then
       select NEEDED_FIELD INTO otherVar from t where cond = 1;
     else
       otherVar := 0;
     end if;
   end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.70

declare
  otherVar  number;
begin
  for i in 1 .. 50000 loop
     begin
       select NEEDED_FIELD INTO otherVar from t where cond = 1;
     exception
       when no_data_found then
         otherVar := 0;
     end;
   end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.06

---

Related Topics