Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009

    Unanswered: cannot read variables that have been fed by cursor

    Hello all,

    this is my second problem:

    I fetched a cursor into variables and wanted to copy this fetched data into another table but for some reason the table remains empty. Here is the code. I also checked the data types, they are all the same. I cannot find any mistakes. Thank you for helping me.

    I marked the problem further down (this doesn"t work)

    CREATE OR REPLACE PROCEDURE ia --inventory assign
    CURSOR c_shipments
    SELECT part, ship_date, ship_qty
    FROM ia_shipping
    ORDER BY part, ship_date;

    r_shipments c_shipments%ROWTYPE; --row

    TYPE receiving_type IS REF CURSOR;

    c_receiving receiving_type; --cursor
    shipment_remainder NUMBER;
    rec_depot VARCHAR2 (20);
    rec_part VARCHAR2 (20);
    rec_date DATE;
    rec_qty NUMBER;
    i NUMBER;
    --DELETE FROM ia_rec;
    --DELETE FROM ia_remainder;
    OPEN c_shipments;

    LOOP -- loop over all shipments
    FETCH c_shipments
    INTO r_shipments;

    EXIT WHEN c_shipments%NOTFOUND;
    --store the ship_qty in shipment_remainder
    shipment_remainder := r_shipments.ship_qty;

    -- load all parts from ia_receiving into cursor c_receiving
    -- get all receivings for this part sorted by China prior to Mexico
    OPEN c_receiving FOR 'SELECT depot, part, receive_date, receive_qty
    FROM ia_receiving t2
    WHERE t2.part='''
    || r_shipments.part
    || '''
    AND t2.receive_date<='''
    || r_shipments.ship_date
    || '''
    ORDER BY t2.receive_date, t2.depot DESC';

    LOOP -- loop over those receivings
    FETCH c_receiving
    INTO rec_depot, rec_part, rec_date, rec_qty;

    EXIT WHEN c_receiving%NOTFOUND;
    -- get the minimum of shipment_remainder and receiving_qty
    i := LEAST (shipment_remainder, NVL (rec_qty, 0));
    -- reduce shipment remainder by i;
    shipment_remainder := shipment_remainder - i;

    -- bulid ship history by recording all shipping amounts
    -- do not exit if shipment_remainder =0
    INSERT INTO ia_rec this doesn"t work
    (depot, part, receive_qty
    VALUES (rec_depot, rec_part, i

    -- after having recorded all shippings
    -- sum shipped parts according to ia_rec and reduce
    -- the sum(ia_receiving.receive_qty) per part and depot by the
    -- shipped parts built in the ship history.
    INSERT INTO ia_remainder
    (depot, part, qty)
    SELECT i1.depot, i1.part,
    (SUM (i2.receive_qty) - NVL (SUM (i1.receive_qty), 0) -- here is still a mistake
    ) AS qty
    FROM ia_rec i1, ia_receiving i2
    WHERE i1.part = i2.part AND i1.depot = i2.depot
    GROUP BY i1.depot, i1.part;

    --ORDER BY i1.part, i1.depot;
    CLOSE c_receiving;

    CLOSE c_shipments;

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    >this doesn"t work
    This is not an Oracle error message.
    This is 100% ambiguous.

    My scooter doesn't work.
    Tell me how to fix it.

    Do you get better/desired results after issuing COMMIT?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2009
    Ok, thank you you for your answer.

    Sorry for taking your time! Without running 'Exec ia' nothing will work. It works now. Sorry about this. it took me half the day to figure it out :-(

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts