CODEWITHSUNDEEP
sqloracle-databaseplsql
saurabhk
saurabhk

Reputation: 140

PL/SQL procedure to cascade delete child tables

I have a situation like this.

I have to write a PL/SQL procedure to delete ALL child tables of a parent table and modify the length of the column which was referenced in the child table.

I need to take input from the user these parameters. parent table name parent column name (whose column size is to be altered ) new column size

I am new to PL/SQL programming .Please help me.

Thanks in advance.

Upvotes: 0

Views: 752

Answers (1)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

I'm not going to do your job here, but these hints should help you:

  • Find the name of the primary key constraint of the parent table in all_cons_columns where table_name='*name of the parent table*' and column_name='*name of the column you want to alter*'
  • Find the child tables in all_conststraints where constraint_type='R' and r_constraint_name='*name of the primary key constraint found in step one*'
  • Use EXECUTE IMMEDIATE to execute the DML statements to drop the child tables and alter the parent table

Upvotes: 1

Related Questions