declare

 /******************************************************************************

  * script     : del_rec_cascade.sql                                            *

  * version    : 1.0                                                           *

  * date       : 15-01-2007                                                    *

  * author     : Peter Boekelaar                                               *

  * description: This script deletes a record and all of its possible          *

  *              underlying childrecords. It simulates a cascade delete. The   *

  *              restriction for this script, is that it works with technical  *

  *              primary keys only i.e. only PK's  that contains one column of *

  *              the number type.                                              * 

  * usage      : l_schema_owner must contain the schema_owner                  *

  *              l_master_table must contain the name of the master table      *

  *              l_master_table_pk must contain the PK id of the master record *    

  ******************************************************************************/

  l_schema_owner    varchar2(50) := '<SCHEMA_OWNER>';

  l_master_table    varchar2(50) := '<MASTER_TABLE>';

  l_master_table_pk number       := 0;

  --

  procedure p_del_cascade( p_owner      in varchar2

                         , p_table_name in varchar2 

                         , p_id         in number

                         )

  is

    procedure p_del_master( p_owner      in varchar2

                          , p_table_name in varchar2 

                          , p_id         in number

                          )

    is 

      cursor c_master( b_owner      in varchar2

                     , b_table_name in varchar2)

      is

        select ccn.column_name

          from sys.all_cons_columns ccn

             , sys.all_constraints csn

         where csn.owner = b_owner

           and csn.table_name = b_table_name

           and csn.constraint_type = 'P'

           and ccn.owner = csn.owner

           and ccn.constraint_name = csn.constraint_name;

      l_column_name varchar2(32);

    begin

      open  c_master( p_owner, p_table_name );

      fetch c_master into l_column_name;

      close c_master;

      --

      execute immediate 'delete from '||p_table_name||

                        ' where '||l_column_name||' = :p_id' using p_id; 

    end p_del_master;  

    --             

    procedure p_del_childrecs( p_owner      in varchar2

                             , p_table_name in varchar2 

                             , p_id         in number

                             )

    is

      cursor c_child( b_owner in varchar2

                    , b_table in varchar2)

      is

        select ccn2.table_name

             , ccn2.column_name r_column

             , ccn3.column_name p_column

          from sys.all_cons_columns ccn3

             , sys.all_constraints act1

             , sys.all_cons_columns ccn2

             , sys.all_constraints act2

             , sys.all_constraints act3

         where act1.owner = b_owner

           and act1.table_name = b_table 

           and act1.constraint_type = 'P'

           and act2.r_owner = act1.owner

           and act2.r_constraint_name = act1.constraint_name

           and act2.constraint_type = 'R'

           and ccn2.owner = act2.owner

           and ccn2.constraint_name = act2.constraint_name

           and ccn2.table_name = act2.table_name

           and trim(act3.owner) = act2.owner

           and act3.table_name = act2.table_name

           and act3.constraint_type = 'P'

           and ccn3.owner = act3.owner

           and ccn3.constraint_name = act3.constraint_name

           and ccn3.table_name = act3.table_name;

      --

      l_id number(10);

    begin

      for r_child in c_child( p_owner, p_table_name )

      loop

        loop

          execute immediate 'select min('||r_child.p_column||') from '||r_child.table_name||

                            ' where '||r_child.r_column||' = :p_id' into l_id using p_id;

          exit when l_id is null; 

          p_del_childrecs( p_owner, r_child.table_name, l_id );

          --

          execute immediate 'delete from '||r_child.table_name||

                            ' where '||r_child.p_column||' = :p_id' using l_id; 

        end loop;

      end loop;

    end p_del_childrecs;

  begin

    p_del_childrecs( p_owner, p_table_name, p_id );

    p_del_master( p_owner, p_table_name, p_id );

  end p_del_cascade;

begin

  -- 

  p_del_cascade(l_schema_owner, l_master_table, l_master_table_pk );

  --

end;