URGENT: How to insert a Date field into an Oracle table

, ,
1

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.

Thanks.

2

I spent about 2 hours on the phone with webMethods trying to figure this one out. I will admit that it is a weird one - Here was the solution that finally worked for me:

INSERT INTO BSN_PRVDR (BSN_PRVDR_ID, NAME, CREATE_PROG_NAME, CREATE_DATE, CREATE_USER_ID, CHG_LST_PROG_NAME, CHG_LST_DATE, CHG_LST_USER_ID)
VALUES (?,?,?,TIMESTAMP ‘%createDate%’,?,?,TIMESTAMP ‘%createDate%’,?)

For whatever reason, you need to preface date fields with TIMESTAMP, and then give it the format YYYY-MM-DD HH24:MI:SS, even if you have your dbParamTypes set to TIMESTAMP…

If you don’t need time set, you can use the default Oracle date mask, ‘DD-MON-YY’.

Try this out and let me know if it works…

Brad