Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


data import using SQLLDR

data import using SQLLDR

data import using SQLLDR

I have text data of around 1 million records.
Is the data imported into oracle in the same order as in the text file. I'm using SQLLDR to import data.


RE: data import using SQLLDR

The date will be imported in the order of the sequential file. HOWEVER, remember that if Oracle does not have an index on the table, the data (when retrieved by a SELECT) could be in any order at all. I have generally observed that the data comes out in the same order that it was put it UNTIL changes are made to the table. Then, it's anything goes.

If you want the data to come out in a certain order, add an index. This can also include adding a row number during the load process. Using an index for ORDER BY is THE ONLY WAY to guarantee that the data will come out in a certain order when you use a SELECT statement.

advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

RE: data import using SQLLDR

since you are using oracle 11, why don't you just use an external file and then you can query the data directly from the csv file in any order that you want

Lead Application Developer
New York State, USA

RE: data import using SQLLDR

Thanks johnherman & Beilstwh.
I just wanted to check this out, bcoz in case while importing the data into oracle & anything goes wrong, the data is partially imported.
So the only option left is to delete all the data imported & redo the import process.

RE: data import using SQLLDR

So you're saying that it only imports good data that is clean and correct? That seems like a good thing. bigsmile

There's a directive you can give to SQL Loader to put BAD records that don't load into a separate file ("BADFILE 'filename.bad'"). That can let you address just those problems and then re-attempt the load with the smaller batch. Doing it that way does load a huge bulk of good records, and also separate the (hopefully) smaller number of exceptions that you have to handle manually. That can save a lot of work.

And, as Beilstwh says, you can leave it in an external CSV file, but that introduces a whole other set of problems. Complex joins with the data can be extremely slow, and those BAD records that aren't loading can really break that external data, sometimes in ways that you don't even know you have a problem. It can be a huge pain to debug why you aren't seeing all of your data.

If data integrety is a concern, it is best to load it.

RE: data import using SQLLDR

thanx Sam

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close