Home » SQL & PL/SQL » SQL & PL/SQL » Error handling inside Cursor (10G)
Error handling inside Cursor [message #573610] Mon, 31 December 2012 15:36 Go to next message
sqlsatya
Messages: 10
Registered: December 2012
Junior Member
Hi All,

I am writing a cursor and inside that cursor I am checking record exists or not and based on that I am doing my operation.But I am getting error that i can not use exception inside cursor see the below sample code (syntex may not be correct)

please suggest how should i write such code.

sample code------------------

cur c1
is select * from T1;
open c1
loop
fetch c1 into cur_id;

select name into var_name from t2 where id = cur_id;

exception
when no_data_found then
continue with next cursor value
end

update t3 set name = var_name where t3.id = cur_id;
commit;
end loop;
end;


[Updated on: Mon, 31 December 2012 15:37]

Report message to a moderator

Re: Cursor Error handling [message #573612 is a reply to message #573610] Mon, 31 December 2012 15:40 Go to previous messageGo to next message
BlackSwan
Messages: 22710
Registered: January 2009
Senior Member
why do you need PL/SQL; when plain SQL will suffice?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Re: Cursor Error handling [message #573614 is a reply to message #573612] Mon, 31 December 2012 15:50 Go to previous messageGo to next message
sqlsatya
Messages: 10
Registered: December 2012
Junior Member
Dear Blackswan,

Sorry if I was not very clear...Below is just a query I posted to understand what actually I am trying...

Actually I am trying to find out can we write an exception block inside a cursor because I am getting error message when I do so.
I am trying if there is an error then my cursor should fetch next value and do some complex operation.


Thanks,
Satya

Re: Cursor Error handling [message #573615 is a reply to message #573614] Mon, 31 December 2012 16:02 Go to previous messageGo to next message
BlackSwan
Messages: 22710
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

You can nest BEGIN ... END blocks & each block can contain its own exception handler; but you are advised against doing so.
Re: Error handling inside Cursor [message #573623 is a reply to message #573610] Tue, 01 January 2013 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 58913
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 good books for you:

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: Error handling inside Cursor [message #573626 is a reply to message #573623] Tue, 01 January 2013 04:56 Go to previous messageGo to next message
Littlefoot
Messages: 19510
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's an example, based on Scott's schema. In a cursor, I'm selecting department numbers from the DEPTNO table and - based on that value - one of employees (ROWNUM = 1; otherwise, I have a chance to get TOO-MANY-ROWS) who work in that department. There are no employees in department 40, so SELECT returns NO_DATA_FOUND - an exception we'd like to handle.

SQL> select d.deptno, count(e.empno) cnt
  2  from emp e, dept d
  3  where e.deptno (+) = d.deptno
  4  group by d.deptno
  5  order by d.deptno;

    DEPTNO        CNT
---------- ----------
        10          3
        20          5
        30          6
        40          0     --> no employees in department 40

SQL>


First, a non-working case:
SQL> declare
  2    cursor  c1 is select deptno from dept;
  3    c1r     c1%rowtype;
  4    l_ename emp.ename%type;
  5  begin
  6    open c1;
  7    loop
  8      fetch c1 into c1r;
  9      exit when c1%notfound;
 10
 11      -- This SELECT will raise NO-DATA-FOUND for DEPTNO = 40
 12      select e.ename
 13        into l_ename
 14        from emp e
 15        where e.deptno = c1r.deptno
 16          and rownum = 1;
 17 
 18      dbms_output.put_line(c1r.deptno ||': '|| l_ename);
 19    end loop;
 20    close c1;
 21  end;
 22  /
10: CLARK
20: SMITH
30: ALLEN
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 12


SQL>


As you were told, nested BEGIN-END block solves the problem:
SQL> declare
  2    cursor  c1 is select deptno from dept;
  3    c1r     c1%rowtype;
  4    l_ename emp.ename%type;
  5  begin
  6    open c1;
  7    loop
  8      fetch c1 into c1r;
  9      exit when c1%notfound;
 10
 11      begin
 12        select e.ename
 13          into l_ename
 14          from emp e
 15          where e.deptno = c1r.deptno
 16            and rownum = 1;
 17
 18        dbms_output.put_line(c1r.deptno ||': '|| l_ename);
 19
 20      exception
 21        when no_data_found then
 22          dbms_output.put_line(c1r.deptno ||': no employees');
 23      end;
 24    end loop;
 25    close c1;
 26  end;
 27  /
10: CLARK
20: SMITH
30: ALLEN
40: no employees

PL/SQL procedure successfully completed.

SQL>

[Updated on: Tue, 01 January 2013 04:58]

Report message to a moderator

Re: Error handling inside Cursor [message #573701 is a reply to message #573626] Wed, 02 January 2013 08:15 Go to previous message
sqlsatya
Messages: 10
Registered: December 2012
Junior Member
Thank you littlefoot for your help!!
Previous Topic: Backward accessing super type's attributes from sub type body in oracle collection (Types)
Next Topic: Update collections using TABLE operator
Goto Forum:
  


Current Time: Wed Aug 27 16:40:16 CDT 2014

Total time taken to generate the page: 0.16789 seconds