Thursday, May 22, 2008

SQL Loader with Null columns

TRAILING NULLCOLS tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

load data
infile data.csv
replace
into table test_table
TRAILING NULLCOLS
(
col1 TERMINATED BY ',',
col2 TERMINATED BY ',',
col3 TERMINATED BY WHITESPACE
)


Loading string terminated by new line

control file construct
load data
infile product.csv "str '\n'"
append into table feature_8k
fields terminated by "," optionally enclosed by "'"
(col1,col2,col3,col4,col6)


data file in the format of

12503,'HABQ5FAI1','ROFA','28','N'
12503,'HABQ5FAI1','ROFA','29','N'
12503,'HABQ5FAI1','ROFA','30','N'
12503,'HABQ5FAI1','ROFA','31','Y'
12503,'HABQ5FAI1','ROFA','32','Y'



load with command

sqlldr asanga/asa@db1 control=controlp.ctl data=product.csv