set echo off
set feedback off;
set verify off;
set heading off;
set arraysize 1;


REM ************************************************************
REM ***                                                      ***
REM ***  PURPOSE: Generate scripts for all tables to port    ***
REM ***           data from any table in a SCHEMA.           ***
REM ***                                                      ***
REM ***  SCRIPTS CALLED:  make_ins.txt                       ***
REM ***    [http://hem.fyristorg.com/publish/make_ins.txt]   ***
REM ***                                                      ***
REM ***  AUTHOR:  Roger Felix, Lund, Sweden 2000-04-13       ***
REM ***                                                      ***
REM ***  USAGE:   Run this script by typing @usertabs at     ***
REM ***           the SQL*Plus prompt. Specify conditions    ***
REM ***           by entering matching primary key string    ***
REM ***           for all user tables. Otherwise just enter  ***
REM ***           the character % and hit the ENTER-key.     ***
REM ***                                                      ***
REM ************************************************************

COLUMN U NEW_VALUE UN
SELECT '&JUST_CREATE_SCRIPTS_Y_OR_N' U FROM DUAL;

DEFINE just_create_scripts = &UN

COLUMN V NEW_VALUE UV
SELECT '&DO_YOU_RUN_16_BIT_PLUS_Y_OR_N' V FROM DUAL;

DEFINE run_16_bit = &UV

REM 
REM <<< FIRST CREATE THE EXTRACTION SCRIPTS USING MAKE_INS.TXT >>>
REM 

spool maketabs.sql
select '@@make_ins.txt '||table_name||' i'||substr(table_name,1,7)||'.SQL' 
       from user_tables where upper('&run_16_bit') = 'Y'
UNION 
select '@@make_ins.txt '||table_name||' i'||table_name||'.SQL' 
       from user_tables where not(upper('&run_16_bit') = 'Y');
spool off;
@maketabs.sql

REM 
REM <<< RUN THE EXTRACTION SCRIPTS FOR ALL USER TABLES >>>
REM 

set echo off
set feedback off;
set verify off;
spool makedata.sql
select '@@ i'||substr(table_name,1,7)||'.SQL' from user_tables 
    where upper('&just_create_scripts') in ('N','NO','NON','NEIN','NEJ')
       and upper('&run_16_bit') = 'Y'
UNION
select '@@ i'||table_name||'.SQL' from user_tables 
    where upper('&just_create_scripts') in ('N','NO','NON','NEIN','NEJ')
       and not (upper('&run_16_bit') = 'Y');
spool off;
@makedata.sql