Delete Dependent Entries of a Table in Oracle PL/SQL

Posted by in Software, Tutorial

The other day I had a task to clean up a database which was populated with a list of records. Unfortunately, my main table is linked to a host of other tables via foreign key dependencies and, to make things worse, no CASCADE constraints are specified. The process I want to is as follows:

Scan main table

First, we need to find the records in the main table we want removed. This is a simple SELECT statement:

In my case, the statement is:

(the 80, and 122 are call identifiers)

Get dependent relevant records

The next step is to find the records in one of the dependent tables we need to remove. This is done via an INNER JOIN:

This will give us all the records in PROJECT_MEMBERS belonging to one of the projects in our selected calls.

Note: You can perform a COUNT(pri.*) for a quick feedback.

Delete

Now that we’ve identified the relevant records in PROJECT_MEMBERS, we can proceed to delete them:

Find dependent tables

The above approach is OK if you have a very limited number of tables. However, if the number is large, it’s better to batch-ify it. The first step is to find the dependent tables of your main table:

This will give you all the constraints and, more important, the linked tables.

Iterate through tables

Once we’ve identified the tables, we can try to iterate through them. Unfortunately, you can only do dynamic statements in PL/SQL, so we’ll have to create a small program.

First, we loop through the dependent tables:

Then, for each iteration we:

  • count the rows and
  • If the count is not 0, then remove the relevant rows.

The inner script is something like:

This will perform the dynamic SELECT with a variable table name.

IMPORTANT! This script assumes the primary key and all foreign keys are named the same (proj_id in this case).

Now, we’re all ready to remove the rows inside the IF statement :)

The full script is something like this:

DISCLAIMER This script works for me. However, it’s your responsibility to make sure the script is adapted to your situation. I can’t be held accountable if you wipe out your DB while using it!

HTH,


A little experiment: If you find this post and ad below useful, please check the ad out :-)