Home » SQL & PL/SQL » SQL & PL/SQL » Cursor with %ROWTYPE
Cursor with %ROWTYPE [message #269360] Fri, 21 September 2007 16:15 Go to next message
feign3
Messages: 5
Registered: September 2007
Junior Member
I am gaving trouble getting the expected results with the script below. It should return 3 records but I always only get 2 no matter what I throw at it:

DECLARE
--
--
CURSOR moto_item_cur IS
SELECT order_number,header_id
FROM apps.oe_order_headers_all
WHERE order_number IN (50003,50004,50007);
--
moto_item_rec moto_item_cur%ROWTYPE;
--
BEGIN
--
FOR moto_item_rec IN moto_item_cur LOOP
FETCH moto_item_cur INTO moto_item_rec;
dbms_output.put_line(' ');
dbms_output.put_line('Header ID: '||moto_item_rec.header_id);
dbms_output.put_line('Order: '||moto_item_rec.order_number);
END LOOP;
END;


The SQL returns 3 rows however the script only outputs 2. If I throw another order number in there I get 3 out of the 4 in the output.

Is this some kind of index issue?
Re: Cursor with %ROWTYPE [message #269363 is a reply to message #269360] Fri, 21 September 2007 16:24 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Remove the line FETCH moto_item_cur INTO moto_item_rec; first.
Now run this :
DECLARE
CURSOR moto_item_cur IS
SELECT order_number,header_id
FROM apps.oe_order_headers_all
WHERE order_number IN (50003,50004,50007);
moto_item_rec moto_item_cur%ROWTYPE;
BEGIN
FOR moto_item_rec IN moto_item_cur LOOP
--FETCH moto_item_cur INTO moto_item_rec;
dbms_output.put_line(' ');
dbms_output.put_line('Header ID: '||moto_item_rec.header_id);
dbms_output.put_line('Order: '||moto_item_rec.order_number);
END LOOP;
END;


and please format your code before posting.

Cheers
Soumen
Re: Cursor with %ROWTYPE [message #269364 is a reply to message #269360] Fri, 21 September 2007 16:24 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Is this some kind of index issue?
I serious doubt it is an "index issue".
> It should return 3 records
Prove that statement!

Since we don't have your data or know what data actually exists, we can't really answer your questions or solve this mystery.

Please read & follow posting guidelines in the #1 STICKY post at the top of this forum .
Re: Cursor with %ROWTYPE [message #269365 is a reply to message #269364] Fri, 21 September 2007 16:36 Go to previous messageGo to next message
feign3
Messages: 5
Registered: September 2007
Junior Member
> Prove that statement!

Good gosh!... there's always one in the bunch. Trust me brother... there absolutely SHOULD be 3 rows Wink

> Since we don't have your data or know what data actually exists, we can't really answer your questions or solve this mystery.

False. Can and was. There was no mystery. The answer was that I am a half-wit writing code on a deadline that I don't understand. Thank you Soumen Kamilya for your help!!

[Updated on: Fri, 21 September 2007 16:39]

Report message to a moderator

Re: Cursor with %ROWTYPE [message #269366 is a reply to message #269363] Fri, 21 September 2007 16:38 Go to previous messageGo to next message
feign3
Messages: 5
Registered: September 2007
Junior Member
Yes, after a little more digging I realized my mistake.

Thanks... and I will watch the formatting next time.
Re: Cursor with %ROWTYPE [message #269404 is a reply to message #269365] Sat, 22 September 2007 01:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
feign3 wrote on Fri, 21 September 2007 23:36
> Prove that statement!

Good gosh!... there's always one in the bunch. Trust me brother... there absolutely SHOULD be 3 rows Wink

> Since we don't have your data or know what data actually exists, we can't really answer your questions or solve this mystery.

False. Can and was. There was no mystery. The answer was that I am a half-wit writing code on a deadline that I don't understand. Thank you Soumen Kamilya for your help!!

So, you expect us to trust you when you claim there have to be three records to be returned; yet you are not able to write a basic pl/sql cursor loop.
Not meaning to offend you, but where would the boundary lie in where to trust your sql and where not?
It is way easier, both for you and for us if you would have provided testdata to prove that with the query you wrote there would actually be three rows.
Re: Cursor with %ROWTYPE [message #269486 is a reply to message #269360] Sat, 22 September 2007 15:31 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Are you sure about what you said ? Because

Quote:

1 declare
2 v_tab tab%rowtype;
3 cursor c1
4 is
5 select * from tab order by tname;
6 begin
7 --open c1;
8 for i in c1
9 loop
10 fetch c1 into v_tab;
11 dbms_output.put_line(i.tname);
12 end loop;
13* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 10

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production





Previous Topic: Delete orphaned childs
Next Topic: UTL_FILE in PL/SQl block
Goto Forum:
  


Current Time: Wed Dec 07 22:20:33 CST 2016

Total time taken to generate the page: 0.06778 seconds