5 Replies Latest reply on Jul 9, 2020 3:34 PM by Gaz in Oz

    bad file with column names

    Vicky007

      HI ,

      I would like to know if we can have bad file with column names by default ?

      This will help us to load same file again with correction quickly by using same control file ... currently user need to include column names explicitly or require to change skip first row parameter in control file. Any suggestions ?

       

      We are using oracle 19c linux platform.

       

      Thanks

        • 1. Re: bad file with column names
          Gaz in Oz

          You must have a manual step already to load the .bad file, you'll still need to have that right?

          Remove the skip=1 directive out of the control file and specify it on the command line.

          Now all you have to do when loading the .data file is:

          sqlldr data=file.dat skip=1... # for the original data file.

          When you fix up the .bad file data you just:

          sqlldr data=file.bad... and don't specify skip.

          • 2. Re: bad file with column names
            Vicky007

            i thought of using same script which loads the data in DB. So data team fix and put it in input directory from where job will pick up and load directly without manipulating Control file.

             

            So if we generate bad file with header then it will be smooth processing and does not require any intervention to add column names again at ROW=1 , anyhow it will be helpful to put column name so that they can refer it and fix data easily too .

            • 3. Re: bad file with column names
              Gaz in Oz

              suggested approach in #1 does not manipulate control file, it involves a one time change to remove the "options=(skip=1...)" from the control file and move it to the command line call.

              No need to manipulate any dataf files after that, the source data file or the .bad file if created.

              • 4. Re: bad file with column names
                Gaz in Oz

                Another thought is if you dispense with the skip= altogether and you table contains non-character columns the the header record, record 1 will always fail and get written to the .bad file.

                You just ignore any errors pertaing to record 1, the header record.

                1 person found this helpful
                • 5. Re: bad file with column names
                  Gaz in Oz

                  ...and yet another thought, there are ways to append a line to the beginning of a file use shell commands.

                  Here's one using the bash shell:

                  -bash-4.2$ cat x.dat

                  a,b,c

                  1,2,3

                  -bash-4.2$ cat x.bad

                  1,2,3

                  -bash-4.2$ (head -1 x.dat; cat x.bad;) >x2.dat

                  -bash-4.2$ cat x2.dat

                  a,b,c

                  1,2,3

                  -bash-4.2$ 

                  1 person found this helpful