SQL Loader question

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)")

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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------