When building customization’s for the Oracle E-Business Suite it might be needed to load data from external sources into specific tables. Tables might be seeded tables, i.e. Open Interface tables, or custom tables which you’ve added to a special customization’s database schema.

To load data from external sources into Oracle eBS you might consider using SQL*Loader to accomplish this. SQL*Loader is using comma seperated (csv) files with data and a so called control file (ctl). The control file tells the system how to import the csv file with data. The control file describes the table and columns to be loaded, what the seperator is of the incoming file etc. Next the SQL*Loader program exports a log file to give an overview of the process, a bad file of records that caused errors in the process, and a discard file for records that were not selected during the load.


Starting a SQL*Loader load can be done by command line by executing the below:

sqlldr db_user/[email protected]:port/sid control=control_file_name.ctl log=log_file_name.log

SQL*Loader can also be executed by starting a special concurrent program which you can create in Oracle E-Business Suite. Below the steps how to do this.

1) First of all you need to have a csv file with data – the csv can also contain header information. The header in a csv file can be skipped by adding a special parameter in the control file. Take note of the columns in the csv file.

2) You need a control file – for example see the below control file. There are a big number of commands you can use in the control file to completely have control on how data gets loaded into tables. If you want to know more than contact me on this topic.

SKIP = 1
LOAD DATA
INFILE ‘data_file_name.csv’
BADFILE ‘data_file_name.bad’
REPLACE INTO TABLE XXX.YOUR_CUSTOM_TABLE
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
(

COLUMN1,COLUMN2,COLUMN3,…
)

SKIP=1
Tells SQL*Loader to exclude row 1 in the data file (to exclude the headers)

INFILE
Specifies the name of the incoming data csv file

BADFILE
Determines the bad file generated for any errors occured during the load

REPLACE
This command will first truncate the table and than add the records. If you change this in an APPEND command the records will be added only to the table specified without truncating first

FIELDS TERMINATED BY
Determines the seperator used in the data csv file

OPTIONALLY ENCLOSED BY
Determines an additional enclosing character like for example ” if you’re adding data which contains the column seperator used

TRAILING NULLCOLS
Is used to treat any missing data in the csv file as NULL for the table to be loaded

COLUMN1, COLUMN2, COLUMN3, …
Gives the column names to be loaded

3) Create the SQL*Loader Executable in Oracle E-Business Suite. Go to responsibility System Administrator – Concurrent – Program – Executable.


Give you executable a name, shortname and assign it to your customizations application. A description is optionally. Select Execution Method SQL*Loader to let eBS know SQL*Loader needs to be started. The Execution File Name holds the name of the control file you want to start (exclude the extension ctl here). The control file needs to be located in the bin directory of your customization application.

4) Create the concurrent program in Oracle E-Business Suite. Go to responsibility System Administrator – Concurrent – Program – Define.


Give your concurrent program a name, a short name, assign it to your customization’s application and optionally provide a description. Assign your created executable to the concurrent program.

5) Add parameters to dynamically provide the incoming data csv file. Click on Parameters.


Add Sequence 10 and give the parameter a name. In this we want to provide a full path to the incoming data csv file so we use seeded Value Set 100 Characters to hold the path. Optionally add a default value.

6) When done creating the concurrent program add it to a Concurrent Request Group and start loading data in your tables.

load data infile ‘AP_INV.TXT’
append into table    AP_INVOICES_INTERFACE
fields terminated by ‘^’ optionally enclosed by ‘”‘
trailing nullcols
    (INVOICE_NUM              CHAR “RTRIM(:INVOICE_NUM)”,
     INVOICE_TYPE_LOOKUP_CODE   CHAR “RTRIM(:INVOICE_TYPE_LOOKUP_CODE)”,
     INVOICE_DATE               DATE “DD-MON-RRRR”,
     VENDOR_NUM                 CHAR “RTRIM(:VENDOR_NUM)”,
     VENDOR_SITE_CODE           FILLER,
     INVOICE_AMOUNT             CHAR “RTRIM(:INVOICE_AMOUNT)”,
     TERMS_NAME                 CHAR “RTRIM(:TERMS_NAME)”,
     DESCRIPTION                CHAR “RTRIM(:DESCRIPTION)”,
     DUMMY1 FILLER,
     DUMMY2 FILLER,
     DUMMY3 FILLER,
     DUMMY4 FILLER,
     GL_DATE  DATE “TO_DATE((TO_CHAR(:GL_DATE,’DD-‘)||DECODE(TO_CHAR(:GL_DATE,’MON’),’AUG’,’SEP’,’SEP’,’SEP’,’OCT’,’OCT’,TO_CHAR(:GL_DATE,’MON’))||TO_CHAR(:GL_DATE,’-YYYY’)))”,
     VOUCHER_NUM                CHAR “RTRIM(:VOUCHER_NUM)”,
     INVOICE_RECEIVED_DATE      DATE,
     AMOUNT_APPLICABLE_TO_DISCOUNT,
     TERMS_DATE                 DATE,
     SOURCE                     CONSTANT ‘MAXCIM INVOICE’,
     PAYMENT_CURRENCY_CODE      CONSTANT ‘USD’,
     PAYMENT_METHOD_LOOKUP_CODE CONSTANT ‘CHECK’,
     CALC_TAX_DURING_IMPORT_FLAG CONSTANT ‘N’,
     ORG_ID                     CONSTANT ‘166’,
     SHIP_TO_LOCATION           CONSTANT ‘Materials Warehouse’,
     INVOICE_CURRENCY_CODE      CONSTANT ‘USD’,
     creation_date              SYSDATE,
     last_updated_by            CONSTANT 1093,
     last_update_date           SYSDATE,
     created_by                 CONSTANT 1093,
     INVOICE_ID “AP_INVOICES_INTERFACE_S.NEXTVAL”)

