Oracle: Automatic import of data into table

Posted on 2012-09-17
Last Modified: 2013-08-08
I am wondering if there is a way to automatically import data from a csv file into a table based on a schedule

in a directory on the same system that oracle is installed on, csv files will be dropped on a daily basis based on tablename and date

so /home/oracle/import may have a new file dropped in everyday

import080812.csv for august 8th

import080912.csv for august 9th

import081012.csv for august 10th

these files conform with the data structure and i never have a problem importing these manually.

how can i go about not having to do this manually, i just want them imported in automatically every day at 12pm
Question by:futureDBA
    LVL 73

    Accepted Solution

    create  a dbms_scheduler process to read them via external table.  Alter the table definition to point to the new file.  Or use a preprocessor command to invoke a script to rename the new file to some fixed file name and import that.

    or, if you already have a script that does the import use dbms_scheduler to invoke it.
    or use cron to invoke it
    LVL 16

    Assisted Solution

    by:Swadhin Ray
    You can also automate this same using SQL Loader.
    something like below :

    Create the control file i.e. <control file name >.ctl

    <<append or insert >>
    into table <<your table name>> 
    fields terminated by ","
    optionally enclosed by '"'
    varchar_column ,
    date_column   "to_date(:date_column   , 'YYYY-MM-DD  HH24:MI:SS')", 

    Open in new window

    Now create a shell script to load it :

    export ORACLE_BASE=/opt/app/oracle
    export ORACLE_SID=<<your SID>>
    export ORACLE_HOME=$ORACLE_BASE/product/11.1.0
    export PATH=$ORACLE_HOME/bin:$PATH
    sqlldr userid=username@$ORACLE_SID/password control=/home/oracle/import/<control file name >.ctl data=/home/oracle/import/<<your CSV file name>>.CSV skip=1 log=/home/oracle/import/filename.log

    Open in new window

    Then you can schedule it by using cron jobs.
    LVL 20

    Expert Comment

    by:Amitkumar Panchal
    Agree with slobaray.
    LVL 73

    Expert Comment

    sqlloader is just a external table with an extra step

    if you already have a script available that uses sqlloader then by all means reuse it.

    If you have to construct a control file and a script to invoke it, just use an external table.  Same syntax, but no need for anything external to the database except the csv files themselves
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    To add to the mix:  If you are running 11gR2 you can set up a file watcher that will trigger the load:
    LVL 16

    Expert Comment

    by:Swadhin Ray
    I too agree with sdstuber suggestion.

    Author Comment

    i am going to try sdstuber and slightwv suggestions and go with what ever ends up being the best solution for my environment

    Author Comment

    revisiting this question after a couple of months because this has become my number one priority with a project i am currently working on..

    sdstuber, can you point me towards some documentation on your method / examples / or elaborate a bit more please?
    LVL 22

    Expert Comment

    by:Steve Wales
    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to recover a database from a user managed backup

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now