How to generate DELETE statements in PL/SQL, based on the tables FK relations?
- by The chicken in the kitchen
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.
