I am trying to insert into an Oracle table from a flat file by calling a service in IS but I am having a problem with a Date field. I first had a BrokerDate error in EAI (I created an insert operation in EAI). I got rid of this error after I unchecked the “Enable Script Security” checkbox which is checked by default in the Java tab, of the Adapter Configuation tool, for my Oracle adapter. Now, I am having another error:
ORA-01858 Non numeric character encountered when character was expected (or something like this)
:v1, :v2, :v3, :v4, :v4, :v5, TO_DATE(:v6, ‘yyyyMMdd’), :v7 etc v6 is the variable defined in EAI that corresponds to my Date field and that I want to be displayed in yyyyMMdd format.
When I don’t specify the format and just keep it like $(HIRE_DATE), a row is inserted in the table and the date value is something like 31/12/1899 when I try to insert a Date of Today.
Any help on how to fix this is greatly appreciated.
When you map a field to a date column in database make sure that field is type of date. If you try to map a non-date field to a data column in database you will get un-predicted result.
Hi, I have a similar problem,
I’ve a record that contains a NULL date, when try to insert into Oracle table, the row is added, but the date field contains ‘12/30/1899 1:01:01 AM’ instead of NULL!
I check the BrokerDate, it is “”.
Written a custom script to set ‘unassigned’ value for the Date field if brokerDate is “”, but still inserted the same ‘12/30/1899 1:01:01 AM’ instead of NULL.
Where are you checking this result? I seem to remember a similar problem relating to an older version of event tracker: i.e. it replaced empty fields with a new Date() (todays date, not 1899 however) when viewing it. However, on inspection of the database, the value was still as it should be.
If this is not the case, I would agree with Raj’s suggestion of ensuring you are inserting a Date type not char.
I’m a Webmethod’s newbie and I’m trying to insert into an Oracle table with a InsertSQL Service via the JDBC Adapter. But I’ve encountered some problems when inserting a field that is NUMERIC in Oracle. In webMethods I can only choose between “java.lang.String” and “java.math.BigDecimal”. The first one produces an error in Oracle, and the second doesn’t seem to do anything. Do you know what I’m doing wrong?
Besides, I’ve got a a timestamp field that with the wmDB package was correctly assigned, but with the InsertSQL service doesn’t. How I’m supposed to do it?
Hi Ignasi,
Your problem is due to Oracle’s date formatting. Determine what the NLS_DATE_FORMAT is for your Oracle DB.
You may also use the “to_date()” function in Oracle with your SQL statement. This will format your input date so Oracle understands it.
If your not familiar with this function, I’ve included a link to help:
[url=“SS64 Command line reference”]http://www.ss64.com/index.html[/url]
The fact is that I’m not inserting the date field. It’s a time stamp auto-generated by the Oracle Server when inserting a row. With the wmDB package it works, but with the JDBC Adapter it inserts a NULL.
Thanks in advance,
I understand now. What version of WM are you using? If 6.01, what service pack is installed. I believe I saw some fixes to the JDBC Adaptor somewhere but not sure where. Check WM Advantage Knowledge Base for available fixes.
I ran into the same issue. Ended up writing a java service that received a string date and converted that string into a date type and returned the date variable.