A: 

What does your query look like in the input prepared statement? That error indicates that Oracle doesn't like the date format you have passed in. Your test environment may have a different NLS_DATE_FORMAT set on the database or machine/driver being used.

Dougman
Indeed and that's why I'm saying the testing environment differs from the production environment.I'll expand the post to make myself clear
Alfabravo
@Alfabravo: So the stored procedure is where the query is executing. What Oracle datatype is this timestamp variable you are passing in at the stored procedure level? Compare the NLS_DATE_FORMAT settings in the Production and the Test environments on the boxes hosting the webservice that calls the stored procedure.
Dougman
+1  A: 

The default NLS_DATE_FORMAT generally doesn't include the time and only a two-digit year. It is probably either DD-MM-YY or MM-DD-YY.

If the WS receives a string and the database stored procedure needs a timestamp, then the two of them will need to negotiate the format mask. Either the WS, when it connects to the database, should set an explicit date format, or the database should be able to accept a string and convert it using a hard-coded format.

Unless there is some particular negotiation you have defined in the WS, nothing the JavaWebApp or WebServiceClient will be able to influence the format that the database assumes the WS is using.

All that said, I'd have a look around any other code at your end and see if there's anything doing a similar translation. You may find something else using a specific format.

Gary
I'll mark this as the right one, editting the question to explain what I've done. Thanks for your help! :)
Alfabravo