how is the definition of your column in the staging table? Is it maybe a CHAR(2), then you have always the extra space, if you declare as VARCHAR2(2) you lose the space.
Herald ten Dam
<Moderator Edit - deleted link signature - pl see FAQ on top right>
Pl post OS and database details, output of "describe <table>", sample of csv file, the complete sqlldr command and contents of the control file
Hi Herald ten Dam,
Thanks for quick reply.
I have re-verified my staging table and i see that column is defined with VARCHAR2(30).
data base details are
Oracle9i Enterprise Edition Release 220.127.116.11.0 - 64bit Production
PL/SQL Release 18.104.22.168.0 - Production
"CORE 22.214.171.124.0 Production"
TNS for Solaris: Version 126.96.36.199.0 - Production
NLSRTL Version 188.8.131.52.0 - Production
SOURCE VARCHAR2(20 BYTE) Yes 1 COMMERCE_ID VARCHAR2(8 BYTE) Yes 2 LAST_NAME VARCHAR2(40 BYTE) Yes 3 FIRST_NAME VARCHAR2(40 BYTE) Yes 4 FULL_NAME VARCHAR2(92 BYTE) Yes 5 STATUS_ACTIVE VARCHAR2(30 BYTE) Yes 6
sample data file
OPTIONS (ERRORS = 0)
INTO TABLE TEST_VENDORS_INFO
FIELDS TERMINATED BY '|'
SOURCE CHAR "LTRIM(RTRIM(:SOURCE))",
COMMERCE_ID CHAR "LTRIM(RTRIM(:COMMERCE_ID))",
LAST_NAME CHAR "LTRIM(RTRIM(:LAST_NAME))",
FIRST_NAME CHAR "LTRIM(RTRIM(:FIRST_NAME))",
FULL_NAME CHAR "LTRIM(RTRIM(:FULL_NAME))",
STATUS_ACTIVE CHAR "LTRIM(RTRIM(:STATUS_ACTIVE))"
sqlldr USERID=$vl_ora_user_pass CONTROL=$OC_TOP/bin/TEST_VENDORS_INFO_LOAD.ctl DATA=$vl_file
Issue has been resolved. I have used replaceoption. now additional space or garbage is not there in the column value.
STATUS_ACTIVE "REPLACE(REPLACE(REPLACE(:STATUS_ACTIVE ,CHR(9)),CHR(10)),CHR(13))"
please confirm above approach can be used for all the columns in the script. Thanks in advance.
You can also try this
STATUS_ACTIVE CHAR(1) TERMINATED BY WHITESPACE