RE: sql loader question

Maybe this isn't a direct answer to your question but you might try using External Tables and/or NVL2. External Tables will give you a much more comfortable and familiar SQL interface to your data. Also, you should look at NVL2. It gives you the ability to specify what to do in the event that your test is not null.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ryan_gaffuri_at_comcast.net
Sent: Monday, December 17, 2007 12:14 PM To: oracle-l_at_freelists.org
Subject: sql loader question

I rarely use sql loader.

I need to account for nulls and set them to January 1, 9999

I am getting errors when I try to use a to_date function in sql loader even though I have seen examples just like this on the web. This is 10.2

my_date date "to_date(:my_date,'YYYY-MM-DD')"

I get: ORA-01821: date format not recognized

The following works:

my_date date "YYYY-MM-DD"

I am trying to get to something like this:

my_date date
"decode(:my_date,null,to_date('9999-01-01','YYYY-MM-DD'),to_date(:my_dat e,'YYYY-MM-DD')"

I dont think nvl will give me what i want since I still need an "else" mask. At this point, I can't get the basic syntax right.

Here is the header part of the control file

LOAD DATA
CHARACTERSET UTF8
INFILE "load.data" "var 7"
APPEND INTO TABLE my_table FIELDS TERMINATED BY ' ' ENCLOSED By "'" AND "'"

>>> This e-mail and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2

--
http://www.freelists.org/webpage/oracle-l