5 Replies Latest reply: Apr 29, 2014 1:32 PM by BPeaslandDBA RSS

    sqlldr is executing but all data are moved to bad file

    SenthilRam

      hi,

      i am trying to load data from the flat file to a table but entire content is moved to .bad file.could you please help me.

       

      >desc Retail;

      Name                                      Null?    Type

      ----------------------------------------- -------- --------

      BILL_NO                                   NOT NULL NUMBER

      BILL_DATE                                NOT NULL DATE

      PRODUCT_NO                           NOT NULL NUMBER

      PRODUCT_NAM                                        VARCHAR2

      PRICE                                        NOT NULL NUMBER

       

      The control file

      load data

      infile *

      append

      into table my_retail

      fields terminated by ','

      (bill_no "seq_mr.nextval",

      bill_date "sysdate",

      product_no,

      product_nam,

      price)

      begindata

      1234,cooker,2500

      1235,stove,1200

      1236,gas,1900

        • 1. Re: sqlldr is executing but all data are moved to bad file
          EdStevens

          SenthilRam wrote:

           

          hi,

          i am trying to load data from the flat file to a table but entire content is moved to .bad file.could you please help me.

           

          >desc Retail;

          Name                                      Null?    Type

          ----------------------------------------- -------- --------

          BILL_NO                                   NOT NULL NUMBER

          BILL_DATE                                NOT NULL DATE

          PRODUCT_NO                           NOT NULL NUMBER

          PRODUCT_NAM                                        VARCHAR2

          PRICE                                        NOT NULL NUMBER

           

          The control file

          load data

          infile *

          append

          into table my_retail

          fields terminated by ','

          (bill_no "seq_mr.nextval",

          bill_date "sysdate",

          product_no,

          product_nam,

          price)

          begindata

          1234,cooker,2500

          1235,stove,1200

          1236,gas,1900

          for openers, you define five fields, but I only see three in your data.

           

          Doesn't the run-time log from sqlldr give you some clues?

          • 2. Re: sqlldr is executing but all data are moved to bad file
            BPeaslandDBA

            > for openers, you define five fields, but I only see three in your data.

             

            Presumably because he is attempting to populate two of those fields with NEXTVAL from a sequence and SYSDATE.

             

            Because the OP is not reading five columns of data, the FIELDS TERMINATE line might need to be this:

             

            fields terminated by ',' trailing nullcols

             

            See if that simple change helps. Otherwise, include the runtime log as Ed indicated. And in case it matters, include your version.

             

            Edit: Added this additional comment:

             

            Might also need to reorder the columns in your list, not to match the table but to match the data order. Have the "computed" fields last. Something like this:

             

            fields terminated by ',' trailing nullcols

            (product_no,

            product_nam,

            price,

            bill_no "seq_mr.nextval",

            bill_date "sysdate")

             

             

             

             

            Cheers,
            Brian

             

            Message was edited by: BPeasland Added last section as noted in my response.

            • 3. Re: sqlldr is executing but all data are moved to bad file
              SenthilRam

              Hi BPeasland,

              Thanks a lot... problem solved.

              • 4. Re: sqlldr is executing but all data are moved to bad file
                EdStevens

                > for openers, you define five fields, but I only see three in your data.

                 

                Presumably because he is attempting to populate two of those fields with NEXTVAL from a sequence and SYSDATE.

                 

                Yep.  I should have caught that.

                • 5. Re: sqlldr is executing but all data are moved to bad file
                  BPeaslandDBA

                  No worries Ed! I'm sure next time it will be you catching me doing something similar. LOL!!!

                   

                  Cheers,

                  Brian