10 Replies Latest reply: Jul 31, 2013 12:15 AM by 3cd7ad85-b56c-4a9c-ae91-83be047aac2c RSS

    Pls help: SQL Loader loads only one record

    3cd7ad85-b56c-4a9c-ae91-83be047aac2c

      Hi!!!

       

       

      I am trying to insert data into a table using SQL Loader.

      At the command line, i have entered

       

       

      >sqlldr userid=abc\abc control = 'contrl_file_name.ctl'

       

       

      This does not perform any operation and simply hangs...table does not have any data...no data in log file ...

      It simply shows the SQL Loader Release ...production...Copyright messages and does not halt.

       

       

      Am new to SQL loader.... Kindly help

       

      *************************************************************

      Another problem:

       

      I have my loading_data.dat file with multiple entries like the below which are records to be inserted for a single column in my table.

      233207332711<EOFD><EORD> 233208660745<EOFD><EORD> 233200767380<EOFD><EORD>.....

       

      My control file is like this:

      continue_load data

      infile loading_data.dat"

      insert

      into table T1

      fields terminated by '<EOFD><EORD> '

      (msisdn)

       

      when I run the following sqlldr statement, it inserts just one record and says Load Completed- logical record count 1.

       

      Why are my other records not getting inserted?

       

      Message was edited by: 3cd7ad85-b56c-4a9c-ae91-83be047aac2c

        • 1. Re: Pls help: my Sqlldr does not insert data...goes blank
          DK2010

          Hi,

           

          Did you get any Error, what is in default log file generated  by sql loader

          Could you please share the DB version/ OS , Your Control file. and some sample data/ table structure,

           

          Check this link for Sql loader Exp :Oracle SQL*Loader Loader

           

          HTH

          • 2. Re: Pls help: my Sqlldr does not insert data...goes blank
            nagarw31

            Hi ,

             

            Please use below steps, To insert data into table through SQL loader

            1. Create table in your database

            ex

            create test_n

            (

            id1 varchar2(100),

            id2 varchar2(100).

            id3 varchar2(100)

            )

            2.Then create test.txt file(the data that you want to load) into c drive

            ex

            1,26,234

            2,23,345

            and save file with .txt extn

             

            3. Create control file and save in the same location where you  saved .txt file(means c drive)

            ex.

            load data

            infile test.txt

            into table test_n

            fields terminated by ','

            (id1,id2,id3)

             

            and save file with .ctl extn

             

            4.Go to command promte and go to c drive and type below command to insert data into table

            sqlldr schema_name/password@database_name CONTROL = control_file_name

             

            * control_file_name will be your control file.

             

            Thanks

             


            • 3. Re: Pls help: my Sqlldr does not insert data...goes blank
              nagarw31

              Hi..I got the issue

               

              suppose you have a record like

              233207332711<EOFD><EORD>233208660745<EOFD>EORD>233200767380

               

              and you have below table structurea nd control file

               

              create table test_n

              (id1 varcahr2(100),

              id2 varchar2(100),

              id3 varchar3(100)

              )

              and control file like below

              load data

              infile test.dat

              into table test_n

              fields terminated by '<EOFD>EOFD>'

              (id1,id2,id3)

               

              then sql loader read this value as 3 values one for id1  ,second for id2 and third for id3 and insert only one row. because you specified as fields terminated by <EOFD><EORD>  when SQL loader see this will insert the new value into new column.

               

              Hope you undserstand my point

               

               


              • 4. Re: Pls help: my Sqlldr does not insert data...goes blank
                3cd7ad85-b56c-4a9c-ae91-83be047aac2c

                I think I get your point...but what if my table contains just one column (msisdn) and the data file contains the list of records for that single column separated by <EOFD>>EORD>?

                How does SQL loader understand that it has finished one row and is now reading a record from datafile for the second row?

                 

                Also, just noticed that my data file has a single space before every record:

                like:

                233207332711<EOFD><EORD>  233208660745<EOFD>EORD>   233200767380

                and not

                233207332711<EOFD><EORD>233208660745<EOFD>EORD>233200767380

                could this be an issue?

                • 5. Re: Pls help: my Sqlldr does not insert data...goes blank
                  DK2010

                  Hi,

                   

                  You can try this control file

                   

                  load data

                  infile 'loading_data.dat' "fix 12"

                  INTO TABLE T1

                  fields terminated by '<EOFD><EORD>'  TRAILING NULLCOLS

                  (msisdn)

                  HTH

                  • 6. Re: Pls help: my Sqlldr does not insert data...goes blank
                    3cd7ad85-b56c-4a9c-ae91-83be047aac2c

                    Hi,

                     

                    Thanks, this helped, inserted 5 records, after reading 64 rows

                     

                     

                    The log file shows:

                    Total logical records skipped:          0

                    Total logical records read:            64

                    Total logical records rejected:        51

                    Total logical records discarded:        0

                     

                     

                    And all records rejected were due to:

                    Rejected - Error on table T1, column MSISDN.

                    ORA-01722: invalid number

                    • 7. Re: Pls help: my Sqlldr does not insert data...goes blank
                      DK2010

                      Hi,

                       

                      So error message  tells itself, what action required

                      • 8. Re: Pls help: SQL Loader loads only one record
                        nagarw31

                        Also you can do one thing replace'<EOFD><EORD>  ' with comma  and then use the below control file..all your records will get inserted.

                         

                        load data

                        infile test.dat "FIX 13"

                        into table test_n

                        fields terminated by ','

                        (id1)

                        • 9. Re: Pls help: SQL Loader loads only one record
                          Barbara Boehmer

                          Continue_load can only be used with direct path, not conventional path.  You need to specify the record terminator as well as the field terminator, which are the same when your data is only one column.  Please see the example below.  As to  your numeric problem, as you can see, it does not reproduce below.  Your problem could be due to many things.  It could be trying to read something other than what you think it is trying to read, due to something else being wrong.  It could be that you need to use:

                           

                          (msisdn "to_number (ltrim (rtrim (:msisdn)))")

                           

                          to removing leading and trailing spaces and convert it to a number.  You need to provide a copy and paste of a complete example as I have done below.

                           

                          -- contents of loading_data.dat:

                          233207332711<EOFD><EORD> 233208660745<EOFD><EORD> 233200767380<EOFD><EORD>

                           

                          -- control_file_name.ctl:

                          load data

                          infile loading_data.dat "str '<EOFD><EORD>'"

                          insert

                          into table T1

                          fields terminated by '<EOFD><EORD> '

                          (msisdn)

                           

                          SCOTT@orcl12c_11gR2> create table t1 (msisdn  number)

                            2  /

                           

                          Table created.

                           

                          SCOTT@orcl12c_11gR2> host sqlldr scott/tiger control=control_file_name.ctl log=test.log

                           

                          SCOTT@orcl12c_11gR2> set numwidth 20

                          SCOTT@orcl12c_11gR2> select * from t1

                            2  /

                           

                                        MSISDN

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

                                  233207332711

                                  233208660745

                                  233200767380

                           

                          3 rows selected.

                          • 10. Re: Pls help: SQL Loader loads only one record
                            3cd7ad85-b56c-4a9c-ae91-83be047aac2c

                            Hi!!!

                             

                            Thanks, your answer was correct.

                            I had not specified the filed terminator and record terminator distinctly in my control file.

                             

                            I modified my control file to the following and the entire record got loaded, the numeric problem did not arise! Thank you once again!

                             

                             

                            load data

                            infile "data_file_name.dat" "str'<EOFD><EORD>'"

                            into table T1

                            fields terminated by '<EOFD><EORD> 'trailing nullcols

                            (msisdn)