load data infile ‘AP_LINE_TEST.TXT’
append into table    AP_INVOICE_LINES_INTERFACE
fields terminated by ‘^’ optionally enclosed by ‘”‘
trailing nullcols
    (REFERENCE_2              CHAR “RTRIM(:REFERENCE_2)”,
     TYPE              FILLER,
     ACCOUNTING_DATE         DATE “DD-MON-RRRR”,
     DESCRIPTION          CHAR “RTRIM(:DESCRIPTION)”,
     DIST_CODE_COMBINATION_ID          CHAR “RTRIM(:DIST_CODE_COMBINATION_ID)”,
     LAST_UPDATED_BY          CHAR “RTRIM(:LAST_UPDATED_BY)”,
     AMOUNT                  CHAR “RTRIM(:AMOUNT)”,
     CREATED_BY          CHAR “RTRIM(:CREATED_BY)”,
     CREATION_DATE          DATE “DD-MON-RRRR”,
     ATTRIBUTE1          CHAR “RTRIM(:ATTRIBUTE1)”,
     ATTRIBUTE2          CHAR “RTRIM(:ATTRIBUTE2)”,
     ATTRIBUTE3          CHAR “RTRIM(:ATTRIBUTE3)”,
     TYPE_1099          CHAR “RTRIM(:TYPE_1099)”,
     UNIT_OF_MEAS_LOOKUP_CODE      CHAR “RTRIM(:UNIT_OF_MEAS_LOOKUP_CODE)”,
     TAX_RATE              CHAR “RTRIM(:TAX_RATE)”,
     QUANTITY_INVOICED      CHAR “RTRIM(:QUANTITY_INVOICED)”,
     UNIT_PRICE          CHAR “RTRIM(:UNIT_PRICE)”,
     INVOICE_ID “AP_INVOICE_LINES_INTERFACE_S.NEXTVAL”)

What is SQL*Loader?
SQL*loader is one of the Oracle tool which will be used to transfer the data from Flat-File to oracle Database table.

Which files in SQL*loader?
1. Flat or Data File
2. Control File
3. Bad File
4. Discard File
5. Log File

What is Flat Or Data File: This file contains the records in a special format; these records will be fetching for other legacy. The extension of these files might be .dat, .txt, or .csv (comma separated view).

What is Control File: This is SQL loader execution file, which will be used to transfer the date from file to table. In side of these control file, we will mention the Data file path, table name, column mapping. The extension of control file is .ctl

Control File Creation:

Load data
INFILE ‘Data File Path’
INSERT INTO ‘Table Name’
FIELD TERMINATED BY ‘,’
WHERE deptno = 10
TRAILING NULL COLS
(column1 , empno
column2, ename
column3, deptno)

Once we develop the control file we will execute this by using fallowing command
C:> sqlldr user/passward @ Database Control = name of control file (with extension .ctl)
This command will start the control file execution, and it will try to read the data and inserting into table. After completion of this execution, automatically three files will gets created
Bad file
Discard file
Log file

Bad File: Bad file contain the records, which are rejected by the SQL*loader. SQL*loader will reject the records, when ever the Flat file format is not correct or if any internal error occurs it will rejected. The extension of bad file is .bad

Discard File: Discard file contains the records which are rejected by the control file, control file reject the records, if record is not satisfying the conditions, which we have mentioned inside of control files the extension of discard file is .dis

Logfile: It contains the complete info of the process, like no of records successfully loaded in to the table
No of records successfully loaded in to the bad file & discard file.
And where the bad, discard file gets created and time taken to complete the process.
Taking the complete log.
SQL* Loader Modes:
INSERT
APPEND
REPLACE
We can replaced the data in to the table by using any one of the allowing method

INSERT: When we are using this statement, table should be empty. SQL * loader will insert the new data form the file.

APPEND: This mode will be use to attach the new record to the existing records.

REPLACE: This will replace the existing records with new records.
C:> sqlldr userid/[email protected] control=text1.ctl path=direct

SQL* Loader Paths: We can execution SQL* loader in two paths or nodes
Direct
Conventional

By default SQL*loader will be running in conventional mode, if we want to run in direct mode will use the fallowing syntax
C:> sqlldr userid/[email protected] control=text1.ctl path=direct
Direct mode will disable the table and column constrains and it will insert the data.
Conventional path will check every constrains, if it is satisfied it will insert the record
Conventional path is just like ‘insert statement’
SQL Commands Limitations:
to_date, to_char, upper, lower, Initcap, string, decode, nvl
when clause
sequence_name.next_value, Ref-Cursor
sysdate, ltrim, rtrim, constant