Is it possible via script/tool to generate authomatically many delete statements based on the tables fk relations, using Oracle PL/SQL?
In example: I have the table: CHICKEN (CHICKEN_CODE NUMBER) and there are 30 tables with fk references to its CHICKEN_CODE that I need to delete; there are also other 150 tables foreign-key-linked to that 30 tables that I need to delete first.
Is there some tool/script PL/SQL that I can run in order to generate all the necessary delete statements based on the FK relations for me?
(by the way, I know about cascade delete on the relations, but please pay attention: I CAN'T USE IT IN MY PRODUCTION DATABASE, because it's dangerous!)
I'm using Oracle DataBase 10G R2.
This is the result I've written, but it is not recursive:
This is a view I have previously written, but of course it is not recursive!
CREATE OR REPLACE FORCE VIEW RUN
(
   OWNER_1,
   CONSTRAINT_NAME_1,
   TABLE_NAME_1,
   TABLE_NAME,
   VINCOLO
)
AS
     SELECT OWNER_1,
            CONSTRAINT_NAME_1,
            TABLE_NAME_1,
            TABLE_NAME,
            '('
            || LTRIM (
                  EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || COLUMN_NAME)),
                           '/x/text()'),
                  ',')
            || ')'
               VINCOLO
       FROM (  SELECT CON1.OWNER OWNER_1,
                      CON1.TABLE_NAME TABLE_NAME_1,
                      CON1.CONSTRAINT_NAME CONSTRAINT_NAME_1,
                      CON1.DELETE_RULE,
                      CON1.STATUS,
                      CON.TABLE_NAME,
                      CON.CONSTRAINT_NAME,
                      COL.POSITION,
                      COL.COLUMN_NAME
                 FROM DBA_CONSTRAINTS CON,
                      DBA_CONS_COLUMNS COL,
                      DBA_CONSTRAINTS CON1
                WHERE CON.OWNER = 'TABLE_OWNER' AND CON.TABLE_NAME = 'TABLE_OWNED'
                      AND ( (CON.CONSTRAINT_TYPE = 'P')
                           OR (CON.CONSTRAINT_TYPE = 'U'))
                      AND COL.TABLE_NAME = CON1.TABLE_NAME
                      AND COL.CONSTRAINT_NAME = CON1.CONSTRAINT_NAME
                      --AND CON1.OWNER = CON.OWNER
                      AND CON1.R_CONSTRAINT_NAME = CON.CONSTRAINT_NAME
                      AND CON1.CONSTRAINT_TYPE = 'R'
             GROUP BY CON1.OWNER,
                      CON1.TABLE_NAME,
                      CON1.CONSTRAINT_NAME,
                      CON1.DELETE_RULE,
                      CON1.STATUS,
                      CON.TABLE_NAME,
                      CON.CONSTRAINT_NAME,
                      COL.POSITION,
                      COL.COLUMN_NAME)
   GROUP BY OWNER_1,
            CONSTRAINT_NAME_1,
            TABLE_NAME_1,
            TABLE_NAME;
