/*
 * Title  : cr_ctl.sql
 * Author : EXADIME LLC
 * Created: 31-Jul-2017
 * Updated: 31-Jul-2017
 * Release: 1.0.0
 * Purpose: To unload the data into a flat file. This generates SQL Loader control file & data file.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on
set termout on

set linesize 125
set pagesize 0
set pause off
set long 80

set heading off
set headsep off
set underline off
set colsep " "

set define on
set verify off
set escape off
set embedded on

--1.Provide the Inputs
--	1.FILE_PATH: Path where the control & data file(s) are to be generated.
--	2.LOADER_FILE_PATH: Root path definition for DAT,DIS,BAD files in control file(s).
--2.Change the format of nls_date_format to YYYYMMDDHH24MISS in os prompt 
--before use of sqlldr command
--	$set nls_date_format=YYYYMMDDHH24MISS

--Specify the Spool file(s) path. (Optional)
--Do not append slash (\ on NT / on UNIX) at the last.
define file_path=&&rpt_dir

--Specify the Loader file(s) path. (Optional)
--Do not append slash (\ on NT / on UNIX) at the last.
define loader_file_path=&&rpt_dir

--prompt define owner = upper('&&sch_name')
define default_precision = 20
define default_scale = 20
define delimiter='|'

accept table_name char prompt 'Enter table name: '

@@&&pls_dir/unl_data.sql

prompt Process completed. Check the files in &&file_path

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80
set long 80

set termout on
set serverout off
set feedback on
set echo off

