Oracle/PLSQL: How to avoid ORA-01403: "No data found" error and it's alternet solution

Most of us in our early days of learning pl/sql face the typical error. This error happens when the query you are submitting does not find any data in the under lying table. This error sounds really simple but things get worse when this errors happens in production and there is no work around other then correct the error in corresponding package which may require database as well as system downtime.

I have seen some incidents in my carrier where this type of small mistakes resulted hash words from team leader.

So in pl/sql package my suggestion would be to avoid SQL like 

DECLARE

    v_empname  emp.empname%TYPE;

    v_ename       emp.ename%TYPE;

    v_job            emp.job%TYPE;

    v_mgr            emp.mgr%TYPE;

    BEGIN

          SELECT  empname,

                           ename,

                           job,

                           mgr

             FROM   emp

             INTO     v_empname,

                            v_ename,

                            v_job,

                            v_mgr

             WHERE empno='7156';   

END; 

Rather in pl/sql package it will be safe  if you write the above query like

DECLARE
    v_empname  emp.empname%TYPE;
    v_ename       emp.ename%TYPE;
    v_job            emp.job%TYPE;
    v_mgr            emp.mgr%TYPE;
    BEGIN
      BEGIN
        SELECT  empname,
                        ename,
                        job,
                        mgr
           FROM  emp         
           INTO    v_empname,
                         v_ename,
                         v_job,
                         v_mgr
           WHERE empno='7156';  
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
           NULL;  -- OR DO what ever you want to
         WHEN TOO_MANY_ROWS  THEN
           NULL;  -- OR DO what ever you want to
         WHEN OTHERS THEN
           NULL;  -- OR DO what ever you want to
      END;
      <-- OTHER SQL or PL/SQL Statement -->
    END;
END;


In my opinion it will be nice if you write every query in separate BEGIN--EXCEPTION-END section.
It reduces the chance of any unhanded exception.

There is another way to avoid this kind of situation. If you are planning to use the data you have retrived from the database you can also write code like this which is safe

FOR r_emp_rec IN ( SELECT   empname,
                                                   ename,
                                                    job,
                                                    mgr
                                       FROM  emp
                                       WHERE empno='7156';
                                 )
LOOP
      Dbms_Output.Put_Line(r_emp_rec.empname||'--'||r_emp_rec.ename||'--'||r_emp_rec.mgr);
END LOOP;



This explicit cursor will never execute if there does not exists any data in emp table against empno=7156.
So system will never encounter ORA-01403: No data found.