Re: sql loader question

ryan_gaffuri_at_comcast.net schrieb:
> 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
>
> my_date date "decode(:my_date,null,to_date('9999-01-01','YYYY-MM-DD'),to_date(:my_date,'YYYY-MM-DD')"
>
>
>

Well, this behaviour is documented:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_field_list.htm#sthref1244

Small example:
SQL> !cat a.ctl
LOAD DATA
INFILE a.dat
TRUNCATE INTO TABLE A
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(id integer external,
dt date "YYYY-MM-DD" "decode(:dt,null,'9999-01-01',:dt)", id1 integer external)

SQL> !sqlldr userid=scott/tiger control=a.ctl log=a.log

SQL*Loader: Release 10.2.0.3.0 - Production on Mon Dec 17 23:29:26 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 2

SQL> select * from a;

        ID DT                         ID1
---------- ------------------- ----------
         1 15.10.1965 00:00:00          3
         2 01.01.9999 00:00:00          4

Of course, nvl seems to be perfectly suitable as well.

Best regards

Maxim

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