SQL> declare
  2    emp_rowtype scott.emp%rowtype;
  3    empno_type  scott.emp.empno%type;
  4  begin
  5    begin
  6  
  7        dbms_output.put_line(' Initial value ' || empno_type);
  8      select e.empno into empno_type from scott.emp e where e.deptno = 10;
  9    exception
 10      when others then
 11        dbms_output.put_line('Error message with  %type    :' || sqlerrm);
 12        dbms_output.put_line(' Exceptional block ' || empno_type);
 13    end;
 14  
 15    begin
 16      dbms_output.put_line(' Before assigning ename  ' || emp_rowtype.ename);
 17      select e.* into emp_rowtype from scott.emp e where e.deptno = 20;
 18  
 19    exception
 20      when others then
 21        dbms_output.put_line('Error message with %rowtype  :' || sqlerrm);
 22  
 23        dbms_output.put_line(' After Raising  Exception  Ename ' ||
 24                             emp_rowtype.ename);
 25        dbms_output.put_line(' After Raising  Exception  empno ' ||
 26                             emp_rowtype.empno);
 27        dbms_output.put_line(' After Raising  Exception  sal ' ||
 28                             emp_rowtype.sal);
 29    end;
 30  end;
 31  /
 
 Initial value 
Error message with  %type    :ORA-01422: exact fetch returns more than requested number of rows
 Exceptional block 
 Before assigning ename  
Error message with %rowtype  :ORA-01422: exact fetch returns more than requested number of rows
 After Raising  Exception  Ename SMITH
 After Raising  Exception  empno 
 After Raising  Exception  sal 
 
PL/SQL procedure successfully completed