set echo off
clear columns
clear breaks
--set linesize 900
set termout on
set feedback off
set head off
set pagesize 0
set arraysize 1
set feedback off
set heading off
set verify off
set recsep off
-- **************************************************************
-- *** Feel free to use this SQL-code, but please             ***
-- *** do not remove these copyright lines.                   ***
-- *** (C) Roger Felix - Lund - Sweden                        ***
-- *** [roger_felix@hotmail.com, web_master@work.utfors.se]   ***
-- *** URL: http://hem.fyristorg.com/publish/index.html       ***
-- **************************************************************
COLUMN U NEW_VALUE UB
SELECT '&TABLE_OWNER_WILDCARD' U FROM DUAL;
DEFINE owner_name = &UB

COLUMN T NEW_VALUE TB
SELECT '&TABLE_NAME_WILDCARD' T FROM DUAL;
DEFINE table_name = &TB

COLUMN C NEW_VALUE CB
SELECT '&CONSTRAINT_NAME_WILDCARD' C FROM DUAL;
DEFINE cons_name = &CB

select '/* ALTER TABLE '||a1.owner||'.'||a1.table_name||' DROP CONSTRAINT '||
       constraint_name||'; */' from dba_constraints a1 
       where a1.constraint_type ='R'
       and a1.constraint_name LIKE upper('&&cons_name')
       and a1.owner LIKE upper('&&owner_name')
       and a1.table_name LIKE upper('&&table_name')
/

select ' ' from dual;

SELECT 
   /* FIRST POSITION OF REFERENCED TABLE LIST */
   decode(i1.position,1,decode(c1.position,1,'ALTER TABLE '||
              a1.owner||'.'||a1.table_name||chr(10)||
             '    ADD CONSTRAINT '||a1.constraint_name||chr(10)||
             '        FOREIGN KEY ('||chr(10)||
             '            ','           ,')
             ||c1.column_name || decode(c1.position,(
          select max(c3.position) 
              from DBA_CONS_COLUMNS c3
                  where c3.constraint_name = c1.constraint_name
           ),chr(10)||'       ) REFERENCING '||
           a2.owner||'.'||a2.table_name||'('||' /* '||a2.CONSTRAINT_NAME||' */'||
           chr(10)||'           '||i1.column_name,'')||
      decode( /* TEST FOR LAST POSITION OF REFERENCED LIST */
          i1.POSITION,(
              select max(i2.position) from
                   dba_cons_columns i2
                        where i2.CONSTRAINT_NAME = i1.CONSTRAINT_NAME
                      ),
                        ')'||decode(A1.DELETE_RULE,
                        'CASCADE',' ON DELETE CASCADE','')|| /* LAST LINE OF i1 */
                           chr(10)||'/'||chr(10),'')         /* NOT LAST LINE OF i1 */
   ,  
   decode(c1.position,1, /* JUST ONE ITERATION OVER C1.POSITION */
      decode( /* TEST FOR LAST POSITION OF REFERENCED LIST */
          i1.POSITION,(
              select max(i2.position) from
                   dba_cons_columns i2
                        where i2.CONSTRAINT_NAME = i1.CONSTRAINT_NAME
                      ),
             '           ,'||i1.column_name||')'||decode(A1.DELETE_RULE,
             'CASCADE',' ON DELETE CASCADE','')||  /* LAST LINE OF i1 */
             chr(10)||'/'||chr(10), 
             '           ,'||i1.column_name        /* NOT LAST LINE OF i1 */
      ),
  '') /* POS 1 OF C1 */         
)
          from dba_constraints a1, dba_constraints a2, 
dba_CONS_COLUMNS c1 , dba_CONS_COLUMNS i1
where a1.constraint_type ='R'
and i1.CONSTRAINT_NAME = a2.CONSTRAINT_NAME
and a1.constraint_name LIKE upper('&&cons_name')
and a1.owner LIKE upper('&&owner_name')
and a2.owner = a1.r_owner
and a2.constraint_name = a1.r_constraint_name
   and c1.constraint_name = a1.constraint_name
      and a1.table_name LIKE upper('&&table_name')
order by a1.table_name,
         a1.constraint_name, 
             i1.position, /* REFERENCED LAST IN LOOP*/
                  c1.position /* REFERENCING FIRST IN LOOP */;
select ' ' from dual;
set heading on;
set feedback on;
set recsep wrap;
set verify on;
set echo on;