1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Whitespace issue in sqlldr data file

Discussion in 'SQL PL/SQL' started by club, Jun 9, 2014.

  1. club

    club Active Member

    Messages:
    31
    Likes Received:
    1
    Trophy Points:
    185
    Hi Friends,

    I am facing one weird issue in sqlloader data file. In data file , I am receiving column value which is of date format. Now whenever that column doesn't contain any values instead of sending it as NULL they are sending whitespaces. Date is of format DD-MON-YYY. Total length is 11 character. So whenever this column don't have any data , I am getting 11 whitespaces. Due to this I my loader program erroring out. Hence I change the data type to VARCHAR2 to load all the data without any error.

    Now the main issue is that how to make column value as NULL whenever we encountered whitespaces in our table.
    I tried REPLACE command on sqlldr control file as well used in update script, but no success.

    Please help me and provide your suggestion to resolve this issue.

    Thanks in advance. :)
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Try using:

    Code (SQL):
        field_name                   "TRIM(:field_name)"
    in control file.