Home » SQL & PL/SQL » SQL & PL/SQL » select the last value from FETCH statement (Oracle 9.2.0.3)
select the last value from FETCH statement [message #405084] Tue, 26 May 2009 05:29 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
call procudure test(RC1)
rc1 is refcursor

FETCH RC1 INTO
v_customer_id;
v_order_id; --order_id is numeric

the above fetch statement will return multiple rows .
dbms_output.put_line( 'customer_id :'||v_customer_id || ' Order_id :' v_order_id);


gives

customer_id :A12345  Order_id : 1
customer_id :A12345  Order_id : 2
......
customer_id :A12345  Order_id : 15



I need to insert only the last rows fetched into a table or the fetched row with maximum order_id
INSERT INTO TAB1 VALUES ( v_customer_id, v_order_id);


select order_id from tab1 where customer_id='A12345'

should give
15
select to the last row from FETCH statement?
Many thanks in advance

[Updated on: Tue, 26 May 2009 05:38]

Report message to a moderator

Re: select the last value from FETCH statement [message #405087 is a reply to message #405084] Tue, 26 May 2009 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Select only the rows you want to insert.

Regards
Michel
Re: select the last value from FETCH statement [message #405088 is a reply to message #405087] Tue, 26 May 2009 05:50 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, many thanks for the reply. But I can not modify the procedure resulset in test.So, what I did is
I put the Fetch statement inside loop and after that did the insert.


loop

fetch into ..

end loop;

insert into tab1 values(v_customer_id,v_order_id)
Re: select the last value from FETCH statement [message #405091 is a reply to message #405084] Tue, 26 May 2009 05:52 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member

Why do you want to parse through unwanted records. I mean why can't you do something like

INSERT INTO target_table
            (customer_id, order_id)
   SELECT alias_1.customer_id, alias_1.order_id
     FROM your_table alias_1
    WHERE (alias_1.customer_id, alias_1.order_id) =
                         (SELECT   alias_2.customer_id,
                                   MAX (alias_2.order_id)
                              FROM your_table alias_2
                             WHERE alias_2.customer_id = alias_1.customer_id
                          GROUP BY alias_2.customer_id)


Regards,
Jo
Re: select the last value from FETCH statement [message #405095 is a reply to message #405091] Tue, 26 May 2009 06:00 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
My requirement is call procedure test (RC1 OUT REFCURSOR);
I cant change the content of the resulset but inside my main procedure I can Fetch the entire resulset and store into variables and from the variable I want the last order id ( and the resul set I got is come through some processes).I need to call the procedure fetch the resuset and get the last row.

So I used it inside loop and the insert statement outside it.
Please let me know if i am wrong here.
Many thanks
Re: select the last value from FETCH statement [message #405098 is a reply to message #405095] Tue, 26 May 2009 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post an example of what you want to do.
It is not clear why your parameter is OUT only, where the cursor is declared and open, where you fetch and so on.

Regards
Michel
Re: select the last value from FETCH statement [message #405105 is a reply to message #405098] Tue, 26 May 2009 06:39 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, sorry for not being able to provide entire bunch of orginal code.
In the orginal called procedure there are some input parameters as well.

In the calling procedure I need to fetch the last records returned from the resulset of procedure Test.I can not modify the content of test but in the calling procedurewhile fetching the resulset I used Fetch statement inside loop.And put the
insert statement just outside the loop. I want to know if I am wrong here using this approach

Many Thanks for the help.
Re: select the last value from FETCH statement [message #405120 is a reply to message #405105] Tue, 26 May 2009 07:24 Go to previous message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a simple example representing your case (not your whole code, use for instance scott.emp table).
I still don't understand who declare the cursor, who open it, who fetch it, who can the result in which form.

Regards
Michel
Previous Topic: Setting up a database link
Next Topic: how to load default picture in blob field
Goto Forum:
  


Current Time: Mon Dec 05 02:42:43 CST 2016

Total time taken to generate the page: 0.14570 seconds