Link to home
Start Free TrialLog in
Avatar of sventhan
sventhanFlag for United States of America

asked on

PLSQL Bulk Update

Dear Experts -

I've this code working fine with the while loop when I'm updating bulk rows(Update only no of rows at a time in a controlled way) .

WHENEVER SQLERROR EXIT SQL.SQLCODE;

SET TIMING ON;
SET ECHO ON;
SET TERMOUT ON;
SET FEEDBACK ON;
SET SERVEROUTPUT ON;

DECLARE
   L_TOTAL             NUMBER := 0;
   L_COUNT             NUMBER := 1;
   L_TIME_LIMIT_MINS   NUMBER := NVL (&&2, 60);
   L_ROW_LIMIT         NUMBER := NVL (&&3, 1000000);
   L_SEQUENCE          NUMBER;
   L_UPDATE_LIMIT      NUMBER := NVL (&&1, 10000);
   L_SQL_CODE          NUMBER;
   L_SQL_MSG           VARCHAR2 (200);
   L_START_TIME        DATE := SYSDATE;
   L_TIMEOUT           CHAR (1) := 'N';
BEGIN

   -- GET SEQUENCE ID
   SELECT SEQ_BATCH_PROC_ID.NEXTVAL INTO L_SEQUENCE FROM DUAL;


   WHILE (L_COUNT > 0 AND L_TIMEOUT = 'N')
   LOOP
      UPDATE BULKTABLE
         SET status = 'B'
       WHERE     status = 'D'
             AND expiration_date > SYSTIMESTAMP + 37
             AND ROWNUM <= L_UPDATE_LIMIT;

      L_COUNT := SQL%ROWCOUNT;
      L_TOTAL := L_TOTAL + L_COUNT;



-- CHECK IF ELAPSED TIME IS MORE THAN LIMIT SPECIFIED  OR TOTAL ROWS UPDATE IS MORE THAN LIMIT SPECIFIED
      IF ( (SYSDATE - L_START_TIME) * 24 * 60 >= L_TIME_LIMIT_MINS
          OR L_TOTAL >= L_ROW_LIMIT)
      THEN
         L_TIMEOUT := 'Y';
      END IF;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE ('Updated a total of ' || L_TOTAL || ' rows.');


EXCEPTION
   WHEN OTHERS
   THEN
      L_SQL_CODE := SQLCODE;
      L_SQL_MSG := SUBSTR (SQLERRM, 1, 200);


      -- RAISE EXCEPTION
      RAISE_APPLICATION_ERROR (
         -20001,
         'SQL CODE: ' || L_SQL_CODE || '. SQL MSG: ' || L_SQL_MSG);
END;
/

How can I convert the following cursor for loop to work like the above while loop? The goal is to split the one bulk update into small ones like the above.

DECLARE
   l_clob   CLOB;

   CURSOR cur
   IS
      SELECT key1, key2, data
        FROM schemaName..bulk_table
       WHERE textclob IS NULL;

   x        cur%ROWTYPE;
BEGIN
   OPEN cur;

   LOOP
      FETCH cur INTO x;

      EXIT WHEN cur%NOTFOUND;

      BEGIN
         ------------> proc will get the value for l_clob
         UPDATE schemaName..bulk_table
            SET textclob = l_clob
          WHERE section_key = x.key1 AND field_key = x.key2;

         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.
            put_line ('Some Message');
      END;
   END LOOP;

   CLOSE cur;
END if;

END;

/

Thanks,
sve.
Avatar of flow01
flow01
Flag of Netherlands image

Unless there is something special that I'm missing
basically you want to be able to do a restart
discarding already updated rows from update.


If key1 and key2 identify a single row (is that true?) the number of rows updated is always 1.

You can use the same method:  -
UPDATE schemaName..bulk_table
          SET textclob = l_clob
          WHERE  section_key = x.key1 AND field_key = x.key2  -- does this identify a row ?
           AND  textclob IS NULL  -- discard already updated rows
                                                       -- but also the only ones selected : so not necessary
           AND ROWNUM <= L_UPDATE_LIMIT; -- never more than 1 ? : not necessary ?


If my previous assumptions are true there are also possibilities to use a bulk collect with a limit clause in combination with a bulk update.
Avatar of sventhan

ASKER

@flow01 -

This is used to backfill the already existing rows. The textclob column is empty and I'm using this process to update the big table by a small chunks of rows.

My question is convert the for loop to the while loop. Something like this..... The updates are different in each case. The while loop update is hard coded the other one uses a cursor.


WHENEVER SQLERROR EXIT SQL.SQLCODE;

SET TIMING ON;
SET ECHO ON;
SET TERMOUT ON;
SET FEEDBACK ON;
SET SERVEROUTPUT ON;

DECLARE
   L_TOTAL             NUMBER := 0;
   L_COUNT             NUMBER := 1;
   L_TIME_LIMIT_MINS   NUMBER := NVL (&&2, 60);
   L_ROW_LIMIT         NUMBER := NVL (&&3, 1000000);
   L_SEQUENCE          NUMBER;
   L_UPDATE_LIMIT      NUMBER := NVL (&&1, 10000);
   L_SQL_CODE          NUMBER;
   L_SQL_MSG           VARCHAR2 (200);
   L_START_TIME        DATE := SYSDATE;
   L_TIMEOUT           CHAR (1) := 'N';
   l_clob              CLOB;

   CURSOR cur
   IS
      SELECT key1, key2, data
        FROM bulk_table
       WHERE textclob IS NULL;

   x                   cur%ROWTYPE;
BEGIN
   -- GET SEQUENCE ID
   SELECT SEQ_BATCH_PROC_ID.NEXTVAL INTO L_SEQUENCE FROM DUAL;

   OPEN cur;

   LOOP
      FETCH cur INTO x;

      EXIT WHEN cur%NOTFOUND;



      IF (L_COUNT > 0 AND L_TIMEOUT = 'N')
      THEN
         ------------> proc will get the value for l_clob
         UPDATE schemaName.bulk_table
            SET textclob = l_clob
          WHERE     section_key = x.key1
                AND field_key = x.key2
                AND ROWNUM <= L_UPDATE_LIMIT;

         COMMIT;         EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (' Message');
      END;

      L_COUNT := SQL%ROWCOUNT;
      L_TOTAL := L_TOTAL + L_COUNT;



-- CHECK IF ELAPSED TIME IS MORE THAN LIMIT SPECIFIED  OR TOTAL ROWS UPDATE IS MORE THAN LIMIT SPECIFIED
      IF ( (SYSDATE - L_START_TIME) * 24 * 60 >= L_TIME_LIMIT_MINS
          OR L_TOTAL >= L_ROW_LIMIT)
      THEN
         L_TIMEOUT := 'Y';
      END IF;
      end if
   END LOOP;

   DBMS_OUTPUT.PUT_LINE ('Updated a total of ' || L_TOTAL || ' rows.');


EXCEPTION
   WHEN OTHERS
   THEN
      L_SQL_CODE := SQLCODE;
      L_SQL_MSG := SUBSTR (SQLERRM, 1, 200);


      -- RAISE EXCEPTION
      RAISE_APPLICATION_ERROR (
         -20001,
         'SQL CODE: ' || L_SQL_CODE || '. SQL MSG: ' || L_SQL_MSG);
END;
/
ASKER CERTIFIED SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That's what I'm looking for. Thanks for the help.
Very much appreciated.