SQL Loader question
- From: Teresa Redmond <oracle@xxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 30 Jun 2004 14:51:17 -0400
Hello,
I posted this to c.d.o.tools yesterday and have no response, and
thought maybe those of you here who don't look there may have an idea.
I am using variable length comma separated values data files with SQL
Loader into Ora 8.1.5. I have been reading the documentation on how
to load data from one csv record into two (or more) tables, and the
information is extensive, but all I can find deals with positional
data, not variable length data. My control file is this:
<load_d.ctl>
LOAD DATA
INFILE 'file.csv'
BADFILE 'file.bad'
DISCARDFILE 'file.dis'
replace
INTO TABLE schema.table
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(col1 nullif col1=blanks "upper(:col1)",
col2 nullif col2=blanks,
col3 nullif col3=blanks "upper(:col3)",
col4 nullif col4=blanks "upper(:col4)",
col5 nullif col5=blanks "upper(:col5)",
col6 nullif col6=blanks "upper(:col6)",
col7 nullif col7=blanks "upper(:col7)",
col8 nullif col8=blanks "upper(:col8)",
col9 "UPPER(:col9)")
</load_d.ctl>
I was thinking that I could add a line to that file like:
<next_lines>
INTO TABLE schema.table2
WHEN col10 <> null <--- question on this, too
(col10 nullif col10=blanks)
</next_lines>
The documentation I'm reading is Case 5 of SQL Loader, loading into
multiple tables, and the example file is ULCASE5.CTL. I'd give a link
but they never go back to the exact location you clicked to, only to
the index page; but it's in the 8.1.5 documentation:
<http://download-east.oracle.com/docs/cd/F49540_01/DOC/index.htm>
under Oracle 8i Utilities. The case, as I said, uses positional data,
but I am using csv; also there's the question of comparison in that
second INTO TABLE WHEN statement. I couldn't find an answer to "when
there is no value after the last comma on a record". Sorry I don't
have a better way to put that...
Anyway, ORA 8.1.5, WinXP (that I'm running this from), Win2K Pro (the
DB is on that), and please let me know if you need more information.
Thank you all,
--
Teresa Redmond
Programmer/Analyst III
Anteon Corporation
tredmond at anteon dot com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
