Bulk Collect Update

set verify on
set show on
set echo on
set feedback on
spool bulk_collect_hz_parties_data_masking.log
select name from v$database;
select to_char(sysdate,'mm/dd/yyyy hh24:mi') from dual;

DECLARE
   CURSOR rec_cur
   IS
      SELECT    /*+ PARALLEL(hp,6) */
               party_id
             , TRANSLATE (
                  party_name
                , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()~`?<>''''|:;-+[]{},./'
                , 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
               )
                  party_name
             , TRANSLATE (
                  person_first_name
                , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()~`?<>''''|:;-+[]{},./'
                , 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
               )
                  person_first_name
             , TRANSLATE (
                  person_middle_name
                , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()~`?<>''''|:;-+[]{},./'
                , 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
               )
                  person_middle_name
             , TRANSLATE (
                  person_last_name
                , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()~`?<>''''|:;-+[]{},./'
                , 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
               )
                  person_last_name
             , NVL2 (email_address, 'user@usps.gov', NULL)
             , TRANSLATE (primary_phone_number, '012345678', '999999999')
                  primary_phone_number
             , TRANSLATE (primary_phone_area_code, '012345678', '999999999')
                  primary_phone_area_code
             , TRANSLATE (primary_phone_country_code
                        , '012345678'
                        , '999999999')
                  primary_phone_country_code
        FROM   apps.hz_parties hp;

   TYPE vc1_party_id_t IS TABLE OF NUMBER;

   TYPE vc2_party_name_t IS TABLE OF VARCHAR2 (1000);

   TYPE vc2_person_first_name_t IS TABLE OF VARCHAR2 (1000);

   TYPE vc2_person_middle_name_t IS TABLE OF VARCHAR2 (1000);

   TYPE vc2_person_last_name_t IS TABLE OF VARCHAR2 (1000);

   TYPE vc2_email_address_t IS TABLE OF VARCHAR2 (1000);

   TYPE vc2_phone_number_t IS TABLE OF VARCHAR2 (256);

   TYPE vc2_phone_area_code_t IS TABLE OF VARCHAR2 (256);

   TYPE vc2_phone_country_code_t IS TABLE OF VARCHAR2 (256);

   vc1_party_id             vc1_party_id_t;
   vc2_party_name           vc2_party_name_t;
   vc2_person_first_name    vc2_person_first_name_t;
   vc2_person_middle_name   vc2_person_middle_name_t;
   vc2_person_last_name     vc2_person_last_name_t;
   vc2_email_address        vc2_email_address_t;
   vc2_phone_number         vc2_phone_number_t;
   vc2_phone_area_code      vc2_phone_area_code_t;
   vc2_phone_country_code   vc2_phone_country_code_t;
BEGIN
   OPEN rec_cur;

   LOOP
      FETCH rec_cur
         BULK COLLECT INTO  vc1_party_id, vc2_party_name, vc2_person_first_name, vc2_person_middle_name
                          , vc2_person_last_name, vc2_email_address, vc2_phone_number, vc2_phone_area_code
                          , vc2_phone_country_code
         LIMIT 10000;

      EXIT WHEN vc1_party_id.COUNT () = 0;

      FORALL i IN vc1_party_id.FIRST .. vc1_party_id.LAST
         UPDATE    /*+ PARALLEL(hp,6) */ apps.hz_parties hp
            SET   party_name = vc2_party_name (i)
                , person_first_name = vc2_person_first_name (i)
                , person_middle_name = vc2_person_middle_name (i)
                , person_last_name = vc2_person_last_name (i)
                , email_address = vc2_email_address (i)
                , primary_phone_number = vc2_phone_number (i)
                , primary_phone_area_code = vc2_phone_area_code (i)
                , primary_phone_country_code = vc2_phone_country_code (i)
          WHERE   party_id = vc1_party_id (i);

      COMMIT;
   END LOOP;

   CLOSE rec_cur;
    COMMIT;
END;
/

spool off
set verify off
set show off
set feedback off
select to_char(sysdate,'mm/dd/yyyy hh24:mi') from dual;