LOADING DATA INTO ORACLE TABLES

( sp_get_set_and_load )

This doc details the procedure evolved to automate the process of loading the data into Oracle database using SQL*Loader.

The functionality of the procedure:

(1) Go to the destination directory and get the list of the .dat files.

(2) Verify whether the file name (without) extension tallies with one of the names of the tables in the given user schema.

(3) If the file name tallies with the name of one of the tables of the given schema, then

(a) Start the SQL*Loader and load data into that table

(b) Populate a specified user table with the results of the loading, such as

i. Date and time of loading,

ii. Table name,

iii. Source directory and file name,

iv. Loader log file,

v. Bad data log,

vi. Discarded data log and

Assumptions:

(1) you have the directory structure and par files, .dat files and and controls files created and stored them in the respective destination directories.

(2) only the .dat file are to be over written by copying the new data file into the directory and all the data files you receive either by FTP or by any other mode have the table name as their name and