Tuesday, September 23, 2014

Oracle SQL * Loader Various Examples to load data from flat data files

Oracle SQL * Loader


SQLLoader is a ubiquitous tool in the Oracle world. It has been shipped with Oracle since at least Version 6 and continues to be supported and enhanced with each new version of Oracle, including Oracle8 and Oracle8i.

The job of SQLLoader is to load data from flat files into an Oracle database. It's optimized for loading large volumes of data, and is flexible enough to handle virtually any input format.

Almost every Oracle user has to use SQLLoader at one time or another, and DBAs are frequently called upon to load data for the users in their organization.

I have given below various examples of how to construct the necessary control files and load from different types of files containing different types of data (e.g., fixed-width data, delimited data, and data of various datatypes).



TRUNCATE, REPLACE, APPEND –Three modes of data loading


SQL * Loader Script to load various/delimeter based data file:

Below script for data file with comma delimeter having 4 fields in it.
This script was designed for Oracle applications. fnd_global is an api of oracle apps used to pull currenct user id.
XXSGN_FA_ADJUST_S1 is an sequence.
$1 is input paramter where we pass data file path.

You should save this script in file with extension .ctl and move that file to applications bin directory where you are registering program.

LOAD DATA
INFILE '$1'
TRUNCATE
into table XXSSE.XXSGN_FA_ADJUST_STG
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(
ASSET_NUMBER position(1),
BOOK_TYPE_CODE char,
COST integer external,
POSTING_STATUS char,
STAGING_ID "XXSGN_FA_ADJUST_S1.NEXTVAL",
CREATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
LAST_UPDATE_DATE SYSDATE,
STATUS_FLAG CONSTANT 'I'
)


SQL * Loader Script to load various/delimeter based data file:

Below script for data file with fixed length(15 char) for each field having 4 fields in it.
This script was designed for Oracle applications. fnd_global is an api of oracle apps used to pull currenct user id.
XXSGN_FA_ADJUST_S1 is an sequence.
$1 is input paramter where we pass data file path.

You should save this script in file with extension .ctl and move that file to applications bin directory where you are registering program.

LOAD DATA
INFILE '$1'
TRUNCATE
into table XXSSE.XXSGN_FA_ADJUST_STG
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(
ASSET_NUMBER position(1:5),
BOOK_TYPE_CODE position(6:10),
COST position(11:12),
POSTING_STATUS position(13:15),
STAGING_ID "XXSGN_FA_ADJUST_S1.NEXTVAL",
CREATED_BY "fnd_global.user_id",
CREATION_DATE SYSDATE,
LAST_UPDATED_BY "fnd_global.user_id",
LAST_UPDATE_DATE SYSDATE,
STATUS_FLAG CONSTANT 'I'
)

Other generic examples of sql * loader:

Manipualte data while loading itself and populate some column with some values

LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)

You can oracle sql function in sql * loader script to do various validations and also create bad file and log file of loading:

LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state,
move_date "substr(:move_date, 3, 2) || substr(:move_date, 7, 2)"
)


Load Data
INFILE '/data1/xfer/empl.employee_new.dat'
truncate PRESERVE BLANKS INTO TABLE empl.employee
(last_name position(06:25) char,
first_name position(26:40) char,
middle_init position(41:50) char,
employee_no position(01:05) char,
phone_exe position(52:54) char,
phone_ext position(56:59) char,
department position(61:90) char,
dept_code position(91:95) char ,
title position(96:122) char,
code position(124:127) char,
territory position(128:129) char,
mailstop position(131:134) char,
location position(136:170) char,
loc_address_1 position(172:206) char,
loc_address_2 position(208:237) char,
loc_city position(239:258) char,
loc_state position(260:261) char,
loc_zip position(263:272) char,
mail_address position(274:308) char,
mail_city position(310:329) char,
mail_state position(331:332) char,
mail_zip position(334:343) char,
telno position(345:356) char,
net_fax position(358:369) char,
royal_net_oper position(371:378) char,
royal_net_fax position(380:387) char,
source_id position(389:389) char,
alt_empl_id position(390:394) char,
bus_seg_cd position(395:397) char,
bus_seg_dsc position(399:428) char,
acct_cd position(430:431) char,
acct_desc position(433:462) char,
bus_area_cd position(464:466) char)

Steps to load data:

load Data TRUNCATE INTO TABLE GL_BALANCE_2012
FIELDS TERMINATED BY "^"
(
LEGAL_ENTITY,
ACCOUNT,
ACCOUNT_DESC,
ENTRY_IND,
PERIOD_NAME,
ACCOUNT_TYPE,
YTD_ACTUAL_AMOUNT "to_number(trim(:YTD_ACTUAL_AMOUNT))"
)
Save above script with .ctl extension e.g. GL_BALANCE_2012.ctl
Then move that script to one unix directory e.g. /usr/bin
Then move your data file GL_BALANCE_2012.dat also to that directory i.e. /usr/bin
Goto that directory /usr/bin
Run below command from that directory:

sqlldr apps/passed123 data=GL_BALANCE_2012.dat control=GL_BALANCE_2012.ctl bad=GL_BALANCE_2012.err \ log=GL_BALANCE_2012.log silent=FEEDBACK

apps is databse login and passed123 is password. use ur 1 there.

If you are passing data file path in script like shown in some above script itself then you can only below command:


sqlldr apps/passed123 control=export.ctl
If you want register sql * loader script in Oracle Applications then you can use SQL * Loader execution method while registering program. Then you can move that script to that program's application bin directory.

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete