JDBC Oracle Date inserts failing after upgrade from Solaris 9 to 10

[Former Member]

We are using a JDBC Receiver adapter to insert data into Oracle. The data is being passed as XML SQL format.

xml node is

<LOGDATE>23-FEB-2009</LOGDATE>

Oracle column LOGDATE is a DATE column

The insert was successful in PI 7.0 and PI 7.1 until we upgraded the OS to Solaris 10. We know get the following error:

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

expected

We can successfully insert the string '23-FEB-2009' into the Oracle Date column using SQL Plus. Using PI we can successfully insert if we change the string to '23-FEB-09'. We would prefer to change a setting in Solaris or PI so PI will accept '23-FEB-2009' rather than chaning all of our code to use '23-FEB-09'. We can also successfully use the Oracle TO_DATE function in the PI message, but that couples the message to Oracle and also requires a lot of code changes.

Thanks,

Dave

[Former Member]

We found out that our environment had been changed during the upgrade to Solaris 10 to use a default locale of "en_CA.ISO8859-1". Our solution is to go back to using a default locale of "C".

[Former Member]

Hi

You can try this

Open the Source Field in the Mapping

Right click and copy

You will see 2 source fields

Use the Substring Text function for both the source field

First source field Starting position = 0 and Number of character = 7

Second source field Starting position = 9 and Number of character = 2

Concatenate the above 2 source fields and map to target field.

So if you pass 23-FEB-2009 the target field will have 23-FEB-09

Regards

Madhan D