CODEWITHSUNDEEP
sqloracle-database
SMH
SMH

Reputation: 1316

Break down an oracle script into smaller scripts

I am trying to run a script which changes 200,000 records. But I want to change each 10000 record separately, so the first 10000 then the second 10000 and so on. 

 FOR d IN ids -- ids is a cursor which is filled by 200,000 record
      LOOP
        UPDATE doctor
        SET name =
          (SELECT name
          FROM source
          WHERE id = d.id
          AND type =
            (SELECT MAX(type)
            FROM Da
            WHERE i = id
            )
          )
        WHERE pat = d.pat;
        COMMIT;
      END LOOP;

Upvotes: 0

Views: 35

Answers (1)

Ashish Dhandharia
Ashish Dhandharia

Reputation: 361

What are you implying by saying change 10000 records separately. Do you mean you want to commit in blocks of 10000s?

You can do that using a counter

   record_count := 0
    FOR d IN ids -- ids is a cursor which is filled by 200,000 record
          LOOP
            UPDATE doctor
            SET name =
          (SELECT name
          FROM source
          WHERE id = d.id
          AND type =
            (SELECT MAX(type)
            FROM Da
            WHERE i = id
            )
          )
        WHERE pat = d.pat;
        record_count := record_count +1;
        IF  mod(record_count,10000) = 0
        THEN
         COMMIT;
        END IF;
      END LOOP;
      COMMIT; -- COMMIT for the last time if you have residual records

Upvotes: 1

Related Questions