Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

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?
  • 2
1 Solution
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.

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:

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.

- 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.

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.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now