CURSOR in ORACLE [message #446017] |
Fri, 05 March 2010 02:58  |
anne_p18
Messages: 8 Registered: March 2010 Location: Philippines
|
Junior Member |
|
|
Hi,
Could anyone help me with this code.
Im getting an error "no data found" and i cant figure out what wrong in the code
declare cursor c1 is
select * from getdata_table;
OPEN c1
begin
for r_c1 in c1 loop
SELECT B.f_name,
B.l_name
FROM employee A,
company B
WHERE A.emp_num = r_c1.EMP_NUM
AND A.POSITION = B.POSITION;
dbms_output.put_line(f_name|| l_name || r_c1.EMP_NUM);
end loop;
CLOSE c1
end;
Thanks!!!!
|
|
|
|
Re: CURSOR in ORACLE [message #446487 is a reply to message #446026] |
Mon, 08 March 2010 17:08   |
anne_p18
Messages: 8 Registered: March 2010 Location: Philippines
|
Junior Member |
|
|
Hi Michael,
when i select the query, i got an output.
How can i do the join, sorry im new in oracle..
Appreciate it very much for your help..
|
|
|
|
Re: CURSOR in ORACLE [message #446491 is a reply to message #446490] |
Mon, 08 March 2010 18:40   |
anne_p18
Messages: 8 Registered: March 2010 Location: Philippines
|
Junior Member |
|
|
create table getdata_table (
EMP_NAME 50 char,
EMP_NUM NUMERIC (25,0)
)
when i select the query
SELECT B.f_name,
B.l_name
FROM employee A,
company B
WHERE A.emp_num = EMP_NUM
AND A.POSITION = B.POSITION
( where the EMP_NUM is in the getdata_table)
i got an output. But when i run the query with cursor the error is "no data found"
would you please help me to do it in a single cursor without repeating the inner query for each row of the first one by making a join.
|
|
|
|
Re: CURSOR in ORACLE [message #446506 is a reply to message #446491] |
Mon, 08 March 2010 23:30   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Just:
for rec in
( SELECT B.f_name, B.l_name, A.emp_num
FROM employee A, company B
WHERE A.emp_num = EMP_NUM
AND A.POSITION = B.POSITION
and a.emp_num in (select EMP_NUM from getdata_table)
) loop
dbms_output.put_line(rec.f_name||rec.l_name || rec..EMP_NUM);
end loop;
Regards
Michel
[Updated on: Wed, 10 March 2010 00:53] Report message to a moderator
|
|
|
|
|
|
|
Re: CURSOR in ORACLE [message #446708 is a reply to message #446705] |
Wed, 10 March 2010 01:13   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote:This is without looking into the code,Just for the OP`s input "no data found "
Right, with false information you have false conclusion. This is why copying and pasting the session is the best to get accurate and correct answers.
Anyway, I provided a far better way to do it in a previous post.
Regards
Michel
[Updated on: Wed, 10 March 2010 01:14] Report message to a moderator
|
|
|
|
|
|
Re: CURSOR in ORACLE [message #446728 is a reply to message #446720] |
Wed, 10 March 2010 03:05   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Actually, no, that wasn't entirely right; what is "DECLARE OPEN c1" supposed to be? That's invalid.
This is one option:SQL> l
1 declare
2 cursor c1 is select deptno from dept;
3 c1r c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into c1r;
8 exit when c1%notfound;
9 dbms_output.put_line(c1r.deptno);
10 end loop;
11 close c1;
12* end;
SQL>
In other words: you declare a cursor (c1), cursor variable (c1r), open a cursor, fetch, test when to exit the loop, close cursor. That's annoying.
A better (easier, that is) approach would beSQL> l
1 begin
2 for c1r in (select deptno from dept) loop
3 dbms_output.put_line(c1r.deptno);
4 end loop;
5* end;
SQL> See the difference? No declaring, opening, exiting, closing - much easier to maintain.
|
|
|
|