/*
 * Title  : unl_data.sql
 * Author : EXADIME LLC
 * Created: 3-JUL-2017
 * Updated: 3-JUL-2017
 * Release: 1.0.0
 * Purpose: To generate a control file and data file for the given table in delimiter format (Origional Source).
 * History: N/A
 */

--Mychanges
--1.colsep and count is added in set linesize command
--2.commenting on direct option in control file
--3.change of control file for delimiter format
--This script handles the null values and date colunms in the fomrat YYYYMMDDHH24MISS

set tab off heading off heading off feedback off echo off verify off space 1 pagesize 0 linesize 120 

 --------------------------------------------------- 
 --  Generate the unload script 
 --------------------------------------------------- 

spool &&FILE_PATH/&&table_name..sql

select 'SET HEADING OFF FEEDBACK OFF ECHO OFF VERIFY OFF SPACE 0 PAGESIZE 0 TERMOUT OFF colsep ''|''' 
from dual 
  ;
 
 -----------------------------------------------------------------------------------
 --  Calculate the sum of all output field lengths and set the output record size 
 -----------------------------------------------------------------------------------

select 'SET LINESIZE '|| (sum(decode(data_type, 'CHAR',data_length, 'VARCHAR',data_length, 'VARCHAR2',data_length, 
                       'DATE',14, 'NUMBER',decode(data_precision, '',&default_precision+2, 
			greatest(data_precision-data_scale,1)+ decode(data_scale,0,0,1)+data_scale)+1, 
                       'FLOAT',&default_precision+2, data_length)) + count(column_name)) 
   from User_tab_columns where
   table_name=upper('&&table_name') 
 ; 

---------------------------------------------------------------------------
 --  Generate an appropriate SQL*Plus COLUMN command to control formatting
 --  of each output field 
--------------------------------------------------------------------------- 

 select 'COLUMN ' || rpad('"'||column_name||'"',32)  
        || ' FORMAT '  
        || rpad(decode(data_type, 
                    'CHAR','A'||data_length, 
                    'VARCHAR2','A'||data_length, 
                    'VARCHAR','A'||data_length, 
                    'DATE','A14', 
                    'NUMBER',decode(data_precision, 
                                    '', rpad('0',&default_precision-&default_scale,'9')||'.'||rpad('9',&default_scale,'9'), 
 rpad('0',greatest(data_precision-data_scale,1),'9') || decode(data_scale,0,'','.') 
 || decode(data_scale,0,'',rpad('9', data_scale,'9'))), 
 'FLOAT',rpad('0',&default_precision-&default_scale,'9')||'.'||rpad('9',&default_scale,'9'),'ERROR'),40) 
 || ' HEADING ''X''' 
   from user_tab_columns 
  where table_name=upper('&&table_name') 
  order by column_id 
 ; 

---------------------------------------------------------------  
--  Generate the actual SELECT statement to unload table data 
---------------------------------------------------------------

 select 'SPOOL &&FILE_PATH/&&table_name..dat'    
  from dual 
 ; 
 column var1 noprint 
 column var2 noprint 
 select 'a' var1, 0 var2, 'SELECT ' 
   from dual 
 union 
 select 'b', column_id, decode(column_id, 1, '    ', '  , ') 
                        || 
 decode(data_type,'DATE','to_char('||'"'||column_name|| 
 '"'||',''YYYYMMDDHH24MISS'') '||'"'||column_name||'"'  , 
 '"'||column_name||'"') 
   from user_tab_columns 
  where table_name=upper('&&table_name') 
 union 
 select 'c', 0, 'FROM &&table_name' 
   from dual 
 union 
 select 'd', 0, ';' 
   from dual 
  order by 1,2 
 ; 
 select 'SPOOL OFF' 
   from dual 
 ; 
 select 'SET TERMOUT ON' 
   from dual 
 ; 
 spool off 

----------------------------------------------------------------------------
 --  Generate the SQL*Loader control file 
----------------------------------------------------------------------------
 
 set lines 120 pages 0 
 spool &&FILE_PATH/&&table_name..ctl
 select 'a' var1, 0 var2, '--OPTIONS(DIRECT=TRUE)' 
   from dual 
 union 
 select 'b', 0, 'LOAD DATA' 
   from dual 
 union 
 select 'c', 0, 'INFILE  ''&&LOADER_FILE_PATH/&&table_name..dat''' 
   from dual 
 union 
 select 'd', 0, 'BADFILE  ''&&LOADER_FILE_PATH/&&table_name..bad''' 
   from dual 
 union 
 select 'e', 0, 'DISCARDFILE  ''&&LOADER_FILE_PATH/&&table_name..dsc''' 
   from dual 
 union 
 select 'f', 0, 'DISCARDMAX 999' 
   from dual 
 union 
 select 'm', 0, 'APPEND INTO TABLE &&table_name FIELDS TERMINATED BY ''&&DELIMITER'' OPTIONALLY ENCLOSED BY ''"'' TRAILING NULLCOLS(' 
   from dual 
 union 
 --SELECT 'n',column_id,DECODE(COLUMN_ID,1,COLUMN_NAME,CONCAT(',',COLUMN_NAME))  FROM USER_TAB_COLUMNS WHERE TABLE_NAME=upper('&&table_name') 
 SELECT 'n',column_id,DECODE(COLUMN_ID,1,rpad(COLUMN_NAME,31)||' NULLIF '||rpad(COLUMN_NAME,30)||'=BLANKS' ||' "TRIM(:'||COLUMN_NAME||')"',
 CONCAT(',',rpad(COLUMN_NAME,30))||' NULLIF '||rpad(COLUMN_NAME,30)||'=BLANKS' ||' "TRIM(:'||COLUMN_NAME||')"')  
 FROM USER_TAB_COLUMNS WHERE TABLE_NAME=upper('&&table_name') 
 union 
 select 'z', 0, ')' 
   from dual 
  order by 1, 2 
 ;
 spool off 
 
 @@&&FILE_PATH/&&table_name
 
----------------------------------------------------------------------------
 --  Cleanup 
----------------------------------------------------------------------------
 clear column 
 clear break 
 clear compute 
 undef owner 
 undef table_name 
 undef default_precision 
 undef default_scale 
 set tab on heading on feedback on echo off verify on 
----------------------------------------------------------------------------