CREATE OR replace PROCEDURE Ph 
IS 
  CURSOR c_ph IS 
    SELECT * 
    FROM   emp; 
  emp_rec emp%ROWTYPE; 
  mb      VARCHAR(40); 
  eid     NUMBER; 
BEGIN 
    OPEN c_ph; 

    LOOP 
        FETCH c_ph INTO emp_rec; 

        eid := emp_rec.employee_id; 

        mb := '+91 ' 
              ||Substr(emp_rec.phone_number, 1, 3) 
              ||'-' 
              ||Substr(emp_rec.phone_number, 5, 3) 
              ||'-' 
              ||Substr(emp_rec.phone_number, 9, 3); 

        UPDATE emp 
        SET    ph_new = mb 
        WHERE  employee_id = eid; 

        COMMIT; 

        exit WHEN c_ph%NOTFOUND; 

        CLOSE c_ph; 
    END LOOP; 
END; 

/ 

[oracle@localhost ~]$ sqlplus hr/hr

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 28 21:01:49 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create or replace view emp as select * from employees;

View created.

SQL> set term on echo on
SQL> create or replace procedure ph
is
cursor c_ph is select * from emp;
emp_rec emp%rowtype;
mb varchar(40);
eid number;
begin
open c_ph;
loop
fetch c_ph into emp_rec;
eid:=emp_rec.employee_id;
mb:='+91 '||substr(emp_rec.phone_number,1,3)||'-'||substr(emp_rec.phone_number,5,3)||'-'||substr(emp_rec.phone_number,9,3);
update emp set ph_new=mb where employee_id=eid;
commit;
exit when c_ph%notfound;
close c_ph;
end loop;
end;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19  

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE PH:

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/1     PL/SQL: SQL Statement ignored
13/16    PL/SQL: ORA-00904: "PH_NEW": invalid identifier
SQL> 

declare cursor ...
begin
  open cursor
  loop
    fetch
    do something
    exit when 
    close cursor          --> this is a culprit
  end loop
end