I have been using sql loader for transfering data from flat files to oracle. Suddenly now we are getting data from the source with blanks on the rigth side of the data. The flat file is being generated from IBM DB2 export utility. Does anyone know how to remove blanks in the ibm db2 export command ???
Also while using the sql loader utlity, I made some changes in the control file. I have attached the file here.
But when the command is runned, the log file shows no error, but the data in the columns is NOT PRESENT ??????
and i have RTM.
Some more info
Version = Oracle 8.1.7 on Windows 2000 server SP3
IBM DB2 6 on AIX
records approx 9 hundred thousand
2.4 ghz machine with 2 GB RAM.
This information from one of our DB2 DBA's is that while creating the script to export the data not to mention the cylinders and blocksize so that the system will ignore the blank spaces and take only the data.
i have read the documentation, but i m inserting the rtrim inside the sqlloader ctl file. I am clueless.
I am clueless coz I dont know what do you want
example, look the TRIM()
INTO TABLE TUHCDM10.hist_ora_alert
FIELDS TERMINATED BY '@'
NUM_LINEA DECIMAL EXTERNAL,
FEC_ALERT DATE "DY MON DD HH24:MI:SS YYYY",
COD_ERROR DECIMAL EXTERNAL "TRIM(:COD_ERROR)",
ES_ERROR CHAR NULLIF(ES_ERROR=BLANKS) "TRIM(:ES_ERROR)",
DES_OBSERVACION CHAR NULLIF(DES_OBSERVACION=BLANKS)
i have used the rtrim function to remove the blank spaces in the column. As the files that we have received have only blank spaces to the right of the data column. eg., '00008___'.
Here ____ represents blank spaces. Now I have to get rid of it.
I have used RTRIM function in SQL LOADER CTL file and have failed.
Hope this sends you a clear picture.
The following method helped me once to remove the blanks at the end while loading.But the column values where continuous without any spaces within them.
ES_ERROR "substr(:ES_ERROR,1,instr(:ES_ERROR,' ')-1)" ,
But trim function suggested by Pando is best because the above method might truncate some values if there are spaces inbetween the column values.
Click Here to Expand Forum to Full Width