Using SQL Loader#

SQL Loader is a utility created by Oracle and installed onto the database server during the installation of the software, as well as a large number of other programs. The utility has one purpose; load flat files into tables within an Oracle database. SQL Loader has a large number of options for loading in different types of files. In order to simplify the loading of these files, High Line has chosen to make use of only one of these load methods. For a complete list of options, with examples of each, please refer to the SQL Loader manual provided by Oracle on their documentation web site.

A large number of .CTL files have been provided by High Line to be used by the SQL Loader utility for loading comma delimited files into the interface tables of the target database. These files are located in the CONVERSION\CTL_FILES directory of the shared, mapped directory on the database server (this should be P:\P2K\CONVERSION\CTL_FILES if the default settings are used from the installation manual).

There is one .CTL file for each conversion interface file defined within the P2K schema of the database. Each of these files has the same basic format with the name of the input file, the target table in the database and the list of columns available changing for each file. The following example is a portion of the PERSONALS.CTL file and is used for loading data into the INF_CV_PERSONALS:

LOAD DATA
INFILE 'PERSONALS.CSV'
TRUNCATE
INTO TABLE INF_CV_PERSONALS
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  PERSON_CODE		POSITION (*)      
, USER_NAME			POSITION (*)        
, GOVERNMENT_CODE		POSITION (*)  
, FIRST_NAME		POSITION (*)       
, LAST_NAME			POSITION (*)        
, BIRTH_DATE		POSITION (*)       
, GENDER			POSITION (*)           
, LANGUAGE_CODE		POSITION (*)    
, ETHNIC			POSITION (*)           
, MIDDLE_NAME		POSITION (*)      
, SALUTATION		POSITION (*)       
, RANK			POSITION (*) 
, BIRTHPLACE		POSITION (*)       
, CHALLENGED		POSITION (*)       
, VETERAN_STATUS		POSITION (*)   
, EFFECTIVE			POSITION (*)"NVL(RTRIM(:EFFECTIVE),'01-JAN-0001') "
, EXPIRY			POSITION (*)"NVL(RTRIM(:EXPIRY),'31-Dec-3999') "
, CHANGE_CODE		POSITION (*)      
, GEO_CODE			POSITION (*) 
...