SQL Loader

Recently , I had to use SQL*Loader for Oracle R12. Faced some issues, but finally got it sorted out.
First things first.

What do we need?
1) Data file: Can be .csv, txt etc. Here in my case it was .csv file.
I had to place the file on the application server. The location is one that has been set up for saving data files.

2) we need a Control file for the SQL*Loader utility to load the data into the database.

Structure of the Control file.

--/******************************************************************************

--   NAME:       myfile.ctl

--   PURPOSE:    Description

--

--   REVISIONS:

--   Ver        Date         Author           Description

--   ---------  ----------   ---------------  ------------------------------------

--   1.0        Date Sai Nimmagadda    Created this package. 

--******************************************************************************/

--Options (SKIP=2)

Load data

INFILE *

APPEND

INTO table table

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

  TRAILING NULLCOLS

(              Columns should be enough no data type needed unless they are dates

Order_date CHAR "to_date (:Order_date,'MM/DD/YYYY HH24:MI:SS')"

)

Order_date from data file is different format.

2/19/2001

10/21/2010 12:12:14

Once the control file is ready , save it with the extension '.ctl'

If this is a custom process , place the control file in the $CUSTOM_TOP/bin directory.

if using putty, perform chmod 777 filename.ctl

3) Create the staging table for the data to be loaded in the database if needed.

4) Next step is to create an Executable in the application

Sysadmin --> Concurrent --> program --> Executable

Define the name, short name,Application,  Description, Execution Method would be SQL*Loader and the Execution file would be the Control file name without the extension.

5) Define a new concurrent program with the new executable defined. Assign a parameter to the concurrent program so that we can pass the datafile as the parameter. 

Sometime the data file name would be the same, in that case we can actually provide the path/file name (.../myfile.csv)  as constant in the parameter definition. 

So that we dont have to provide the parameter each time we run the SQL*Loader Concurrent program.

Save your work.

6) Assign the new concurrent program created to a request group of your choice.

7) Login to the Applications, go to the responsibility, and we should be able to launch the Concurrent program from the SRS window.

**Any issues with dates should be worked on a trail and error basis. (At least for me)