Oracle Dates through Data Provider - ORA-01858

[Paul Long]

Hi,

I'm having a nightmare when trying to insert records into an Oracle
database that contain dates. I've believe I should be using the
to_date() function but I continually get the error:

"ORA-01858: a non-numeric character was found where a numeric was
expected"

I'm reading from a text file and the datetime values are as follows:

12-DEC-2003 12:01:22
12-DEC-2003 12:01:23
12-DEC-2003 12:01:24
12-DEC-2003 12:01:25

If I carry out straight forward SQL upon the database I can insert
records without any problems. E.g.:

INSERT INTO MyTable (Name, OccurrenceTime)
VALUES ('Test', to_date('31-Dec-2004 10:47:50', 'DD-MON-YYYY
HH:MI:SS') );

However, my problem is achieving the same results from my C#
application that uses the DataTable structure and OracleDataAdapt er
object.

The DataTable's Rows.List[0] contains:

ItemArray[0] = "Test"
ItemArray[1] = "to_date('3 1-DEC-2004 10:47:50', 'DD-MON-YYYY
HH:MI:SS')"

However, when I call the Update() method of OracleDataAdapt er and pass
the DataTable as the parameter I get the ORA-01858 error.

Can I pass: 'to_date(....)' via the DataTable? It appears from what
I've found on the newsgroups that you cannot pass a literal date
straight into Oracle.

Please, if anyone can help, I would very much appreciate it.

Regards,

Paul.

[Mark D Powell]

pa*********@hot mail.com (Paul Long) wrote in message news:<4c******* *************** ****@posting.go ogle.com>...

Hi,

I'm having a nightmare when trying to insert records into an Oracle
database that contain dates. I've believe I should be using the
to_date() function but I continually get the error:

"ORA-01858: a non-numeric character was found where a numeric was
expected"

I'm reading from a text file and the datetime values are as follows:

12-DEC-2003 12:01:22
12-DEC-2003 12:01:23
12-DEC-2003 12:01:24
12-DEC-2003 12:01:25

If I carry out straight forward SQL upon the database I can insert
records without any problems. E.g.:

INSERT INTO MyTable (Name, OccurrenceTime)
VALUES ('Test', to_date('31-Dec-2004 10:47:50', 'DD-MON-YYYY
HH:MI:SS') );

However, my problem is achieving the same results from my C#
application that uses the DataTable structure and OracleDataAdapt er
object.

The DataTable's Rows.List[0] contains:

ItemArray[0] = "Test"
ItemArray[1] = "to_date('3 1-DEC-2004 10:47:50', 'DD-MON-YYYY
HH:MI:SS')"

However, when I call the Update() method of OracleDataAdapt er and pass
the DataTable as the parameter I get the ORA-01858 error.

Can I pass: 'to_date(....)' via the DataTable? It appears from what
I've found on the newsgroups that you cannot pass a literal date
straight into Oracle.

Please, if anyone can help, I would very much appreciate it.

Regards,

Paul.

Paul, I am not familiar with the tool you are using but does your
program error immediately or is it processing for a little while
before it dies? If it is processing then you might just have some
invalid data in some of your input.

Second check the date data to see if 24 hour clock is in use or if you
need to account for an AM/PM indicator. For the 24 hour clock you
would need HH24 in your format mask.

The idea of what might be messing you up is to check to see if the low
digit days are entered as '01' or just '1'. If may be that the tool
has a problem related to number of digits for the first through ninth.

Good Luck,
-- Mark D Powell --