DBAdapter workaround for inserting non-ISO date formats

In SOA there will be many projects which will migrate from using db triggers/procedures to transfer data between different databases/systems to using fusion middleware.

One of the main issues that has DB inserts is due to the legacy way in which Date would have been inserted .It may not be following any ISO standards.

DB adapters would be conforming to iso 8601 xs:dateTime format

From Console error snapshot

               *

ERROR at line 1:

ORA-01830: date format picture ends before converting entire input string

<faultType>0</faultType>

<bindingFault>

<part  name="detail">

<detail> Exception Description: The object [27-Jun-2012 7:12:25 ], of class [class java.lang.String], could not be converted to [class java.sql.Timestamp]. Internal Exception: BINDING.JCA-11636 Could Not Convert Timestamp Exception. Unable to convert a string value in the xml to a java.sql.Timestamp. Even though databases accept strings representing timestamps in a variety of formats, the adapter only accepts strings representing them in xml ISO dateTime format. The input value must be in iso 8601 xs:dateTime format, i.e. YYYY-MM-DDTHH:MM:SS.sss-07:00 </detail>

Solution

1.    Use a custom SQL for insert instead of using Out of the box Insert query.

Since the format that we want in tables are non-ISO format, use TO_DATE function in the custom query with the format - TO_DATE(#DATE_VALUE,'DD-Mon-YYYY HH24:MI:SS'),.

Eg: INSERT INTO LOAD (shipment_id, transaction_id, action_code, create_timestamp, update_timestamp, e1_release_start_date,  e1_release_end_date)    VALUES (#E0Z58SHPID, #E0Z58TRNID, #E0ACTI, TO_DATE(#SSTM,'DD-Mon-YYYY HH24:MI:SS'), TO_DATE(#SSUTM,'DD-Mon-YYYY HH24:MI:SS'),)

2.    Check your XSD element types. Change datetime to string.

      <xs:element name="SSUTM" type="xs:datetime" nillable="true"/>

         <xs:element name="SSUID" type="xs:datetime" nillable="true"/>

to

      <xs:element name="SSUTM" type="xs:string" nillable="true"/>

         <xs:element name="SSUID" type="xs:string" nillable="true"/>

That’s it, now you should be able to insert the records propeprly into legacy databases in date formats not supported by the DB adapter.