Keeping track of all values created by a sequence for multiple inserts

In PL SQL, I'm writing a stored procedure that uses a DB link:

CREATE OR REPLACE PROCEDURE Order_Migration(us_id IN NUMBER, date_id in DATE) 
as 
begin
  INSERT INTO ORDERS(order_id, company_id)
  SELECT ORDER_ID_SEQ.nextval, COMPANY_ID 
  FROM ORDERS@SOURCE
  WHERE USER_ID = us_id  AND DUE_DATE = date_ID;   
end;  

It takes all orders done on a certain day, by a certain user and inserts them in the new database. It calls a sequence to makes sure there are no repeat PKs on the orders, and it works well.

However, I want the same procedure to do a second INSERT into another table that has order_id as a foreign key. So I need to add all the order_id's just created, and the data from SOURCE that matches:

INSERT INTO  ORDER_COMPLETION(order_id, completion_dt)
SELECT ????, completion_dt                    
FROM ORDER_COMPLETION@SOURCE

How can I keep track of which order_id that was just created matches up to the one whose data I need to pull from the source database?

I loo开发者_JS百科ked into making a temporary table, but you can't create those in a procedure.

Other info: I'll be calling this procedure from a C# app I'm writing

I'm not sure that I follow the question. If there is an ORDERS table and an ORDER_COMPLETION table in the remote database, wouldn't there be some key on the source system that related those two tables? If that key is the ORDER_ID, why would you want to re-assign that key in your procedure? Wouldn't you want to maintain the ORDER_ID from the source system?

If you do want to re-assign the ORDER_ID locally, I would tend to think that you'd want to do something like

CREATE OR REPLACE PROCEDURE order_migration( p_user_id IN orders.user_id%type,
                                             p_due_date IN orders.due_date%type )
AS
  TYPE order_rec IS RECORD( new_order_id  NUMBER,
                            old_order_id  NUMBER,
                            company_id    NUMBER,
                            completion_dt DATE );
  TYPE order_arr IS TABLE OF order_rec;
  l_orders order_arr;
BEGIN
  SELECT order_id_seq.nextval,
         o.order_id,
         o.company_id,
         oc.completion_dt
    BULK COLLECT INTO l_orders
    FROM orders@source o,
         order_completion@source oc
   WHERE o.order_id = oc.order_id
     AND o.user_id  = p_user_id
     AND o.due_date = p_due_date;

  FORALL i IN l_orders.FIRST .. l_orders.LAST
    INSERT INTO orders( order_id, company_id )
      VALUES( l_orders(i).new_order_id, l_orders(i).company_id );

  FORALL i IN l_orders.FIRST .. l_orders.LAST
    INSERT INTO order_completion( order_id, completion_dt )
      VALUES( l_orders(i).new_order_id, l_orders(i).completion_dt );
END;

You could also do a single FOR loop with two INSERT statements rather than two FORALL loops. And if you're pulling a lot of data each time, you probably want to pull the data in chunks from the remote system by adding a loop and a LIMIT to the BULK COLLECT

There must be some link between the rows in ORDERS@SOURCE and ORDERS, and between ORDERS@SOURCE and ORDER_COMPLETION@SOURCE, so can you not use a join?

Something like:

INSERT INTO  ORDER_COMPLETION(order_id, completion_dt)
SELECT o.order_id, ocs.completion_dt                    
FROM ORDER_COMPLETION@SOURCE ocs
JOIN ORDERS o ON o.xxx = ocs.xxx