SQL*Loader has long been the standard way of getting data from a file into an Oracle Table.  While this works well, a newer approach using External Tables will do nearly anything that SQL*Loader will do and it also moves some control information into the Oracle database eliminating one file.

First, SQL*Loader on a UNIX platform:

SQL*Loader, sqlldr requires a set of files:

sqlldr USERID=$UID/$PWD control=bjvfeed_z.ctl log=bjvfeed_z.log DISCARD=bjvfeed_z.dsc

The control file describes the data being loaded and specifies the Oracle Table to be loaded.  e.g.,

-- bjvfeed_z.ctl Load JV Extract jvload.csv to Banner

LOAD DATA
INFILE 'bjvfeed_z.dat'
LOGFILE 'bjvfeed_z.log'
BADFILE 'bjvfeed_z.bad'
DISCARDFILE 'bjvfeed_z.dsc'
APPEND
INTO TABLE bjvfinc
-- WHEN BJVFINC_AMT != "0.000"
--Description, Amount, Fund, Orgn, Account, D/C, Rule Code
--FOAPAL Fund, Orgn, Acct, Prog, Actv, Location
(BJVFINC_CATEGORY_CODE CONSTANT "Z",
 BJVFINC_COAS_CODE CONSTANT "Z",
 BJVFINC_INTERFACED_IND CONSTANT "N",
 BJVFINC_TRANS_DESC CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' "SUBSTR(:BJVFINC_TRANS_DESC,1,35)",
 BJVFINC_AMT CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' "ABS(TO_NUMBER(REPLACE(REPLACE(REPLACE(REPLACE(:BJVFINC_AMT,',',''),' ',''),'(',''),')','')))",
BJVFINC_FUND_CODE CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' "SUBSTR(:BJVFINC_FUND_CODE,1,6)",
BJVFINC_ORGN_CODE CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' "SUBSTR(:BJVFINC_ORGN_CODE,1,6)",
BJVFINC_ACCT_CODE CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' "SUBSTR(:BJVFINC_ACCT_CODE,1,6)",
BJVFINC_DR_CR_IND CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' "SUBSTR(:BJVFINC_DR_CR_IND,1,1)",
-- BJVFINC_SOURCE CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"',
-- BJVFINC_FISC_CODE CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"',
BJVFINC_RUCL_CODE CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' "SUBSTR(:BJVFINC_RUCL_CODE,1,4)",
BJVFINC_ACTIVITY_DATE SYSDATE,
BJVFINC_SEQ_NUM SEQUENCE (1, 1))

BJVFINC is a standard Oracle Table that was created for use with this Batch Load.  This is just a temporary work or intermediate table that is used hold the data before loading into a Banner Table.  (The actual code for doing this is available in the Batch Journal Voucher Scripts example in the Banner Scripts repository, see: http://www.uaex.edu/bknox/.)

INFILE is, in this case, a CSV or Comma Separated Value file.  The easiest way to get data from an Excel user.
LOGFILE will contain the log of the loader run, including any error messages.
BADFILE will contain records rejected by the loader run.  These could be corrected and reloaded.
DISCARDFILE would contain any records discarded by the WHEN, which is commented in this example.

The APPEND will cause the records to be added to Table BJVFINC.  Another frequently used option is REPLACE which empties the Table before Inserting the new records.

CONSTANT "N", inserts a "N" into the Column.

"SUBSTR(:BJVFINC_TRANS_DESC,1,35)", is an example of using an Oracle Function in SQL*Loader.  In this case to make sure that the user did not supply too large a value for the field which would have caused the record to be rejected.

"ABS(TO_NUMBER(REPLACE(REPLACE(REPLACE(REPLACE(:BJVFINC_AMT,',',''),' ',''),'(',''),')','')))", is a much more complex example and it is being used to clean up an use of commas and parentheses from the user in this amount field.

SYSDATE,is being used to time stamp the loaded records.

SEQUENCE (1, 1), is being used to sequence the records starting with 1 by 1.

External Tables
(for now the discussion of using Oracle Loader and External Tables on a Windows platform can be found on http://www.uaex.edu/bknox/Oracle_Notes.htm Tip# 20.)

This is a personal site maintained by Bruce Knox ( bknox at uaex.edu ) documenting University of Arkansas, Division of Agriculture, Cooperative Extension Service IT projects.