1 2 Previous Next 17 Replies Latest reply: Aug 12, 2013 12:11 PM by Deep2910 Go to original post Branched from an earlier discussion. RSS
      • 15. Re: Sql*loader 11g - Error ORA-12899
        Deep2910

        Hi,
        I have one situation again.
        My data is like
        A90402|I|31-1060|Endowed Chair/Telecom Market|Endowed Chair in Telecommunication Marketing| |EO
        And my control file says

        load data
        infile '/mnt/oraclefra/adis_etl_dir/ALLOCATION.csv'
        INSERT INTO TABLE owbrep.ALLOCATION

        FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'

        TRAILING NULLCOLS

        (

        ALLOCATION_CODE,
        STATUS_CODE,
        ACCOUNT,
        SHORT_NAME,
        LONG_NAME,
        LONG_NAME2,
        CFAE_PURPOSE_CODE)
        ---
        The problem is with this column "LONG_NAME2".It is not null.It has a space character.But still my sqlldr throws this error

        Record 2: Rejected - Error on table OWBREP.ALLOCATION, column LONG_NAME2.
        ORA-01400: cannot insert NULL into ("OWBREP"."ALLOCATION"."LONG_NAME2")

        Please suggest what needs to be done.How to get rid of this null error as my column has a space character and this is to be loaded into the target table as it is.

        • 17. Re: Sql*loader 11g - Error ORA-12899
          Deep2910

          I have a scenario where the data file has values separated by ^.While loading this data into the table using sql loader I want to convert it into multiple lines.eg:

          data file:

          1|1013 park ridge~12345~irving|

          2|2013 park ridge~12345~irving|

          3|1013 park ridge|




          while loading it into table i want the data like




              ID ADDRESS

          1 "1013 park ridge

          12345

          irving"

          2 "2013 park ridge

          12345

          irving"

          .....

          My control file says:


          load data
          infile "/usr2/home2/adistest/h91ftp/temp/owb_test/owb_test1.csv"
          preserve blanks INTO TABLE owbrep.owb_test1

          TRUNCATE
          fields terminated by '|' TRAILING NULLCOLS
          (
             id,
             address
          )



          Please suggest what should I do?

          1 2 Previous Next