| Automating loading data into Oracle [message #69724] |
Tue, 26 February 2002 07:13  |
Sue
Messages: 49
Registered: May 2000
|
Member |
|
|
Help!
Can somebody brainstorm on this issue. I have comma delimited text files to be loaded into Oracle periodically. How can I automate this process of loading into Oracle so that a non programmer can load this data into Oracle with some routine commands?
Any help is appreciated.
Sue
|
|
|
|
| Re: Automating loading data into Oracle [message #69726 is a reply to message #69724] |
Tue, 26 February 2002 10:26  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi,
You have to use sqlloader tool for batch loading
in or before 8.1.x.
follow these steps.
1. create a control file for each table load.
for example: if you have data in csv format for
emp table. create a control file emp.ctl
with these commands. You can use notepad or vi for creating this control file.
load data
replace into table test_tab
#replace will empty the table and load the new data
# if you want to append the records then use "append" in place of "replace"
fields terminated by ',' optionally enclosed by '"'
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO)
2. Create a par file with these contents
userid=username/password@dbname
control=full path for the control file(the emp.ctl you have created in the previous step)
data=full path for the data file(your csv file)
log=c:temptest.log (path for the log file will be created automatically during data load)
3. create a .bat file (in win 32) or .sh in unix
with this command.
emp.dat
sqlldr parfile=D:emp.par
Basically you are calling sqlldr exe(the name of this exe will be different for different versions of oracle)
and passing the parfile.
4. now place the .bat file on the desktop
so that any user can just double click on it and run
the data load. You can even schedule it using windows
scheduler.
Read the sql loader doc from oracle to know more about
direct loading and other options.
|
|
|
|
|
|
| Re: Automating loading data into Oracle [message #69755 is a reply to message #69726] |
Fri, 01 March 2002 09:17 |
Sue
Messages: 49
Registered: May 2000
|
Member |
|
|
I am currently testing this from my computer. I have Oracle 8i installed in my computer (I have windows 2000 machine). Currently I am populating the database tables. Once it is all populated we are moving it to a server. I can connect to the database from the SQL plus prompt.
Sue
|
|
|
|
| Re: Automating loading data into Oracle [message #69757 is a reply to message #69755] |
Fri, 01 March 2002 09:40 |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi Sue
Do you have multiple oracle homes(different versions
of oracle) in you win 2k machine?
other wise try doing it mannualy
from the command prompt
cmd.exe
C:> sqlldr userid/password@your_dbname
it will prompt you to enter control file name and
data file name etc..
if you still get unable to connect to the host
then some thing wrong in net*8 configuration..
do a tnsping
C:> tnsping yourdb_name
you should get a OK
if not find for tnsnames.ora files in you PC
and make sure you have an entry for your database
there...
Bala.
|
|
|
|
| Re: Automating loading data into Oracle [message #69759 is a reply to message #69755] |
Fri, 01 March 2002 10:33 |
Sue
Messages: 49
Registered: May 2000
|
Member |
|
|
I tried doing it manualy from the command prompt.
C:> sqlldr asphaltuserasphalt@o8iasphalt
it asked for the control file and I gave the control file name.
It came back with the message that the connect failed.
Then I did a tnsping with this command
C:> tnsping o8iasphalt
It came back again with the connection failed. Finally I tried to do the usual way - went to the directory where my control file exists.
I used this command
sqlldr asphaltuser/asphalt control=addresses
This was successful.
I went and deleted the records I added. I tried two ways to do it.
one giving this following command.
c:sqlldr asphaltuser/asphalt@o8iasphalt
it asked for the control file and I gave a control file it came back - not able to connect.
Second with this command. it asked for the control file and it successfully added the records.
c:sqlldr asphaltuser/asphalt
So I realized probable something wrong with the database name.
But I don't know how to fix this problem.
Sue
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
