I have a problem inserting the timestamp into oracle database. i have run the following query in database: insert into table VALUES (‘VIJAY10’,‘78956’,‘89855’,to_date(‘2006-12-21’, ‘YYYY-MM-DD’),‘AB’,‘C’,null,‘000’,‘000’,null,null,to_timestamp(‘2006-12-21-17.55.10.111111’, ‘YYYY-MM-DD-HH24.MI.SS.FF6’),to_timestamp(‘2006-12-21-17.55.10.222222’, ‘YYYY-MM-DD-HH24.MI.SS.FF6’),‘srcd’).it worked for me. i can see the data in the table.
then i went to webMethods(for adapterservice-custom sql) and copied the same query, in runtime iam passing the input for a timestamp field as : 2006-12-21-17.55.10.222222. but when i actually run the query it is throwing an error:
com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime
(Adapter Service): Unable to invoke adapter service vijay:settlement.
[ADA.1.314] Cannot set data for the input field “UPDT_DTM”
Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff.
As with most things in software, there are many ways to skin this cat.
If your requirement is to simply insert the timestamp representing the current time into an Oracle table, you can replace the ‘?’ substitution variable with the Oracle function sysdate or, if you wanted a time 15 minutes in the future, sysdate + (15/1440) as that adds a fraction of a day (1440 minutes) to the current time.
If you need to pass in some other value, you can set the data type to java.lang.String and pass a string like “2006-12-27 02:10:46.0171”. Note the space between the “27” and the “02” and the presence of fractional seconds after the “46”.
You can also get the current date using the pub.date:getCurrentDate, format it using pub.date:formatDate with the pattern “yyyy-MM-dd hh:mm:ss.SSSS” to adjust the timezone, if necessary, and set the data type of your input variable to java.lang.String.
If you don’t need to reformat the results of pub.date:getCurrentDate, you can set the data type of your input timestamp column to java.util.Date and pass the date object directly to the insert adapter service.
In my current project, we are frequently working with XML dateTime data types and inserting them into Oracle timestamp columns. To make this a bit simpler, we created a Flow service called xmlDateToOracleTimestamp that accepts only an XML dateTime string and returns a string formatted for easy insertion into an Oracle timestamp column.
According to the error, the expected format of the string does not match the format of the string you are passing. Are you passing a string or a timestamp to the adapter service? Trust that the error message is telling you exactly what is wrong.
Thanks you all for your advice, I have got my problem solved when i gave the format as yyyy-MMM-dd hh:mm:ss.SSSSSS. I have given 3 M’s because oracle accepts dd-MON-yyyy format.