Hi All,
I'm a new subscriber to this forum, but have been a great admirer and regular visitor. Very Informative. I've now run into a problem while using SQL Loader. I've used this in the past, but never had suchan irritating issue.
I'm trying to load a delimited file into Oracle using SQLLDR. I have a couple of dates in the INFILE. When I try to declare it as
STMTDATE Date "MM/DD/YYYY hh24:mi:ss"
in the CTL file, I get an error
Record 1: Rejected - Error on table FCSTATEMENTS, column STMTDATE.
ORA-01858: a non-numeric character was found where a numeric was expected
I also tried STMTDATE Date "MM/DD/YYYY hh24:mi:ss" "to_date("stmtdate, 'MM/DD/YYYY hh24:mi:ss'"
I get the same error. I've even tried to set the nls_date_format through SQL+, but didn't work(here's another question: Why am I not getting the same result in TOAD as I got in SQL+ after I altered the nls-date-format?).
The format for the target column is date, in 9i.
The source fields are like: The last but one being the field of interest.
(847)576-5000~~Y~12/31/1992 00:00:00~0
I don't know what I'm doing wrong. I'm running out of options. Any expedited response would be of a great help.
I'm a new subscriber to this forum, but have been a great admirer and regular visitor. Very Informative. I've now run into a problem while using SQL Loader. I've used this in the past, but never had suchan irritating issue.
I'm trying to load a delimited file into Oracle using SQLLDR. I have a couple of dates in the INFILE. When I try to declare it as
STMTDATE Date "MM/DD/YYYY hh24:mi:ss"
in the CTL file, I get an error
Record 1: Rejected - Error on table FCSTATEMENTS, column STMTDATE.
ORA-01858: a non-numeric character was found where a numeric was expected
I also tried STMTDATE Date "MM/DD/YYYY hh24:mi:ss" "to_date("stmtdate, 'MM/DD/YYYY hh24:mi:ss'"
I get the same error. I've even tried to set the nls_date_format through SQL+, but didn't work(here's another question: Why am I not getting the same result in TOAD as I got in SQL+ after I altered the nls-date-format?).
The format for the target column is date, in 9i.
The source fields are like: The last but one being the field of interest.
(847)576-5000~~Y~12/31/1992 00:00:00~0
I don't know what I'm doing wrong. I'm running out of options. Any expedited response would be of a great help.