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 ?

Posted on 2011-10-06
Last Modified: 2012-05-12
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?
Question by:fester62
    LVL 23

    Accepted 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.*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:


    Author Comment

    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.

    LVL 23

    Expert Comment

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

    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now