Search This Blog

Tuesday, February 12, 2008

Tips and Tricks with SQL Loader

Following are some of the tips and tricks that can be used with SQL Loader

1) Load text for a column which is having more than 4000 bytes.
Use following syntax


LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
long_text_columns char(40000)
)


2) How to use oracle functions with SQL Loader

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
long_text_columns char(40000) --- first 40000 characters
, column1 "trim(:column1)" --- Trims and loads
, column2 "replace(:column2,'\n',chr(10))" --- replace \n with new line
, column3 DATE "DD-MON-YYYY" --- defining date style
, column4 "upper(:column4)" --- changing to upper case
, column5 "lower(:column5)" --- changing to lower case
, column6 constant "FIXED" --- assigning a constant value
, column7 "sv_sequence.nextval" --- defaulting a value from sequence
)


Thats it for today .. will update with more commands later.

3 Comments:

Khandelwal Brahamin(ramdhan Sharma) said...

Incredible opening Suresh

Will post multitude of postings and make it more beneficial and encouraging in technical era.

Regards
Ramdhan

Suresh Vaishya said...

Thanks Ramdhan.

Anonymous said...

very helpful. thank you, Suresh.

Copyright (c) All rights reserved. Presented by Suresh Vaishya