sqlloader

[iceman]

hi all

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.

regards
ICEMAN

[iceman]

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.

[pando]

http://download-west.oracle.com/docs.../ch05.htm#5817

[akhadar]

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.

[iceman]

hi pando,

i have read the documentation, but i m inserting the rtrim inside the sqlloader ctl file. I am clueless.

[pando]

example, look the TRIM()


LOAD DATA
INFILE '/tmp/hist_ora_alert.dat'
APPEND
INTO TABLE TUHCDM10.hist_ora_alert
FIELDS TERMINATED BY '@'
TRAILING NULLCOLS
(
   NUM_LINEA                        DECIMAL EXTERNAL,
   NOM_ORACLE_SID                   CHAR,
   FEC_ALERT                        DATE "DY MON DD HH24:MI:SS YYYY",
   FEC_BBDD                         SYSDATE,
   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 am clueless coz I dont know what do you want

[iceman]

hi pando,

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.


regards
ICEMAN

[star]

hi

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.

regards