How to load data from a text file into database table using a SQL*Loader Request in Oracle Applications?

---

Create the text file containing the data and the control file (with ‘.ctl’ extension).
Then create a request in Oracle Application. This request must use as Execution Method SQL*Loader and must have a parameter for the text file path.

---

Solution description with an example
Step 1
The table test which will be populated with the data from the text file has the following columns:
Column Name--Nullable?---Data Type
X---------------No----------NUMBER
Y---------------Yes---------VARCHAR2(20 BYTE)
Z---------------Yes---------DATE

The sequence X_S is defined for column X:
CREATE SEQUENCE X_S
MINVALUE 1
INCREMENT BY 1
START WITH 1
NOCACHE
NOORDER
NOCYCLE ;

Name the text file with data to insert test.txt.
Place this file in $APPL_TOP\app_name\version\txt\ folder.
Suppose text file contains something like this:
,"aaa",10-APR-2008
,"bbb",11-APR-2008
,"ccc",12-APR-2008
,"ddd",13-APR-2008
,"eee",14-APR-2008
,"fff",15-APR-2008
"aaa", "bbb" ,"ccc" ,"ddd" ,"eee" ,"fff" are for column Y and 10-APR-2008,
11-APR-2008, …, 15-APR-2008 are for column Z. There are no values specified for
column X because for this values it will be used the sequence X_S.

Step 2
Name the control file test.ctl.
Place it in $APPL_TOP\app_name\x.y.z\bin\ folder.
The control file looks like this:
LOAD DATA
APPEND
INTO TABLE eco.test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(X "eco.x_s.nextval",
Y,
Z)

Step 3
Define the Request in Oracle Application:
The Concurrent Program Executable has:
- Execution Method = SQL*Loader
- Execution File Name = test

The Request must have a parameter for the full path of the text file
( for example: C:\\oracle\visappl\eco\11.x.x\txt\test.txt ).

Step 4
Assign the request to some Request Group.

Step 5
Run the request and check if the data was loaded successfully into the database
table.

---

Troubleshooting
If the log file shows the error SQL*Loader-524: partial record found at end of
datafile, the last line in the text file and the control file must end with an “Enter”.

---