CODEWITHSUNDEEP
sqlplsqloracle11gsql-delete
Ragav
Ragav

Reputation: 229

Oracle table Delete Looping -

I have a thirty party tool that has their stored procedure to delete records from the Oracle Tables which takes below arguments to delete the data from it

DELETE_TABLE1 UserId, Begin_Date, End_date

I have control to give these arguments based on which their process deletes the data. But sometime the process fails due to UNDO space error because based on the provided argument the data to delete may pile up to 10 Million or more than that.

The third party team says they can't update the stored procedures rather they are requesting to control the argument so only limited number of records are put in.

Is there a way i can control the values that can be passed as a argument say limiting the rows to 50,000 and then pass them as a argument to the final stored procedure? And loop them in iteration?

I have an option to use Shell Script as well to call the third party stored procedure

Upvotes: 1

Views: 921

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

To LIMIT the number of rows to be DELETED, you must have to modify the procedure where the DELETE statement is executed.

It is a bad idea to delete rows in a loop. A FOR LOOP, is ROW-BY-ROW aka SLOW-BY-SLOW. So, either do it at one go using simple SQL, if not, then use FORALL to avoid the LOOP and do it in BULK.

Regarding limiting the rows, you could use ROWNUM each time you delete a set of rows, commit the transaction, release the UNDO and delete the next set of rows.

Since you are using PL/SQL, the best way is to use FORALL with a LIMIT in BULK COLLECT.

For example :

OPEN c_data;
  LOOP
    FETCH c_data
    BULK COLLECT INTO l_tab LIMIT 1000; -- Here you limit the number of rows to be processed
    EXIT WHEN l_tab.count = 0;
  END LOOP;
CLOSE c_data;

Upvotes: 2

Related Questions