Forum Stats

  • 3,855,332 Users
  • 2,264,499 Discussions
  • 7,905,971 Comments

Discussions

Text Importer - reorganized the data

lov2tango
lov2tango Member Posts: 3
edited Sep 15, 2013 9:21PM in General Database Discussions


Hello,

I have searched high and low to solve this problem.

I was trying to import a large text file to a table.  The text file has more that 1 million lines.  The table has only 1 column to hold one line from the text file.  I made sure that the CLEAR TABLE is checked before I start the process.  Commit every 100.

When I checked the resulting table, I noticed that it reoganized the sequence of lines from the text file.

For example, on the text file

1

2

3

4

5

On the table

4

5

1

2

3

How can I import from the text file onto a table without reorganizing the data.  I really need the data on this table to be in the same line sequence as the text file.  I also tried commit every 1 record.

Thanks.  I really appreciate your help and advise.

lov2tango

Tagged:

Answers

  • jgarry
    jgarry Member Posts: 13,844 Gold Crown

    Oracle tables are heap tables, there is no intrinsic order.

    You can either use the order by statement when you select, or use an index-organized table with an order by.  The correct answer depends on what exactly you intend to do with the table, why you feel there should be an inherent order.

  • I was trying to import a large text file to a table.  The text file has more that 1 million lines.  The table has only 1 column to hold one line from the text file.  I made sure that the CLEAR TABLE is checked before I start the process.  Commit every 100.

    Oracle has no 'CLEAR TABLE' command so you must be using some tool or GUI front-end you haven't told us about.

    If you commit every 100 you will just slow things down so just issue a commit at the end of the data load.

    When I checked the resulting table, I noticed that it reoganized the sequence of lines from the text file.

    No - Oracle doesn't 'organize' or 'reorganize' the rows. It just dumps them into a table; there is NO first row or last row. Just like if you throw a bunch of tennis balls into a basket you won't know afterward which one was the first ball you threw in.

    How can I import from the text file onto a table without reorganizing the data.  I really need the data on this table to be in the same line sequence as the text file.  I also tried commit every 1 record.

    Just use my 'tennis ball' example from above. If you want the balls to have an order you need to write a number on each one as you throw it into the basket. Then later you can find ball #1, #37 or sort the balls in order if you want.

    For your use case that means add a second column to the table (e.g. LINE_NO) and use an Oracle sequence to 'number' them.

  • lov2tango
    lov2tango Member Posts: 3

    Now I understand.  Thank you very much!

  • lov2tango
    lov2tango Member Posts: 3

    I did used PL/SQL Developer's Text Importer feature.

  • I did used PL/SQL Developer's Text Importer feature.

    Thanks for sharing but how is that relevant? Your problem is that you need to create your own ordering since the database doesn't create one for you. What tool you use to import the data doesn't really matter unless that tool won't allow you to create an ordering.

This discussion has been closed.