4 Replies Latest reply on Jul 25, 2018 12:35 PM by EdStevens

    Retain record order after loading from file to table using SQL Loader

    3047423

      Hi Experts,

       

      I need to load a file(.txt) to table. I am able to do this using SQL Loader(Easy to automate).

      After loading it into table the order of the records are changed. I tried to sort the data in table and tried inserting into a new table, still it didn't help.

      I tried googling the answer but still couldn't find the solution.

      Can someone please suggest me how this can be handled.

       

      Thanks,

      Venkat.

        • 1. Re: Retain record order after loading from file to table using SQL Loader
          Gaz in Oz

          With sqlldr you would use the filler field RECNUM parameter to mark  the order of the rows loaded. To get the order the records were loaded as ordered rows, you select from the table and order by recnum.

           

          Have a column in your destination table called, for instance, ordid then in your sqlldr ctl file populate it as:

          Using SQL*Loader to Generate Data For Input

          ...

          ordid RECNUM,

          ...

           

          ...and plese move (top right -> ACTIONS -> Move) your post to the more appropriate forum here:

          Export/Import/SQL Loader & External Tables

          • 2. Re: Retain record order after loading from file to table using SQL Loader
            3047423

            Hi Gaz,

             

            Thanks for your reply. I tried the suggestion by you. The RECNUM is getting generated properly (Yet the records are not in order in table). So tried to insert it into another table with order by on RECNUM column as suggested by you. It is not helping, still the order of the records are random

             

            Thanks,

            Venkat.

            • 3. Re: Retain record order after loading from file to table using SQL Loader
              Gaz in Oz

              Hi Gaz,

               

              Thanks for your reply. I tried the suggestion by you. The RECNUM is getting generated properly (Yet the records are not in order in table). So tried to insert it into another table with order by on RECNUM column as suggested by you. It is not helping, still the order of the records are random

               

              Thanks,

              Venkat.

               

              Oracle does NOT guarantee ordering of rows in a table.

              To get a deterministic order you HAVE to use ORDER BY on a column or columns in the table when selecting from that table.

              If you insert rows into a table with RECNUM via sqlldr, the RECNUM gets assigned to the records as they appear in the data file or data record order. Sequentially unless you use "PARALLEL=true".

              Create a simple test case and post what you are doing, as only you know at the moment. Include the command line, the ctl file small amount of sample data and the select statement you run after the load.
              ...and please move this post to
              • 4. Re: Retain record order after loading from file to table using SQL Loader
                EdStevens

                3047423 wrote:

                 

                Hi Gaz,

                 

                Thanks for your reply. I tried the suggestion by you. The RECNUM is getting generated properly (Yet the records are not in order in table). So tried to insert it into another table with order by on RECNUM column as suggested by you. It is not helping, still the order of the records are random

                 

                Thanks,

                Venkat.

                Rows (not 'records') in a table are like balls in a basket.  There is no order to them.  As Gaz said, if you want to see them in a certain order you MUST use the ORDER BY clause with your SELECT statement.  This is inherent in the entire concept of relational database systems.  If you ever see a case where you get the rows in the order you want without using ORDER BY, then it is purely an accidental result, depending on factors that may not exist the next time you run the query, or the next time you apply a patch or upgrade.