5 Replies Latest reply: Oct 4, 2011 9:31 AM by 851268 RSS

    Values with trailing minus in sql*loader data file

    851268
      Hi All,

      I am facing issues with data file that gets parses through sql*loader utility and loads the data into one of the oracle tables.
      In the data file, there are some values containing minus at the end of the value, which is not getting parsed through sql*loader control file and resulting in INVALID NUMBER error.

      They are all number datatypes in the final Oracle table.

      Data looks like
      |||||||||||||881.02-|88.20-|2016.02||||||||||
      Is their any kind of formatting that can be done at the control file level such that it reads
      |||||||||||||881.02-|88.20-|2016.02||||||||||
      as
      |||||||||||||-881.02|-88.20|2016.02||||||||||
      and loads it into the Oracle table.

      Thanks.
        • 1. Re: Values with trailing minus in sql*loader data file
          user130038
          Here is a code snippet that changes the input string from '82.20-' to '-82.20' and so forth [ if there is a minus sign at the end of the string - otherwise it leaves it as-is]:
            with data1 as (select '82.20-' num from dual union all
                                 select '881.02-' from dual union all
                                 select '2016.02' from dual)
            select decode(substr(num, -1), '-', '-'||substr(num,0, length(num)-1), num) num_field from data1
          
          NUM_FIELD
          -82.20
          -881.02
          2016.02
          So, if the column name is "num_field" (number field), you can try something like this in your control file:
          LOAD DATA
            INFILE ...
            BADFILE ...
            APPEND
            INTO TABLE ...
            FIELDS TERMINATED BY ...
            (  ...,
               ...,
               num_field  "decode(substr(:num_field, -1), '-', '-'||substr(:num_field,0, length(:num_field)-1), :num_field)"
               ...,
            )
          Let me know if it worked or not. I don't have access to Oracle environment so I can't try it myself.
          • 2. Re: Values with trailing minus in sql*loader data file
            Solomon Yakobson
            848265 wrote:

            In the data file, there are some values containing minus at the end of the value
            Do you mean some have sign at the end and some in the beginning or sign, if specified, always appears at the end? If latter use to_char:
            field_name "TO_NUMBER(:field_name, '9999.99S')"
            For example:
            SQL> select to_number('881.02-','9999.99S') from dual
              2  /
            
            TO_NUMBER('881.02-','9999.99S')
            -------------------------------
                                    -881.02
            
            SQL> 
            SY.
            • 3. Re: Values with trailing minus in sql*loader data file
              851268
              Hi SY,

              When there is a negative number, the minus sign will appear at the end of the number, whereas a positive number will not have any specific signs and will look like a normal number.

              select to_number('881.02-','9999.99S') from dual fails when it is a positive number. The positive number would have nothing, it will be a kind of how a normal number looks like.

              I tried, S, MI but they fails on normal scenarios.

              Rgds.
              • 4. Re: Values with trailing minus in sql*loader data file
                851268
                Hi,

                I have tried your solution (the decode statement) and happy to say that as of now its working, I am still trying it with various data scenarios and results are positive so far.

                Many thanks.
                • 5. Re: Values with trailing minus in sql*loader data file
                  851268
                  The solution provided using DECODE Statement works.