SQL Loader use of functions

Here we have explained the use to SQL Loader control file with functions which can be effectively used for data cleansing

Check the following link for whole set of explanation at Oracle
Check for beginner section here
Check for the expert section
Check for filtering data into multiple tables

Check the following control file

LOAD DATA
infile 'load_11.txt'
discardfile 'load_11.dsc'
INSERT
INTO TABLE test_funcdemo
FIELDS TERMINATED BY ','
(
FIRST_NAME CHAR "UPPER(:FIRST_NAME)",
LAST_NAME CHAR "LOWER(:LAST_NAME)"
)

Note for "UPPER(:FIRST_NAME)" which would convert the data for the field to upper.

The data load is given below

ripal,KANkotIa,
Maria,AntonI,
ABHIshek,GOsH

For practice create a table test_funcdemo with fields FIRST_NAME and LAST_NAME

To check use of timestamp for records use the control file in the following way


load data
infile 'load_3.txt'
insert
into table test_loader_1
(
load_time sysdate,
field_2 position( 1:10),
field_1 position(11:20)
)



Note
1. use of load_time sysdate which is insert the system date
2. use to position (1:10) means data for the first field will be extracted from the first to 10th character and so on.

The text file looks like

0123456789abcdefghij
**********##########
foo bar
here comes a very long line
and the next is
short

Check the following link for whole set of explanation at Oracle
Check for beginner section here
Check for the expert section
Check for filtering data into multiple tables