sqlloader
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: sqlloader

  1. #1
    Join Date
    Jan 2002
    Posts
    83

    sqlloader

    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
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2002
    Posts
    83

    more details

    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.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

  4. #4
    Join Date
    May 2001
    Posts
    736
    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.

  5. #5
    Join Date
    Jan 2002
    Posts
    83
    hi pando,

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

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    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

  7. #7
    Join Date
    Jan 2002
    Posts
    83

    explanation

    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

  8. #8
    Join Date
    Mar 2001
    Posts
    78
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width