2

I'm making a request from a java webapp to an Oracle' stored procedure which happens to have a Timestamp IN parameter.

The way info travels is something like:

javaWebApp --} webservice client --} ws --} storedProcedure

And I send the Timestamp param as a formatted string from the webservice client to the ws.

In the testing environment, it works sending:

SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy hh:mm:ss a");
input.setTimestampField(dateFormat.format(new Date()));

As you see, a formatted string is sent. But in the production environment, it raises an exception

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

It relates to the format not being the same, possibly due to differences in configuration from one DB to the other. I know the testing environment should be a replica of the production site, but it is not in my hands to set them properly. And I need to send the Timestamp-as-a-formatted-string field despite the way they setup the database. Any ideas? Thanks in advance.

**** EDIT ****: I've found the way to make it work properly despite the particular configuration. It is as simple as setting the call instruction in the web service with the appropiate Oracle instructions. I mean, the calling to the Oracle stored procedure went from 

"call PACKAGE.MYPROCEDURE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"

to

"call PACKAGE.MYPROCEDURE(?,?,?,?,?,?,TO_TIMESTAMP(?, 'DD-MM-YYYY HH24:MI:SS'),?,?,?,?,?,?,?,?,?,?,?)"

while the format I set in the procedure calling matches the format sent by the webapp using the SimpleDateFormat stated in the original question, slightly modified:

SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");

Thank you all for the help and the ideas.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Alfabravo
  • 7,493
  • 6
  • 46
  • 82

2 Answers2

1

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 Myers
  • 34,963
  • 3
  • 49
  • 74
  • I'll mark this as the right one, editting the question to explain what I've done. Thanks for your help! :) – Alfabravo Mar 26 '10 at 17:23
0

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.

Doug Porter
  • 7,721
  • 4
  • 40
  • 55
  • 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 Mar 25 '10 at 00:43
  • @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. – Doug Porter Mar 25 '10 at 01:07