?
Solved

Oracle: Automatic import of data into table

Posted on 2012-09-17
10
Medium Priority
?
1,887 Views
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
called


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
0
Comment
Question by:futureDBA
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 672 total points
ID: 38405353
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
0
 
LVL 16

Assisted Solution

by:Swadhin Ray
Swadhin Ray earned 664 total points
ID: 38405537
You can also automate this same using SQL Loader.
something like below :

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

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

Open in new window


Now create a shell script to load it :

#!/bin/sh

export ORACLE_BASE=/opt/app/oracle
export ORACLE_SID=<<your SID>>
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$ORACLE_HOME/bin
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.
0
 
LVL 21

Expert Comment

by:Amitkumar P
ID: 38406475
Agree with slobaray.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 74

Expert Comment

by:sdstuber
ID: 38406513
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
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 664 total points
ID: 38406770
To add to the mix:  If you are running 11gR2 you can set up a file watcher that will trigger the load:

http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse005.htm#CIABIEJA
0
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 38407200
I too agree with sdstuber suggestion.
0
 

Author Comment

by:futureDBA
ID: 38409383
i am going to try sdstuber and slightwv suggestions and go with what ever ends up being the best solution for my environment
0
 

Author Comment

by:futureDBA
ID: 38755366
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?
0
 
LVL 23

Expert Comment

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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month15 days, 9 hours left to enroll

850 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