... and it contains the error of using DBA_CONSTRAINTS instead of ALL_CONSTRAINTS...
Please pay attention to this:
http://stackoverflow.com/questions/485581/generate-delete-statement-from-foreign-key-relationships-in-sql-2008/2677145#2677145
Another user has just written it in SQL SERVER 2008, anyone is able to convert to Oracle 10G PL/SQL?
I am not able to... :-(
This is the code written by another user in SQL SERVER 2008:
DECLARE @COLUMN_NAME AS sysname 
DECLARE @TABLE_NAME AS sysname 
DECLARE @IDValue AS int 
SET @COLUMN_NAME = '<Your COLUMN_NAME here>' 
SET @TABLE_NAME = '<Your TABLE_NAME here>' 
SET @IDValue = 123456789 
DECLARE @sql AS varchar(max) ; 
WITH    RELATED_COLUMNS 
          AS ( 
              SELECT    QUOTENAME(c.TABLE_SCHEMA) + '.' 
                        + QUOTENAME(c.TABLE_NAME) AS [OBJECT_NAME] 
                       ,c.COLUMN_NAME 
              FROM      PBANKDW.INFORMATION_SCHEMA.COLUMNS AS c WITH (NOLOCK) 
              INNER JOIN PBANKDW.INFORMATION_SCHEMA.TABLES AS t WITH (NOLOCK) 
                        ON c.TABLE_CATALOG = t.TABLE_CATALOG 
                           AND c.TABLE_SCHEMA = t.TABLE_SCHEMA 
                           AND c.TABLE_NAME = t.TABLE_NAME 
                           AND t.TABLE_TYPE = 'BASE TABLE' 
              INNER JOIN ( 
                          SELECT    rc.CONSTRAINT_CATALOG 
                                   ,rc.CONSTRAINT_SCHEMA 
                                   ,lkc.TABLE_NAME 
                                   ,lkc.COLUMN_NAME 
                          FROM      PBANKDW.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc 
                                    WITH (NOLOCK) 
                          INNER JOIN PBANKDW.INFORMATION_SCHEMA.KEY_COLUMN_USAGE lkc 
                                    WITH (NOLOCK) 
                                    ON lkc.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG 
                                       AND lkc.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA 
                                       AND lkc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME 
                          INNER JOIN PBANKDW.INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
                                    WITH (NOLOCK) 
                                    ON rc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG 
                                       AND rc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA 
                                       AND rc.UNIQUE_CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
                          INNER JOIN PBANKDW.INFORMATION_SCHEMA.KEY_COLUMN_USAGE rkc 
                                    WITH (NOLOCK) 
                                    ON rkc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG 
                                       AND rkc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA 
                                       AND rkc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
                          WHERE     rkc.COLUMN_NAME = @COLUMN_NAME 
                                    AND rkc.TABLE_NAME = @TABLE_NAME 
                         ) AS j 
                        ON j.CONSTRAINT_CATALOG = c.TABLE_CATALOG 
                           AND j.CONSTRAINT_SCHEMA = c.TABLE_SCHEMA 
                           AND j.TABLE_NAME = c.TABLE_NAME 
                           AND j.COLUMN_NAME = c.COLUMN_NAME 
             ) 
    SELECT  @sql = COALESCE(@sql, '') + 'DELETE FROM ' + [OBJECT_NAME] 
            + ' WHERE ' + [COLUMN_NAME] + ' = ' + CONVERT(varchar, @IDValue) 
            + CHAR(13) + CHAR(10) 
    FROM    RELATED_COLUMNS 
PRINT @sql 
Thank to Charles, this is the latest not working release of the software, I have added a parameter with the OWNER because the referential integrities propagate through about 5 other Oracle users (!!!):
CREATE OR REPLACE PROCEDURE delete_cascade (
   parent_table          VARCHAR2,
   parent_table_owner    VARCHAR2)
IS
   cons_name        VARCHAR2 (30);
   tab_name         VARCHAR2 (30);
   tab_name_owner   VARCHAR2 (30);
   parent_cons      VARCHAR2 (30);
   parent_col       VARCHAR2 (30);
   delete1          VARCHAR (500);
   delete2          VARCHAR (500);
   delete_command   VARCHAR (4000);
   CURSOR cons_cursor
   IS
      SELECT constraint_name,
             r_constraint_name,
             table_name,
             constraint_type
        FROM all_constraints
       WHERE constraint_type = 'R'
             AND r_constraint_name IN
                    (SELECT constraint_name
                       FROM all_constraints
                      WHERE     constraint_type IN ('P', 'U')
                            AND table_name = parent_table
                            AND owner = parent_table_owner)
             AND delete_rule = 'NO ACTION';
   CURSOR tabs_cursor
   IS
      SELECT DISTINCT table_name
        FROM all_cons_columns
       WHERE constraint_name = cons_name;
   CURSOR child_cols_cursor
   IS
      SELECT column_name, position
        FROM all_cons_columns
       WHERE constraint_name = cons_name AND table_name = tab_name;
BEGIN
   FOR cons IN cons_cursor
   LOOP
      cons_name := cons.constraint_name;
      parent_cons := cons.r_constraint_name;
      SELECT DISTINCT table_name, owner
        INTO tab_name, tab_name_owner
        FROM all_cons_columns
       WHERE constraint_name = cons_name;
      delete_cascade (tab_name, tab_name_owner);
      delete_command := '';
      delete1 := '';
      delete2 := '';
      FOR col IN child_cols_cursor
      LOOP
         SELECT DISTINCT column_name
           INTO parent_col
           FROM all_cons_columns
          WHERE constraint_name = parent_cons AND position = col.position;
         IF delete1 IS NULL
         THEN
            delete1 := col.column_name;
         ELSE
            delete1 := delete1 || ', ' || col.column_name;
         END IF;
         IF delete2 IS NULL
         THEN
            delete2 := parent_col;
         ELSE
            delete2 := delete2 || ', ' || parent_col;
         END IF;
      END LOOP;
      delete_command :=
            'delete from '
         || tab_name_owner
         || '.'
         || tab_name
         || ' where ('
         || delete1
         || ') in (select '
         || delete2
         || ' from '
         || parent_table_owner
         || '.'
         || parent_table
         || ');';
      INSERT INTO ris
           VALUES (SEQUENCE_COMANDI.NEXTVAL, delete_command);
      COMMIT;
   END LOOP;
END;
/
In the cursor CONS_CURSOR I have added the condition:
         AND delete_rule = 'NO ACTION';
in order to avoid deletion in case of referential integrities with DELETE_RULE = 'CASCADE' or DELETE_RULE = 'SET NULL'.
Now I have tried to turn from stored procedure to stored function, but the delete statements are not correct:
CREATE OR REPLACE FUNCTION deletecascade (
   parent_table          VARCHAR2,
   parent_table_owner    VARCHAR2)
   RETURN VARCHAR2
IS
   cons_name                VARCHAR2 (30);
   tab_name                 VARCHAR2 (30);
   tab_name_owner           VARCHAR2 (30);
   parent_cons              VARCHAR2 (30);
   parent_col               VARCHAR2 (30);
   delete1                  VARCHAR (500);
   delete2                  VARCHAR (500);
   delete_command           VARCHAR (4000);
   AT_LEAST_ONE_ITERATION   NUMBER DEFAULT 0;
   CURSOR cons_cursor
   IS
      SELECT constraint_name,
             r_constraint_name,
             table_name,
             constraint_type
        FROM all_constraints
       WHERE constraint_type = 'R'
             AND r_constraint_name IN
                    (SELECT constraint_name
                       FROM all_constraints
                      WHERE     constraint_type IN ('P', 'U')
                            AND table_name = parent_table
                            AND owner = parent_table_owner)
             AND delete_rule = 'NO ACTION';
   CURSOR tabs_cursor
   IS
      SELECT DISTINCT table_name
        FROM all_cons_columns
       WHERE constraint_name = cons_name;
   CURSOR child_cols_cursor
   IS
      SELECT column_name, position
        FROM all_cons_columns
       WHERE constraint_name = cons_name AND table_name = tab_name;
BEGIN
   FOR cons IN cons_cursor
   LOOP
      AT_LEAST_ONE_ITERATION := 1;
      cons_name := cons.constraint_name;
      parent_cons := cons.r_constraint_name;
      SELECT DISTINCT table_name, owner
        INTO tab_name, tab_name_owner
        FROM all_cons_columns
       WHERE constraint_name = cons_name;
      delete1 := '';
      delete2 := '';
      FOR col IN child_cols_cursor
      LOOP
         SELECT DISTINCT column_name
           INTO parent_col
           FROM all_cons_columns
          WHERE constraint_name = parent_cons AND position = col.position;
         IF delete1 IS NULL
         THEN
            delete1 := col.column_name;
         ELSE
            delete1 := delete1 || ', ' || col.column_name;
         END IF;
         IF delete2 IS NULL
         THEN
            delete2 := parent_col;
         ELSE
            delete2 := delete2 || ', ' || parent_col;
         END IF;
      END LOOP;
      delete_command :=
            'delete from '
         || tab_name_owner
         || '.'
         || tab_name
         || ' where ('
         || delete1
         || ') in (select '
         || delete2
         || ' from '
         || parent_table_owner
         || '.'
         || parent_table
         || ');'
         || deletecascade (tab_name, tab_name_owner);
      INSERT INTO ris
           VALUES (SEQUENCE_COMANDI.NEXTVAL, delete_command);
      COMMIT;
   END LOOP;
   IF AT_LEAST_ONE_ITERATION = 1
   THEN
      RETURN ' where COD_CHICKEN = V_CHICKEN AND COD_NATION = V_NATION;';
   ELSE
      RETURN NULL;
   END IF;
END;
/
Please assume that V_CHICKEN and V_NATION are the criteria to select the CHICKEN to delete from the root table: the condition is: "where COD_CHICKEN = V_CHICKEN AND COD_NATION = V_NATION" on the root table.