none
Decimals not working when calling Oracle stored procedure RRS feed

  • Question

  • Hi all

    I have a problem regarding decimals when calling an Oracle stored procedure. In order to trouble shoot this, I have created a very small BizTalk 2013 solution that mimics the problem;

    In my solution I have used the "add generated items..." with the "Consume Adapter Service" to generate schemas and UDT for a stored procedure in Oracle that uses a complex type as parameter. The complex type has three fields in it, and one of them is a NUMBER(12,4).

    In this solution, BizTalk received an XML document with three fields in it. One of them is an xs:decimal and there is a map that transforms the input-XML into the XML for the Oracle connection. The send port uses the WCF-Custom adapter with the oracleDBBinding. 

    I can see in the tracking, that the XML the send port sends contains this:

    <ns3:DECIMALTAL_FELT>4.5</ns3:DECIMALTAL_FELT>

    which looks fine to me. But the stored procedure inserts the value into a table and it's the "45" instead of "4.5" that is inserted into the table :-( 

    So a couple of questions;

    1. The WCF-adapter with the oracleDBBinding. I assume that it works like this; The adapter receives the XML and reads the values from it. It then creates a connection to the Oracle database and calls the stored procedure with the right parameters. Is this correct? Or is the entire XML sent to Oracle which then parses the XML itself and calls the stored procedure? Basically I need to figure out if the adapter misreads the value "4.5" or Oracle misreads it.

    2. Any thoughts as to what may go wrong? The XML clearly has a "4.5" inside it. But somehow the value 45 is saved in the Oracle-table. And it's not the Stored procedure failing. The exact same stored procedure is used from an old BizTalk 2009 installation and here it works just fine.

    Thanks in advance!


    eliasen, representing himself and not the company he works for.
    Five times MVP and four times MCTS in BizTalk.
    Blog: http://blogs.eliasen.dk/technical/

    Tuesday, October 24, 2017 9:22 AM

All replies

  • The command is built and executed against the server from the local client.  Works the same with SQL Server.

    The first place I would look is in Oracle.  Is it possible somehow this is the correct behavior?  If you send 45.0, does 450 end up in the table?  Does the column type match the parameter?

    Tuesday, October 24, 2017 10:45 AM
  • The command is built and executed against the server from the local client.  Works the same with SQL Server.

    The first place I would look is in Oracle.  Is it possible somehow this is the correct behavior?  If you send 45.0, does 450 end up in the table?  Does the column type match the parameter?

    Hi and thanks for answering :-)

    The existing environment using BizTalk 2009 has been running against the ORacle database for years without issues. It's my new BizTalk 2013 version that fails. So I am not too confident anything is wrong in Oracle. But I am happy to be proven wrong - I just want this fixed! ;-)

    Anyway, if I try 45.0 it ends up as 45 in the database and not 450 it seems. I hadn't tried that before...

    The column types match, as far as I can see. decimals in the schemas in BizTalk and NUMBER(12,4) in Oracle. And again; IT works fine in the BizTalk 2009 version. I have just taken the source code and compiled it for BizTalk 2013...

    Any other thoughts? :-)

    Thanks!


    eliasen, representing himself and not the company he works for.
    Five times MVP and four times MCTS in BizTalk.
    Blog: http://blogs.eliasen.dk/technical/

    Tuesday, October 24, 2017 11:00 AM
  • Check regional/internation settings for service account the posting to Oracle
    Could be different on the new setup

    For service accounts without desktop access ti's the default user

    On windows 10 the key is "HKEY_USERS\.DEFAULT\Control Panel\International"

    /Peter

    Tuesday, October 24, 2017 11:09 AM
  • Check regional/internation settings for service account the posting to Oracle
    Could be different on the new setup

    Thanks :-)

    Unfortunately, I doubt the regional settings have anything to do with the issue. I just tried the exact same BizTalk solution, but instead of calling a stored procedure that takes a complex type as a parameter, it just takes three simple types, one of which is a decimal.

    This works fine. So the XML input for BizTalk has a decimal inside it, and it is mapped to the generated schema for calling the SP and then sent to the adapter. All OK. But when mapping the same input to the XML for the SP that takes a complex type as a parameter, it fails.

    So somehow, the UDT and/or the adapters handling of the UDT must be at fault. Or?

    Thanks.


    eliasen, representing himself and not the company he works for.
    Five times MVP and four times MCTS in BizTalk.
    Blog: http://blogs.eliasen.dk/technical/

    Tuesday, October 24, 2017 12:03 PM
  • Check regional/internation settings for service account the posting to Oracle
    Could be different on the new setup

    Hi

    The service account on the new server has sDecimal = "," and sMonThousandSep = "."

    On the old server, the user has the exact same settings. So that shouldn't be the problem.

    Thanks.


    eliasen, representing himself and not the company he works for.
    Five times MVP and four times MCTS in BizTalk.
    Blog: http://blogs.eliasen.dk/technical/

    Tuesday, October 24, 2017 1:05 PM