Subject: Re: writing ascii data to oracle DB,HOWTO?
Thilo Gelenk (TG@techsoft.de) wrote:
: Hi,
: I am in the first stage of developing an untility to
: convert app. specific ascii data into an ascii file,
: which data I want to "insert" into the ORACLE DB.
:
: -Makes it sense to write the typical SQL commands like
: "SELECT * FROM ..." in my ascii file around the data
: I want to insert (names and floating point numbers) into the
: ORACLE DB ? Or are there nicer ways ?
:
: -What have I to do to bring (half)automatically
: this file into the ORACLE DB ?
: Any smaller examples or hints would be GREAT !
:
: Thank you very much
: I would appreciate it,
: if you could email me also directly:
: --------------------------------------------------
: Thilo Email: TG@techsoft.de
Thilo,
All you need to do is use the SQL*Loader utility. It comes with the Oracle
database. A full explanation is beyond the scope of an email, but you can learn
about it in the "Oracle7 Server Utility User's Guide".
It basically takes an ASCII file and loads it into Oracle tables, reporting any
errors in a nice, consistent format. The files are easiest to load if they are
in a tab/comma delimited format, or a fixed-length format.
The OS command is: sqlload keyword=value, keyword=value, ...
or: sqlldr keyword=value, keyword=value, ...
For example,
sqlldr userid=scott/tiger control=controlfile.ctl log=load.log
The other part is to have a control file. An example is:
load data infile data.txt
append into table TINO
fields terminated by "," optionally enclosed by '"'
(ID, Last_Name, First_Name, SSN, Eye_Color)
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 80+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page