Re: Oracle

Lance:

You want to use the Oracle Sqlloader utility.

The example is from a 9i windows environment, the only difference is the
syntax on the file specification)

The following example is run from an environment in which you've installed
the oracle database (environment variable ORACLE_SID is set to the database
SID), or an Oracle client environment (The environmental variable TWO_TASK
is set) and from which sqlldr (the loader utility) is run.

line command:

sqlldr userid/password control=C:\XXX.ctl log=C:\XXXloadlog_prod.log

Alternately you can run this in an environment and specify the database
connect string, for which a tnsnames.ora file entry is setup for the
database 'connectstring', This example has the command line specifying the
log file location.

sqlldr userid/password@connectstring control=C:\XXX.ctl
log=C:\XXXloadlog_prod.log

The oracle database userid and password will need to have table insert
privileges for the table being loaded / updated.

The control file (XXX.ctl) referenced above contains an example of a tilda
'~' delimited input file, it shows the syntax for specifying the input
file, and also shows use of Built-in functions for removing spaces. It
also shows how to specify a default value for a column not supplied in the
output file.

We opted for '~' as data we loaded contained commas.

The MS spreadsheet contains 4 rows of data, inprocess.fau is specified in
the XXX.ctl file ... and contains the 'tilda' delimited files.

(See attached file: inprocess.fau)(See attached file: SITE_UOP.xls)(See
attached file: XXX.ctl)

Here is an example of another control file (for a different load)

LOAD DATA
INFILE 'T715_Update_Key_Flexfield.csv'
BADFILE 'T715_Update_Key_Flexfield.bad'
DISCARDFILE 'T715_Update_Key_Flexfield.dsc'
REPLACE
INTO TABLE XXX_UPDATE_KEY_FLEXFIELD_TMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(ASSET_NUMBER CHAR
,DESCRIPTION CHAR
,LOCATION CHAR
)

You can specify skip (in case of a partial load), and number of errors
(allowed / to ignore) ... check the Oracle Utilities manual for details.

If you are loading in place on the 10g server.

Roger Siemens, BSc (Hon)
Database Supervisor
Potash Corp Inc.
email: rnsiemens@potashcorp.com
Tel: (306)933-8751
Fax: (306)956-5055

                                                                           
             Lance Levsen
             <lance@catprint.c
             a> To
             Sent by: SLG <linux@slg.org>
             owner-linux@slg.o cc
             rg
                                                                   Subject
                                       Oracle
             12/11/2006 11:29
             AM
                                                                           
                                                                           
             Please respond to
               linux@slg.org
                                                                           
                                                                           

Hey all,

Anyone off hand know of a cmdline command to append a CSV/TSV file into
an oracle table?

Cheers,
lance
Received on Mon Dec 11 13:03:34 2006