set echo off;
set feedback off;
REM *************************************************************
REM ***  GENERIC SCRIPT GENERATION FOR ANY TABLE              ***
REM ***  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~              ***
REM ***  PURPOSE: Generate script for any table to port       ***
REM ***           data using INDSERT-scripts.                 ***
REM ***                                                       ***
REM ***  AUTHOR:  Roger Felix, Lund, Sweden 1999-09-21        ***
REM ***                                                       ***
REM ***  DESC:    Creates a script for any table that         ***
REM ***           creates a script containing                 ***
REM ***           INSERT-statements for inserting data from   ***
REM ***           one SID to another, or for making           ***
REM ***           installation scripts.                       ***
REM ***                                                       ***
REM ***  HINT:    Call this script from with my script        ***
REM ***           USERTABS.TXT to create data port scripts    ***
REM ***           for all tables in the current SCHEMA.       ***
REM *************************************************************
-- **************************************************************
-- *** 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       ***
-- **************************************************************
clear columns
clear breaks
set linesize 900
set termout on
set feedback off
set head off
set verify off
set pagesize 0
set recsep off
set time off
set timing off
set heading off;
set recsepchar ' ';
set verify off;
set headsep off;
set trimspool on;
set arraysize 1;

SELECT 'FIRST PARAMETER = TABLE and '||
        'SECOND PARAMETER = SCRIPT NAME' From dual;

select 'TABLE:  &1' from dual;
select 'SCRIPT: &2' from dual;

COLUMN T NEW_VALUE TB
SELECT '&1' T FROM DUAL;

COLUMN S NEW_VALUE SC
SELECT '&2' S FROM DUAL;

DEFINE table_name = &TB
DEFINE script_file = &SC

set termout off;
SPOOL &script_file

select '-- ************************************************************' from dual;
select '-- *** Table INSERT SCRIPT for table &table_name            ***' from dual;
select '-- *** created by the generic MAKE_INS.TXT SCRIPT           ***' from dual;
select '-- *** creating scripts for any table or all user_tables in ***' from dual;
select '-- *** a schema in conjugation with script USERTABS.TXT.    ***' from dual;      
select '-- ***                                                      ***' from dual;
select '-- *** Feel free to use the scripts mentioned, but please   ***' from dual;
select '-- *** do not remove these copyright lines.                 ***' from dual;
select '-- *** (C) Roger Felix - Lund - Sweden                      ***' from dual;
select '-- *** [roger_felix@hotmail.com, web_master@work.utfors.se] ***' from dual;
select '-- *** URL: http://hem.fyristorg.com/publish/index.html     ***' from dual;
select '-- ************************************************************' from dual;

select ' ' from dual;
select 'SET VERIFY OFF' from dual;
select 'SET FEEDBACK OFF' from dual;
select 'SET HEADING OFF' from dual;
select 'SET TERMOUT ON' from dual;
select 'SET LINESIZE 900' from dual;
select 'select ''TABLE:  &1'' from dual;' from dual;
select 'SET ECHO OFF' from dual;
select 'SET TRIMSPOOL ON' from dual;

select ' ' from dual;
select 'COLUMN C NEW_VALUE CD' from dual;
select ' ' from dual;
select 'SELECT '''||chr(38)||
        min(cc.column_name)||''' C from dual;'
  from  user_cons_columns cc,  user_constraints uc
    where cc.constraint_name = uc.constraint_name and
     uc.CONSTRAINT_TYPE = 'P' and uc.table_name = upper('&table_name');
select ' ' from dual;
select 'DEFINE THE_'||min(cc.column_name)||' = '||chr(38)||'CD' 
  from  user_cons_columns cc,  user_constraints uc
    where cc.constraint_name = uc.constraint_name and
     uc.CONSTRAINT_TYPE = 'P' and uc.table_name = upper('&table_name');
select ' ' from dual;
select 'SET FEEDBACK ON' from dual;
select 'SET TERMOUT OFF;' from dual;
select 'Spool INS&table_name'||'.SQL' from dual; 
select 'select ''insert into &table_name (''||' from dual;
select '       '' '||COLUMN_NAME||'''||' from user_tab_columns
where table_name = upper('&table_name')
and column_id = 1;

select '       '','||COLUMN_NAME||'''||' from user_tab_columns
where table_name = upper('&table_name')
and column_id != 1
order by column_id;

select ''') values (''||' from dual;

select DECODE(
           decode(DATA_TYPE,'CHAR','VARCHAR2',data_type)
                 ,'VARCHAR2','''''''''||replace(replace(',
         DECODE(DATA_TYPE,'NUMBER',''',''||nvl(to_char(',
            DECODE(DATA_TYPE,'DATE','decode('||COLUMN_NAME||',null,''null'',''to_date(''''''||to_char(',
               DECODE(DATA_TYPE,'LONG','''null''||')
            )
         )
       )||
       DECODE(DATA_TYPE,'LONG','',COLUMN_NAME)||
       DECODE(
          decode(DATA_TYPE,'CHAR','VARCHAR2',data_type)
               ,'VARCHAR2',','''''''',''''''''''''),'''||chr(38)||''',''''''||chr(38)||'''''')||''''''''||',
         DECODE(DATA_TYPE,'NUMBER','),''null'')||',
            DECODE(DATA_TYPE,'DATE',',''yyyymmddhh24miss'')||'''''',''''yyyymmddhh24miss'''')'')||',
               DECODE(DATA_TYPE,'LONG','')
            )
         )
       ) 
    from user_tab_columns
  where table_name = upper('&table_name')
and column_id = 1;

select DECODE(
         decode(DATA_TYPE,'CHAR','VARCHAR2',data_type)
            ,'VARCHAR2',''',''''''||replace(replace(',
         DECODE(DATA_TYPE,'NUMBER',''',''||nvl(to_char(',
            DECODE(DATA_TYPE,'DATE','decode('||COLUMN_NAME||',null,'',null'','',to_date(''''''||to_char(',
               DECODE(DATA_TYPE,'LONG',''',''||''null''||')
            )
         )
       )||
       DECODE(DATA_TYPE,'LONG','',COLUMN_NAME)||
       DECODE(
           decode(DATA_TYPE,'CHAR','VARCHAR2',data_type)
              ,'VARCHAR2',','''''''',''''''''''''),'''||chr(38)||''',''''''||chr(38)||'''''')||''''''''||',
         DECODE(DATA_TYPE,'NUMBER','),''null'')||',
            DECODE(DATA_TYPE,'DATE',',''yyyymmddhh24miss'')||'''''',''''yyyymmddhh24miss'''')'')||',
               DECODE(DATA_TYPE,'LONG','')
            )
         )
       ) 
      from user_tab_columns
    where table_name = upper('&table_name')
  and column_id != 1
order by column_id;

select ''');''' from dual;
select ' from &table_name ' from dual;
select DECODE(min(cc.column_name),null,'',
      ' where '|| min(cc.column_name) ||' LIKE '''||chr(38)||'THE_'||min(cc.column_name)||'''')
  from  user_cons_columns cc,  user_constraints uc
    where cc.constraint_name = uc.constraint_name and
     uc.CONSTRAINT_TYPE = 'P' and uc.table_name = upper('&table_name');
select '/' from dual;     
select 'SPOOL OFF;' from dual;
select 'SET TERMOUT ON;' from dual;
select 'select ''host notepad INS&table_name''||
        ''.SQL;'' from dual;' from dual;
SPOOL OFF;
set termout on;
select ' ' from dual;

select '@&script_file' from dual; 

set heading on;
set feedback on;
set recsep wrap;
set verify on;
set echo on;