When using an external table (flat file) to import data to an Oracle db, so I need the external location to be on the server ?

I have some large csv files containing data that I need to load to an Oracle database. I am not that familiar with Oracle bulk upload processes, but it would appear that the most efficient process for doing this is via an External Table construct. Some files are quite large and in the 100's of megs.

My questions are :
Is this the best process to use?
Can the location containing the csv files be in a directry that is not on the Oracle Server or must it be on the same box?
fester62DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OP_ZaharinCommented:
Q: Is this the best process to use?
- depending on the structure of your data file. alternatively you can also look into using sqlloader to load the data.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/part_ldr.htm
http://www.orafaq.com/wiki/SQL*Loader_FAQ

Q: Can the location containing the csv files be in a directry that is not on the Oracle Server or must it be on the same box?
- it need to be on the same box but not necessary in Oracle folder. if you are using external table, you need to 'register' the directory in oracle, then grant access the directory to the oracle user who will be creating the external table. here is one example the steps to do so:
http://orafaq.com/node/848



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fester62DeveloperAuthor Commented:
OP_Zaharin: When you say depending on the structure of the files what do you mean exactly ? The files are all csv and will have been put into a consistent format by some VBA code such that there are no unexpected elememts, headers footers etc.

Yes, I have come across those the site you listed on External Tables whcih is where I got my sample code from.

Tony
0
OP_ZaharinCommented:
- i mean structure of the data in the files. based on my experience, if the datafile need for manipulation/massaging/validation prior to the data loading, i would use external tables. if the datafile is good and can be load as it is, i would use sqlloader as its much faster - for me. and its more to preferences of one dba.

- i've got used to sqlloader, even when i come across a datafile that requires the data to be cleanup, i would usually use sqlloader to load the data to a temp table, then do the manipulation/massaging/validation in the temp table before inserting it to the actual table.

OP
0
slightwv (䄆 Netminder) Commented:
I agree with everything mentioned thus far.  I just wanted to elaborate a little.

If the CSV file is not on a file system seen by the database server sqlloader is the way to go.  Loading data from files into Oracle is what it does therefore it is pretty good at it.

I would also agree that you might no want to load directly into the destination table just in case something goes wrong and you end up with a partial load.  It might be hard to back out if loaded directly into the main table.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.