Calling stored procedure leads to different results in SOA 10g and SOA 12c

Question

I am porting a BPEL process from SOA 10g to SOA 12c.

I configured a DBAdapter to call the same stored procedure, with the same input payload.

The problem is that on SOA 10g everything is fine, and the procedure returns as custom output an error code of 0 and "OK" as message. In SOA 12c, instead, the procedure breaks at some point and returns an error code of -1722 and "Invalid number" as message (which is for sure an internal handling of the ORA-01722 database error). I cannot debug the stored procedure to see at which line it breaks.

My question is: what "low-level" parameter could impact this? This is a list of the ideas I've had so far and checks I've already done:

• The database on which SOA was installed uses a different encoding from AL32UTF8, which is the recommended one. I don't know if this could be a problem, but I guess not since both SOA versions use the same database.
• The input payload is the same in both cases on all relevant fields. I checked by downloading the XML from the Enterprise Manager and comparing with Meld; at least from this I couldn't notice any difference.

I guess that at some point there is an issue in encoding which sends odd data from WebLogic to the database. I just enabled the logs for the DbAdapter specific connection pool that is used, I will update the question with relevant information as soon as I can obtain some (I have to wait for instances to start, I am not able to launch them myself).

Answer 1

I'd guess it's an NLS issue (most ORA-01722 turn out to be NLS-related in my experience).

Things you should check:

• do you use the same NLS settings on both Oracle instances?
• do you use the same NLS settings on both clients / DBAdapters?
• do you have floating-point numbers in your input (e.g. "12.1" - this will happily parse with English NLS settings but raise an ORA-01722 if you're using German settings)