Table foreign key script generator

The script below generates a script, which when executed will create a table with data along with foreign keys and indexes on foreign key columns based on a table on a remote server and accessed via a database link.

  
set feedback off
set verify off
set linesize 1000
set pagesize 5000
set heading off

undefine Table_to_Script
undefine DB_Link_to_use

variable tbl_name varchar2(30);
variable db_link_name varchar2(30);
exec :tbl_name := '&Table_to_Script';
exec :db_link_name := '&DB_Link_to_use';

select 'Table selected for scripting : ' || upper(:tbl_name) || ';'
from dual;

select 'DB Link to use : ' || upper(:db_link_name) || ';'
from dual;

SELECT 'DROP TABLE ' || TABLE_NAME || ';'
FROM USER_TABLES
WHERE TABLE_NAME = :tbl_name;

set serveroutput on for wra size unlimited
begin
  dbms_output.put_line('prompt Create table ' || :tbl_name);
  dbms_output.put_line('create table '
                           || :tbl_name
                           || ' as (select * from '
                           || :tbl_name
                           || '@'
                           || :db_link_name
                           || ');');
end;
/

-- Create primary and/or unique keys first off
declare

  l_PK_Str    varchar2(4000);
  l_Tbl_Name  varchar2(30) := :tbl_name;

begin

  -- Assume that indexes will exist for all foreign keys
  for p in (select  uc.constraint_name as pk_constraint_name
                   ,uc.table_name
                   ,uc.constraint_type
                   ,'('||
                    listagg(ucc.column_name, ',')
                    within group (order by ucc.position)
                    || ')' as col_lst
            from
                    user_cons_columns   ucc
                   ,user_constraints    uc
            where
                    uc.table_name       = l_Tbl_Name
            and     uc.table_name       = ucc.table_name
            and     uc.constraint_name  = ucc.constraint_name
            and     uc.constraint_type  in ('P', 'U')
            group by uc.table_name
                    ,uc.constraint_name
                    ,uc.constraint_type
            ) loop

    l_PK_Str := 'alter table ' || l_Tbl_Name 
                               || ' add constraint ' 
                               || p.pk_constraint_name
                               || case p.constraint_type
                                     when 'P' then
                                       ' primary key '
                                     else
                                       ' unique '
                                   end
                               || p.col_lst || ';';

    dbms_output.put_line(null);
    dbms_output.put_line('prompt Add ' 
                            || case p.constraint_type 
                                 when 'P' then 
                                   ' primary ' 
                                 else 
                                   ' unique ' 
                               end 
                            || ' key to ' 
                            || p.table_name);

    dbms_output.put_line(l_PK_Str);

  end loop;

end;
/

declare
  l_FK_Str    varchar2(1000);
  l_Cnt       number;
  l_Tbl_Name  varchar2(30) := :tbl_name;
begin
  for r in (select  u1.constraint_name    as constraint_name
                   ,u1.r_constraint_name  as r_constraint_name
                   ,u1.table_name         as table_name
                   ,u2.table_name         as Ref_Tbl
            from
                    user_constraints      u1
                   ,user_constraints      u2
            where
                    u1.table_name         = l_Tbl_Name
            and     u1.constraint_type    = 'R'
            and     u1.r_constraint_name  = u2.constraint_name 
           )        loop


    l_FK_Str := 'alter table ' || r.table_name
                || ' add constraint '
                || r.constraint_name
                || ' foreign key (';

    -- add table foreign key columns
    for k in (select  column_name
              from    user_cons_columns
              where   constraint_name = r.constraint_name
              order by position) loop

      l_FK_Str := l_FK_Str || k.Column_Name || ',';
    end loop;

    -- strip off trailing commans and replace with ')'
    l_FK_Str :=    substr(l_FK_Str, 1, length(l_FK_Str) - 1)
                || ') references '
                || r.Ref_Tbl
                || '(';

    -- add reference table key columns
    for k in (select  column_name
              from    user_cons_columns
              where   constraint_name = r.r_constraint_name
              order by position) loop

      l_FK_Str := l_FK_Str || k.Column_Name || ',';
    end loop;

    l_FK_Str := substr(l_FK_Str, 1, length(l_FK_Str) - 1) || ');';

    dbms_output.put_line(null);
    dbms_output.put_line('prompt Add foreign key to table ' 
                          || l_Tbl_Name 
                          || ' references ' 
                          || r.Ref_Tbl 
                          || substr(l_FK_Str
                                   ,instr(l_FK_Str,  '(', 1)
                                   ,(instr(l_FK_Str,')',1) -  
                                     instr(l_FK_Str,'(',1)+1)
                                   )
                        );
    dbms_output.put_line(l_FK_Str);
  end loop;

  -- Assume that indexes will exist for all foreign keys
  for i in (select  'idx_'
                    || substr(uc.constraint_name,1,26) as index_name
                   ,uc.table_name
                   ,'('||
                    listagg(ucc.column_name, ',')
                    within group (order by ucc.position)
                    || ')' as col_lst
            from
                    user_cons_columns ucc
                   ,user_constraints  uc
            where
                    uc.table_name      = :tbl_name
            and     uc.table_name      = ucc.table_name
            and     uc.constraint_name = ucc.constraint_name
            and     uc.constraint_type = 'R'
            group by uc.table_name, uc.constraint_name) loop

    l_FK_Str := 'create index ' || i.Index_Name
                                || ' on '
                                || i.table_name 
                                || i.col_lst 
                                || ';';
 
    dbms_output.put_line(null);
    dbms_output.put_line('prompt prompt Add index on ' 
                            || i.table_name 
                            || i.col_lst);

    dbms_output.put_line(l_FK_Str);

  end loop;


end;
/

set feedback on
set verify on
set heading on