none
BizTalk WCF Oracle DB adapter inserting multiple records Issue RRS feed

  • Question

  • I am having an issue inserting more than 1 record in oracle at a time.  I am running BTS 2009 and generated the schemas from the database to insert  into a table.  I get the error below if there are more than 1record inserted at a time.  If i insert 1 at a time it is fine.  

    The adapter failed to transmit message going to send port "SendPort.WCF" with URL "oracledb://Dev3/". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: line 85, column 237:
    PL/SQL: ORA-00957: duplicate column name
    ORA-06550: line 85, column 1:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 242, column 237:
    PL/SQL: ORA-00957: duplicate column name
    ORA-06550: line 242, column 1:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 344, column 237:
    PL/SQL: ORA-00957: duplicate column name
    ORA-06550: line 344, column 1:


    Basically if there are multiple insert records as depicted below i will get the error. 

    <ns0:Insert xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/STAGE/Table/Table1">
      <ns0:RECORDSET>
        <ns0:Table1INSERT xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/STAGE/Table/Table1"/>
        <ns0:Table1INSERT xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/STAGE/Table/Table1" />
      </ns0:RECORDSET></ns0:Insert>

    Any ideas?
    Thursday, July 15, 2010 3:27 PM

Answers

  • Hi Shawn,

    In Oracle you can use change the "Date" format using NLS_DATE_FORMAT.  Something like

    alter session set nls_date_format='DD-MM-RR';

    where 'DD-MM-RR' is the date fomat of the message going to Oracle.

    Thanks,

    William
     

    Tuesday, July 20, 2010 7:14 PM

All replies

  • Hi,

    Ensure you have set the propety "InsertBatchSize" properly.  It's default value is '1'.

    See MSDN

    http://msdn.microsoft.com/en-us/library/cc185291(BTS.10).aspx

    Thanks,

    William

    Thursday, July 15, 2010 5:11 PM
  • I have already confirmed that setting. I have tried usign the default generated bindings file along with creating a seperate oracleDB send port all with the same result. thanks
    Thursday, July 15, 2010 5:23 PM
  • Hi Shawn,

    Can you confirm that the column names are unique in the Oracle table which you are inserting.

    Thanks,

    William

    Thursday, July 15, 2010 7:18 PM
  • Yes, the column names are unique. I do not think Oracle would allow that anyhow. A few other things i have tried. I changed the binding settings on the custom adapter and found that it will work if change enableSafeTyping to true however this then causes me to get an error of Invalid month on all date columns. I have tried to make the change on the normal OracleDB port and now it will send, it just does not get a response. Thanks, Shawn
    Thursday, July 15, 2010 8:06 PM
  • Are you trying to insert empty rows in the table? Or you truncated the XML for this post? Does the XML you're sending conform to the schema for insert that was generated by the adapter?
    Friday, July 16, 2010 4:21 AM
  • Hi Shawn,

    In Oracle you can use change the "Date" format using NLS_DATE_FORMAT.  Something like

    alter session set nls_date_format='DD-MM-RR';

    where 'DD-MM-RR' is the date fomat of the message going to Oracle.

    Thanks,

    William
     

    Tuesday, July 20, 2010 7:14 PM
  • I think you have not used a looping functoid in your map. This has to be placed in between repeating node in your source schema and Table1INSERT node in target schema.

    I have faced similar kind of issue but it worked after fixing this in map. Let me know if it works. Thanks!

    Below is the error message.

    Error details: Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: line 2, column 2557:

    PL/SQL: ORA-00957: duplicate column name

    ORA-06550: line 2, column 1:

    PL/SQL: SQL Statement ignored ---> Oracle.DataAccess.Client.OracleException: ORA-06550: line 2, column 2557:

    PL/SQL: ORA-00957: duplicate column name

    ORA-06550: line 2, column 1:

    PL/SQL: SQL Statement ignored

    Thursday, February 25, 2016 9:36 AM