Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    24

    Unanswered: INSERT loop problem

    Hi,

    i am trying to find id numbers in a table and then instert into them predifined values.

    the query returns multiple rows which are the ones i need to update.

    i run the procedure and is says it has completed succsesfully but it does not do anything!


    DECLARE
    v_app_no NUMBER(10);
    CURSOR update_app IS
    (SELECT
    app.app_refno
    FROM
    applications app,
    applic_list_entries apen
    WHERE
    app.app_sco_code = 'CUR'
    AND
    apen.ale_rli_code = 'SH'
    AND
    app.app_refno = apen.ale_app_refno
    minus
    SELECT
    ga.gan_app_refno
    FROM
    general_answers ga
    WHERE
    ga.gan_que_refno =547);
    BEGIN
    FOR general_answers IN update_app
    LOOP
    FETCH update_app INTO v_app_no;
    EXIT WHEN update_app%NOTFOUND;
    INSERT INTO
    general_answers(
    gan_app_refno,
    gan_que_refno,
    gan_created_by,
    gan_created_date,
    gan_char_value
    )
    VALUES (v_app_no,547,'HOU','01-JAN-1950','Y');
    DBMS_OUTPUT_putline ('v_app_no');
    END LOOP;
    END;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    When you use a cursor FOR loop, you do not explicitly FETCH yourself - PL/SQL is already doing that for you. You are actually skipping every other row this way. So your code becomes:
    Code:
    DECLARE
        CURSOR update_app IS
            SELECT app.app_refno
            FROM   applications app,
                   applic_list_entries apen
            WHERE  app.app_sco_code = 'CUR'
            AND    apen.ale_rli_code = 'SH'
            AND    app.app_refno = apen.ale_app_refno
            MINUS
            SELECT ga.gan_app_refno
            FROM   general_answers ga
            WHERE  ga.gan_que_refno =547;
    BEGIN
        FOR general_answers_row IN update_app
        LOOP
            INSERT INTO
            general_answers(
            gan_app_refno,
            gan_que_refno,
            gan_created_by,
            gan_created_date,
            gan_char_value
            )
            VALUES (general_answers_row.app_refno,547,'HOU',TO_DATE('01-JAN-1950','DD_MON-YYYY'),'Y');
            DBMS_OUTPUT_putline ('v_app_no');
        END LOOP;
    END;
    But actually, you don't need a cursor and a loop at all here. SQL is all about sets, you need to think in sets and not just single rows:
    Code:
    BEGIN
       INSERT INTO
       general_answers(
       gan_app_refno,
       gan_que_refno,
       gan_created_by,
       gan_created_date,
       gan_char_value
       )
       SELECT app.app_refno,547,'HOU',TO_DATE('01-JAN-1950','DD_MON-YYYY'),'Y'
       FROM   applications app,
             applic_list_entries apen
       WHERE  app.app_sco_code = 'CUR'
       AND    apen.ale_rli_code = 'SH'
       AND    app.app_refno = apen.ale_app_refno
       MINUS
       SELECT ga.gan_app_refno,547,'HOU',TO_DATE('01-JAN-1950','DD_MON-YYYY'),'Y'
       FROM   general_answers ga
       WHERE  ga.gan_que_refno =547;
    END;
    BTW You may notice some other changes I made to your code:
    1) Never name variables with the same name as a table, it is likely to confuse PL/SQL and lead to incorrect results. Hence I renamed the variable "general_answers" to "general_answers_row".
    2) Never pass string literals where dates are required. Hence I added TO_DATE with an appropriate format mask. Another way would be to start using the ISO date format: DATE '1950-01-01'.

  3. #3
    Join Date
    Oct 2005
    Posts
    24
    THANKS TONY!!! ID BUY YOU A PINT IF YOU WAS NEAR!
    that worked a treat.. can beleive how quick you was!

Posting Permissions

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