6 Replies Latest reply: Jan 24, 2013 11:32 PM by 971895 RSS

    sql loader

    926318
      hi sir,
      i have one issue in the loader in a unix script.
      i am using sql loader which will insert more than 1000 record from a .txt file into a table.
      but i want to add one functionality that if more than 500 hundred error occurs then the unix script will get aborted.
      for example if first 10 record are inserted then for 501 error my script has to stopr executing and no data will be saved in the database.
      means alredy inserted 10 rows will get deleted.
      is this possible with sql loader?

      i am thinking that i will first all the record get inserted into the table.
      then i will read the .bad file
      if the line is greater than 500 then i will apply the rollback statement.
      pls let me kinow how to do?

      many thanks in advance..
        • 1. Re: sql loader
          Paul  Horth
          923315 wrote:
          hi sir,
          i have one issue in the loader in a unix script.
          i am using sql loader which will insert more than 1000 record from a .txt file into a table.
          but i want to add one functionality that if more than 500 hundred error occurs then the unix script will get aborted.
          for example if first 10 record are inserted then for 501 error my script has to stopr executing and no data will be saved in the database.
          means alredy inserted 10 rows will get deleted.
          is this possible with sql loader?

          i am thinking that i will first all the record get inserted into the table.
          then i will read the .bad file
          if the line is greater than 500 then i will apply the rollback statement.
          pls let me kinow how to do?

          many thanks in advance..
          Please post to the correct forum

          {forum:id=732}
          • 2. Re: sql loader
            Srini Chavali-Oracle
            Pl post details of OS and database versions.

            Pl see if the ERRORS parameter can help

            http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_params.htm#sthref471

            HTH
            Srini
            • 3. Re: sql loader
              TSharma-Oracle
              Yes, there is the ROWS sqlldr parameter that controls the commit frequency. For example, If you use the SQL*Loader parameter ROWS=1000, we asking sql*loader to commit after every thousand rows are loaded. You may also be able to try ROWS=9999_high_values to get SQL*Loader to commit at the end of the job, but beware of potential undo log (rollback segment) issues, such as the ORA-01555 Snapshot Too Old error.

              The default commit is after 28 records. Youc an increase it to any number.

              Use ERRORS=N in your sqlldr command line or parameter file, where N is an arbitrary large number. I don't know if there is any upper limit for this parameter. As you've allready found ot, the default is 50.
              You can use Error=501 and rows = high number so whenever it will hit an maximum error limit your sqlldr job will quit and it will rollback.
              • 4. Re: sql loader
                926318
                Hi,

                Many thanks for your useful information..

                now i just want to confirm that what happen
                suppose loader load 10 row in database .
                then continiously 501 rows it get error.
                so in this case
                whether my loader will stop executing?
                what about the first 10 rows which was ionserted in the table? where they get committed or rollbacked?

                please let me know above two answer. i have read that loader is auto commit. so i am confusing in the above situation?

                Thanks in advance..
                • 5. Re: sql loader
                  TSharma-Oracle
                  Q)whether my loader will stop executing?

                  Ans) Yes it will quit.


                  Q)what about the first 10 rows which was ionserted in the table? where they get committed or rollbacked?

                  Ans) The default auto commit is after every 28 rows. But you can change this default behavior by ROWS parameter. Like if you give very high number in ROWS(for ex ROWS=999999), now the commit will happen after 999999 not before that. and if your job quits after 500 errors , it should roll back. You can give this a shot.
                  • 6. Re: sql loader
                    971895
                    Per my understanding please look into this one...
                    OPTIONS
                    (SKIP=1, LOAD=0,ERRORS=5)
                    LOAD DATA
                    INFILE 'd:\test.txt'
                    INSERT
                    INTO TABLE emp_info2
                    FIELDS TERMINATED BY ","
                    TRAILING NULLCOLS
                    (
                    empno,
                    ename,
                    description
                    )