Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
data import using SQLLDR
- Thread starter rk68
- Start date
johnherman
MIS
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
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
- Thread starter
- #4
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.
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.
So you're saying that it only imports good data that is clean and correct? That seems like a good thing. ![[bigsmile]](/data/assets/smilies/bigsmile.gif "[bigsmile] [bigsmile]")
There's a directive you can give to SQL Loader to put BAD records that don't load into a separate file ("[tt]BADFILE 'filename.bad'[/tt]"). 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.
There's a directive you can give to SQL Loader to put BAD records that don't load into a separate file ("[tt]BADFILE 'filename.bad'[/tt]"). 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.
- Thread starter
- #6
- Status
