Results 1 to 5 of 5

Thread: invalid row id

  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: invalid row id

    with this code:
    SET SERVEROUTPUT ON
    CREATE OR REPLACE
    PROCEDURE update_price(p_cutoff IN CHAR) AS
    change CONSTANT Real := 0.9;
    CURSOR c_upgrade_p IS
    SELECT price FROM (select price, start_date from items i, on_sale os
    WHERE (i.id = os.item_id))
    WHERE (TO_DATE(start_date,'DD-MON-YYYY') = TO_DATE(p_cutoff,'DD-MON-YYYY'))
    FOR UPDATE OF PRICE;
    BEGIN
    FOR record IN c_upgrade_p LOOP
    UPDATE items
    SET price = price * 0.9
    WHERE CURRENT OF c_upgrade_p;
    END LOOP;
    COMMIT;
    END;
    /
    exec update_price('30-AUG-03');

    i get an invalid row id error, why is this?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: invalid row id

    Try it without the inline view:

    CURSOR c_upgrade_p IS
    SELECT price
    from items i, on_sale os
    WHERE (i.id = os.item_id))
    WHERE (TO_DATE(start_date,'DD-MON-YYYY') = TO_DATE(p_cutoff,'DD-MON-YYYY'))
    FOR UPDATE OF PRICE;

    BTW, does this: TO_DATE(start_date,...) mean that start_date is not a DATE???

  3. #3
    Join Date
    Sep 2003
    Posts
    5
    the start_date is a date, i am just checking that the format is the same... i dont know how to change from inline...?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If start_date is a DATE, then don't use TO_DATE on it. And I showed you the version without the inline view, though I made a mistake or two. Here is the corrected version, with the unwanted TO_DATE removed:

    CURSOR c_upgrade_p IS
    SELECT price
    from items i, on_sale os
    WHERE i.id = os.item_id
    AND start_date = TO_DATE(p_cutoff,'DD-MON-YYYY')
    FOR UPDATE OF PRICE;

    A DATE column doesn't have any format, it is stored in a special internal coding. Only use TO_DATE to convert a text string to a DATE, and use TO_CHAR to format a DATE for display.

  5. #5
    Join Date
    Sep 2003
    Posts
    5
    thanks heaps. ill implement it today and see what happens!

Posting Permissions

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