2 Replies Latest reply: Nov 22, 2012 2:45 AM by bharathDBA RSS

    How to get complete list of bad recordswhile running SQLLDR script

    bharathDBA
      Hi,

      I'm running an SQLLDR script to populate a table from and input datafile consisting of 80000 odd records.
      Only about 30000 records are getting loaded into the table.

      When I tried to list all the bad records in the .bad file generated,I could only see the first 50 records.

      Is there a buffer size which limits this? if yes,how to increase the buffer size to get a list of all the 50000 bad records.

      Can someone please help me with this...

      Regards,
      Bharath.

      Edited by: bharathDBA on Nov 21, 2012 10:54 PM

      Edited by: bharathDBA on Nov 21, 2012 10:55 PM

      Edited by: bharathDBA on Nov 22, 2012 12:09 AM
        • 1. Re: How to get complete list of bad recordswhile running SQLLDR script
          AlbertoFaenza
          bharathDBA wrote:
          Hi,

          I'm running an SQLLDR script to populate a table from and input datafile consisting of 80000 odd records.
          Only about 30000 records are getting loaded into the table.

          When I tried to list all the bad records in the .bad file generated,I could only see the first 50 records.

          Is there a buffer size which limits this? if yes,how to increase the buffer size to get a list of all the 50000 bad records.

          Can someone please help me with this...

          Regards,
          Bharath.

          Edited by: bharathDBA on Nov 21, 2012 10:54 PM

          Edited by: bharathDBA on Nov 21, 2012 10:55 PM

          Edited by: bharathDBA on Nov 22, 2012 12:09 AM
          Hi Bharath,

          by default sqlldr stops after 50 errors.
          In order to allow a higher number you need to specify the number of errors allowed while running sqlldr.

          You can do like this:
          sqlldr userid=user/pass control=yourfile.ctl errors=999999
          In this case up to 999999 errors will be inserted in your bad file.

          Regards.
          Al
          • 2. Re: How to get complete list of bad recordswhile running SQLLDR script
            bharathDBA
            Thanks Al.It worked.

            Regards,
            Bharath.