[CODE GENERATION] 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...
