Re: sqlldr problems with dates and long datatypes

In article <994837723.1945.0.nnrp-02.c2de4217_at_news.demon.co.uk>, "CME" says...
>
>I am trying to upload data into an oracle database using sqlldr and a comma
>seperated file from a sybase database
>I am having two problems:
>1) I can't input dates and times into the oracle date datatype. However I
>can get both dates and times into an oracle date datatype using DBA Studio.
>2) I cannot input a very long string of charactors into a long datatype.
>
>my control file for sqlldr looks something like this:
>----------------------------
>LOAD DATA
>INFILE *
>INTO TABLE X.TBLNAME
>FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'"
>(num,num2,lastupdate)
>BEGINDATA
>1,1,2001/07/04 13:36:00.801000
>-----------------------------
>
>thanks for any help
>
>

Until 9i, fractional seconds aren't supported in the Oracle set of datatypes. In 9i, there are new timestamp datatypes that do support this.

You can use:

LOAD DATA
INFILE *
INTO TABLE X.TBLNAME
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" (num,num2,
  lastupdate "to_date(substr(:lastupdate,1,19),'yyyy/mm/dd hh24:mi:ss')" )
BEGINDATA
1,1,2001/07/04 13:36:00.801000

As for the long issue, the default datatype in sqlldr is a char(255), anything over that will get "too long".

Simply code:

LOAD DATA
INFILE *
INTO TABLE X.TBLNAME
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" (num,num2,
  lastupdate "to_date(substr(:lastupdate,1,19),'yyyy/mm/dd hh24:mi:ss')",   some_long_data char(100000)
)
BEGINDATA

1,1,2001/07/04 13:36:00.801000,xxxxxxxxxxxxxxxxxxxx..........

where 1000000 is some appropriate large number.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp