SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trim Function in SQL Loader

780828Aug 3 2010 — edited Aug 4 2010

Oracle version 10g

I have control file like below and would like to know is there a better way to declare trim function at the top level instead of specifying at field level. I want to remove leading or trailing spaces for the field.

LOAD DATA
INFILE * 
TRUNCATE
INTO TABLE TMPTUPS_POD_DLY
( 
 ORD_NO POSITION(1:15) "TRIM(:ORD_NO)",
 CUST_PO_NO POSITION(16:37) "TRIM(:CUST_PO_NO)",
 SHP_NAME POSITION(38:72) "TRIM(:SHP_NAME)",
 SHP_ADDR POSITION(73:102) "TRIM(:SHP_ADDR)",
 SHP_CTY POSITION(103:132) "TRIM(:SHP_CTY)",
 SHP_ST POSITION(133:134),
 SHP_ZIP POSITION(135:144),
 SHP_DT POSITION(145:154) DATE "YYYY-MM-DD",
 TRCKNG_NO POSITION(155:184),
 NOU POSITION(185:192) INTEGER EXTERNAL,
 SHP_WGT POSITION(193:204) "TO_NUMBER(:SHP_WGT,'999999999999')/100",
 POD_NAME POSITION(205:234) "TRIM(:POD_NAME)",
 POD_DT POSITION(235:244) DATE "YYYY-MM-DD",
 POD_TIME POSITION(245:252) DATE "HH:MI:SS",
 AIR_BLL_PC POSITION(253:260) INTEGER EXTERNAL,
 POD_STS_CD POSITION(261:262) 
 )

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 1 2010

Added on Aug 3 2010

5 comments

17,894 views