locked
not able to update in Oracle Table decimal values from Biztalk 2006 R2 using Oracle Adapter (Oracle(r) E-Business Suite) RRS feed

  • Question


  • We are not able to update in Oracle Table decimal values from Biztalk 2006 R2, below are the softwares used for this activity.
    Kindly put your thoughts on this.

    BizTalk 2006 R2
    Oracle Adapter (Oracle(r) E-Business Suite)
    OS is Windows Server 2003 R2 (Standard Edition – Service Pak2).
    Oracle Client software (9.2.0.8 32 bit)

    Tuesday, May 19, 2015 10:55 AM

All replies

  • Hi Rajeev,

    Can you put in some more details.. like the entire decimal is gone? Or .0000 is being removed.. What is the actual issue?


    Praveen Behara
    MCST : BizTalk Server 2006 R2, 2010

    Tuesday, May 19, 2015 11:19 AM
  • In case we are sending 10.65 Oracle is updating only as 10 and discarding the decimal values.

    Getting ORA-01722: invalid number errors on BIZTALK application server when trying to insert decimal values.
    Inserting data using sqlplus is working fine and not able to insert same from app server.


    Rajeev Kumar Singh


    Tuesday, May 19, 2015 12:00 PM
  • what is the actual datatype associated with the field in Oracle ?

    When you generated the Oracle Insert schema as part Consume Adapter what is the xml data type associated with this field ?

    Are you assigning this value in a MAP ? or using a xsd generated class to populate this ?

    Regards.

    Tuesday, May 19, 2015 2:36 PM
  • Please see below answers:

    > · what is the actual datatype associated with the field in Oracle ?

    NUMBER(10,2) 

     
    > · When you generated the Oracle Insert schema as part Consume Adapter what is the xml data type associated with this field ?

    xsd:string with number pattern (as generated by BizTalk).
    >
    > · Are you assigning this value in a MAP ? or using a xsd generated class to populate this ?

                          Assigning the value in MAP, no C# class used.


    Rajeev Kumar Singh


    Wednesday, May 20, 2015 7:36 AM
  • What is the source datatype ? If this is a string then you need to check the source data. It may not be 10.65 and may be coming as 10 (in the source message). Alternately if this is coming as float/decimal type then ideally you should be doing a decimal.Parse and then decimal.ToString("g") to ensure you write the decimals back into the destination.

    Regards.

    Wednesday, May 20, 2015 9:06 AM

  • > What is the source datatype ? If this is a string then you need to check the source data. It may not be 10.65 and may be coming as 10 (in the source message). Alternately if this is coming as float/decimal type then ideally you should be doing a decimal.Parse and then decimal.ToString("g") to ensure you write the decimals back into the destination.

    The map is functioning correctly and not loosing the data while formatting to Oracle schema.
    If the source sends 10 or 10.65, it is being sent the same to ODBC/oracle.


    Rajeev Kumar Singh

    Wednesday, May 20, 2015 1:58 PM
  • So if you're sure that 10.65 is reaching Oracle and then is it displaying only 10 (UI or Toad problem) or actually in the DB there is only 10.00 stored then you need to open a case with Microsoft against the Oracle Adapter. It is possible that while the adapter handles base numeric types, it may/may not address NUMBER as a data type properly.

    Alternatively you can write a .Net program to do a similar task just to ensure that the problem is not with the Oracle side of things... maybe there is an issue with Oracle handling serialization of string with pattern into NUMERIC types?

    Regards.

    Wednesday, May 20, 2015 2:19 PM
  • We are trying to know what Oracle is receiving -it's 10.65 or 10

    Since an older version of Oracle client(ver. 9) is in BTS Application Server, there is no support to get the details from Oracle. We have planned to upgrade the Oracle client to 11 to know the trace from Oracle.

    Is there any way to get the Log files from Oracle for the failed case, as per the settings Oracle gives Log only for Success cases. :-( , is it true ?


    Rajeev Kumar Singh

    Thursday, May 21, 2015 5:25 AM
  • Hi,

    Any solution to this issue? I am also facing the same problem.

    I am sending the data to the Oracle Package and in-terns it will do some processing and eill insert the data to Oracle table. We are also using UDT's just fyi.

    I have also debug the oracle package, from BizTalk we are able to send the data correctly with decimal precision 12.34, but when package receives the data precision value doesn't seems to be present it just has value 12.

    Champs let me know what could be the issue here?. Is it because UDT's doesn't support precision "."?

    Thanks,

    Wednesday, April 26, 2017 9:22 AM