Purge (PL/)SQL script

[fid509]

Hello,

I created an Oracle SQL script that deletes closed incidents and related data from eg. clocks, activity,etc. older then 2 years. This works fine except that there is always be room for improvement. One of the shortcomings in the script is the lack of flexibility. Now it contains for every a-table in Oracle a seperate and almost similar line of SQL in the script. If I add an array, in for example probsummary, I have to remember to put an extra line into the SQL script.
So I would like to put some kind of loop in the script that tells the script to do the delete for every probsummaryaxx tables it could find in the database. The same applies for each of the related tables.
I think I have to resort to PL/SQL for that but I don't know anything about PL/SQL programming. Can somebody give me some tips on how this could be done?

[tommy]

I can't help You with PL/SQL but I wonder why don't You just create a purge schedule in servicecenter? If You make sure the erddefs and joindefs are correct deleting an incident will delete all related files and You don't have to worry about updating a purge routine when adding new arrays.

[fid509]

On the volume of incidents I'm talking about it would take SC hours to complete the job. SQL is much faster, plus I rebuild all the indexes after the purge.

[fid509]

Never mind, I teached myself PL/SQL in 4 hours with a little aid of google