0

Here I am updating for one particular contract_id. I need to update for all contract_ids at one shot,
i.e Dynamically passing Contract_id in the plsql block.

DECLARE
    VAR1   NUMBER := 0;

    CURSOR C1 (P_CONTRACT_ID IN NUMBER)
    IS
        SELECT GO.CONTRACT_ID,
               GO.COLUMN46,
               GO.COLUMN1,
               GO.ORG_ID
          FROM XYZ GO
         WHERE GO.CONTRACT_ID = P_CONTRACT_ID AND GO.COLUMN46 IS NULL;
BEGIN
    BEGIN
        SELECT NVL (MAX (TO_NUMBER (COLUMN46)), 0)
          INTO VAR1
          FROM XYZ
         WHERE CONTRACT_ID = 525215;
    EXCEPTION
        WHEN OTHERS
        THEN
            VAR1 := 0;
    END;

    FOR F1 IN C1 (525215)
    LOOP
        VAR1 := VAR1 + 1;

        BEGIN
            UPDATE XYZ
               SET COLUMN46 = VAR1
             WHERE     CONTRACT_ID = F1.CONTRACT_ID
                   AND COLUMN46 IS NULL
                   AND COLUMN1 = F1.COLUMN1;
        EXCEPTION
            WHEN OTHERS
            THEN
                NULL;
        END;
    END LOOP;

    COMMIT;
EXCEPTION
    WHEN OTHERS
    THEN
        NULL;
END;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Pasha Md
  • 37
  • 5
  • 1
    You should really remove all of those `WHEN OTHERS` logic bombs. What if the first lookup fails because of a corrupted index block? I've seen that happen. Do you really want it to just set `VAR1 := 0;` and carry on as if everything was fine? – William Robertson Jun 01 '18 at 11:36

1 Answers1

0

Change your cursor and remove your where-condition:

CURSOR C1
IS
    SELECT GO.CONTRACT_ID,
           GO.COLUMN46,
           GO.COLUMN1,
           GO.ORG_ID
      FROM XYZ GO
     WHERE GO.COLUMN46 IS NULL;

You properbly want to remove it at id-select, too:

    SELECT NVL (MAX (TO_NUMBER (COLUMN46)), 0)
      INTO VAR1
      FROM XYZ
     --WHERE CONTRACT_ID = 525215
     ;

You should also read something about sequences and triggers to generate Ids ;-) How to create id with AUTO_INCREMENT on Oracle?

kara
  • 3,205
  • 4
  • 20
  • 34