This may be a silly question, but are you sure that the column names in the controlfile correspond properly to the columns in your table RE_STATS_STAGING? In particular, are you sure that the datatypes of the input data (including the timestamp constant) are compatible with the datatypes in the table definition?
===================================== Jim Silverman Senior Systems Database Administrator Solucient, LLC Telephone: 734-669-7641 FAX: 734-930-7611 E-Mail: jsilverman@(protected)
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Madhavi Kanugo Sent: Wednesday, December 07, 2005 1:20 PM To: 'oracle-l@(protected)' Subject: SQL*Loader Date error (ORA-01858 (See ORA-01858.ora-code.com))
Hello All,
I'm trying to load data using SQL Loader and ran into the ORA-01858 (See ORA-01858.ora-code.com): a non-numeric character was found where a numeric was expected error.
Below is my control file. As you can see, the timestamp is a constant field and I am loading its value from the filename of the data files. I am executing the sql loader from a shell script and creating the control file on the fly. The filename is in the form of: 20051206130101.txt So basically, the shell script replaces the when_changed value with 20051206130101.
I created a temp table with just a date field and tried to insert a value into it. Insert into temp values (to_date('20051207160752','YYYYMMDDHH24MISS')); and there is no error and date conversion is implict.
LOAD DATA INFILE FILENAME APPEND INTO TABLE RE_STATS_STAGING FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( RE_NAME, TG_TYPE, TRUNKGROUP, CONNECTS, HANGUPS, DECLINES, LOOPS, SECONDS, CC_CONNECTED, CC_SETUP, TIMESTAMP CONSTANT "to_date('_when_changed_','YYYYMMDDHH24MISS')" )
Any ideas on where I am going wrong? Any thoughts or help will be appreciated.
Thanks in advance, Madhavi
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii"> <META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7226.0"> <TITLE>RE: SQL*Loader Date error (ORA-01858 (See ORA-01858.ora-code.com))</TITLE> </HEAD> <BODY> <!-- Converted from text/rtf format -->
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT COLOR="#000080" SIZE=2 FACE="Arial">This may be a silly question, but are you sure that the column names in the controlfile correspond properly to the columns in your table</FONT></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"> <FONT COLOR="#000080" SIZE=2 FACE= "Arial">RE_STATS_STAGING? </FONT></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"> <FONT COLOR="#000080" SIZE=2 FACE="Arial">In particular, are you sure that the datatypes of the input data (including the timestamp constant) are compatible with the datatypes in the table definition?</FONT></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"><FONT COLOR="#000080" SIZE=2 FACE="Arial "></FONT></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"> </SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">I'm trying to load data using SQL Loader and ran into the ORA-01858 (See ORA-01858.ora-code.com):</FONT></SPAN><SPAN LANG="en -us"> a non-numeric character was found where a numeric was expected < /SPAN><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">error.</FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">Below is my control file. As you can see, the timestamp is a constant field and I am loading its value from the filename of the data files. I am executing the sql loader from a shell script and creating the control file on the fly. The filename is in the form of: 20051206130101.txt So basically, the shell script replaces the when_changed value with 20051206130101.</FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">I created a temp table with just a date field and tried to insert a value into it. Insert into temp values (</FONT></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"><FONT COLOR="#000000" SIZE=2 FACE="Courier">to_date</FONT></SPAN><SPAN LANG="en-us">< /SPAN><SPAN LANG="en-us"><FONT COLOR="#0000F0" SIZE=2 FACE="Courier">(</FONT>< /SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"><FONT COLOR="#FF0000" SIZE=2 FACE="Courier">'20051207160752'</FONT></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"><FONT COLOR="#0000F0" SIZE=2 FACE="Courier">,</FONT></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"><FONT COLOR="#FF0000" SIZE=2 FACE= "Courier">'YYYYMMDDHH24MISS'</FONT></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG= "en-us"><FONT COLOR="#0000F0" SIZE=2 FACE="Courier">)); and there is no error and date conversion is implict.</FONT></SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT COLOR="#0000F0" SIZE=2 FACE="Courier" >But SQLLOADER is erring out on ORA-01858 (See ORA-01858.ora-code.com).</FONT></SPAN><SPAN LANG="en-us">< /SPAN><SPAN LANG="en-us"> </SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">LOAD DATA </FONT>< /SPAN></P>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">Any ideas on where I am going wrong? Any thoughts or help will be appreciated.</FONT></SPAN></P> <BR>
<P ALIGN=LEFT><SPAN LANG="en-us"><FONT SIZE=2 FACE="Arial">Thanks in advance,< /FONT></SPAN></P>
