| Error handling inside Cursor [message #573610] |
Mon, 31 December 2012 15:36  |
 |
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: Error handling inside Cursor [message #573626 is a reply to message #573623] |
Tue, 01 January 2013 04:56  |
 |
Littlefoot
Messages: 21809
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
