Cursor with %ROWTYPE [message #269360] |
Fri, 21 September 2007 16:15  |
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 #269365 is a reply to message #269364] |
Fri, 21 September 2007 16:36   |
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 
> 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 #269486 is a reply to message #269360] |
Sat, 22 September 2007 15:31  |
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
|
|
|
|