Home » SQL & PL/SQL » SQL & PL/SQL » CURSOR in ORACLE
icon5.gif  CURSOR in ORACLE [message #446017] Fri, 05 March 2010 02:58 Go to next message
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 #446026 is a reply to message #446017] Fri, 05 March 2010 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing is wrong but you have no data that satisfy your query conditions.

By the way you can do it by a single cursor without repeating the inner query for each row of the first one by making a join (and you will not have this error).

Regards
Michel
Re: CURSOR in ORACLE [message #446487 is a reply to message #446026] Mon, 08 March 2010 17:08 Go to previous messageGo to next message
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 #446490 is a reply to message #446487] Mon, 08 March 2010 18:28 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: CURSOR in ORACLE [message #446491 is a reply to message #446490] Mon, 08 March 2010 18:40 Go to previous messageGo to next message
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 #446492 is a reply to message #446491] Mon, 08 March 2010 18:46 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>create table getdata_table (
Does not match any table in FROM clause & does not contain POSITION field.

I don't know what you have.
I don't understand what you desire/expect.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: CURSOR in ORACLE [message #446506 is a reply to message #446491] Mon, 08 March 2010 23:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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 #446688 is a reply to message #446506] Wed, 10 March 2010 00:20 Go to previous messageGo to next message
anne_p18
Messages: 8
Registered: March 2010
Location: Philippines
Junior Member
it works now.... Smile

thank you all for the help...
Re: CURSOR in ORACLE [message #446698 is a reply to message #446017] Wed, 10 March 2010 00:47 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello everybody,

Is following code valid, as described in OP?

DECLARE
  CURSOR C1...
  OPEN C1
BEGIN
  FOR C1 IN ...
  LOOP
    ...
  END LOOP;
  CLOSE C1;
END;


regards,
Delna
Re: CURSOR in ORACLE [message #446703 is a reply to message #446698] Wed, 10 March 2010 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just try it. Wink
SQL> declare
  2    cursor c1 is select ename from emp;
  3  begin
  4    open c1;
  5    for r in c1 loop
  6      dbms_output.put_line(r.ename);
  7    end loop;
  8    close c1;
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at line 2
ORA-06512: at line 5

FOR cursor loop open, fetch and close the cursor.

Regards
Michel
Re: CURSOR in ORACLE [message #446705 is a reply to message #446698] Wed, 10 March 2010 01:03 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member

@ delna
Good catch ./fa/1581/0/

Quote:
Nothing is wrong but you have no data that satisfy your query conditions.

This is without looking into the code,Just for the OP`s input "no data found "

sriram Smile
Re: CURSOR in ORACLE [message #446708 is a reply to message #446705] Wed, 10 March 2010 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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 #446710 is a reply to message #446708] Wed, 10 March 2010 01:15 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Thanks for the Input Michel.

sriram Smile
Re: CURSOR in ORACLE [message #446716 is a reply to message #446703] Wed, 10 March 2010 01:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think you didn't get Delna's question right; she is talking about OPEN being in the DECLARE section:
DECLARE
  CURSOR C1...
  OPEN C1         --> this!
BEGIN

However, "just try it" is a fair answer.
Re: CURSOR in ORACLE [message #446720 is a reply to message #446716] Wed, 10 March 2010 02:24 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks Littlefoot.

Moreover, it should be like

1> DECLARE OPEN C1 --> declare type of OPEN
2> BEGIN OPEN C1; FOR... --> CURSOR is already open
3> END LOOP; CLOSE C1 --> Invalid cursor

If anything wrong, please correct me.

regards,
Delna
Re: CURSOR in ORACLE [message #446728 is a reply to message #446720] Wed, 10 March 2010 03:05 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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 be
SQL> 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.
Re: CURSOR in ORACLE [message #446731 is a reply to message #446728] Wed, 10 March 2010 03:16 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Yes, I was intended to say this only...

regards,
Delna
Previous Topic: replicate sql problem
Next Topic: LEFT JOIN _last_ of matchin entries
Goto Forum:
  


Current Time: Fri Dec 09 09:34:53 CST 2016

Total time taken to generate the page: 0.08420 seconds