none
Oracle Database Adapter throwing ORA-00984 "Column not allowed here" RRS feed

  • Question

  • Hi community,

    I'm trying to connect to an Oracle Database from a new BizTalk 2010 development environment. The Oracle Client is configured and working properly, also the adapter pack is installed, this seems OK so far.

    After set up all generated schemes from one table and creating an "Insert" sendport for inserting a record into the table, I throw a sample-file (already the correct schema) in a watched folder and map it direct to the send port. This is the first test of the Oracle-Adapter from my site as found in many tutorials (e.g. http://soa-thoughts.blogspot.com/2011/04/table-operation-on-oracle-11g-xe-with.html - BTW Many thanks for that!).

    Unfortunately the message will be suspended in the sendport with the following error:

    Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: Row 2, Column 66:
    PL/SQL: ORA-00984: Column not allowed here
    ORA-06550: Row 2, Column 1:
    PL/SQL: SQL Statement ignored ---> Oracle.DataAccess.Client.OracleException: ORA-06550: Row 2, Column 66:
    PL/SQL: ORA-00984: Column not allowed here
    ORA-06550: Row 2, Column 1:
    PL/SQL: SQL Statement ignored
       bei Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
       bei Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
       bei Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
       bei Microsoft.Adapters.OracleCommon.OracleCommonUtils.ExecuteNonQuery(OracleCommand command, OracleCommonExecutionHelper executionHelper)

    I have no Idea why this happens and if it's my fault of configuration. The Soap-Action Header is configured right, any change on this results in an error message regarding the soap header.

    Thanks for any hint or soultion as I am currently starting my BizTalk career.

     


    • Edited by Cithine Tuesday, December 6, 2011 3:24 PM
    Tuesday, December 6, 2011 3:16 PM

Answers

  • I've got it! - I've missed that each sample Record has the attribute "InlineValue filled". Here is the Part of documentation I've overseen.

    Support for specifying inline values in the Insert operation

    You can use the InlineValue attribute in the Insert operation to insert computed values into tables or views in the Oracle database. This is an optional attribute and is available for all simple data records in a multiple record Insert operation. If you specify a value for this attribute, it overrides the specified value of a record. For more information about the InlineValue attribute, see Performing Basic Insert, Update, Delete, and Select Operations on Oracle Tables and Views.

    Therefore Steef-Jans answer was the right hint for my solution. Thank you very much.

    • Marked as answer by Cithine Wednesday, December 7, 2011 1:22 PM
    Wednesday, December 7, 2011 1:22 PM
  • Hi,

    What does your message look like when it is send to Oracle? The error you are getting indicates you do something with column (name) that is not expected. Check the syntax of the statement (i.e. what you want to do) and use column names only where appropriate.

    HTH

    Steef-Jan Wiggers

    Ordina ICT B.V. | MVP & MCTS BizTalk Server 2010

    http://soa-thoughts.blogspot.com/ | @SteefJan

    If this answers your question please mark it accordingly


    BizTalk
    • Marked as answer by Cithine Wednesday, December 7, 2011 1:22 PM
    Tuesday, December 6, 2011 7:46 PM
    Moderator

All replies

  • Hi,

    What does your message look like when it is send to Oracle? The error you are getting indicates you do something with column (name) that is not expected. Check the syntax of the statement (i.e. what you want to do) and use column names only where appropriate.

    HTH

    Steef-Jan Wiggers

    Ordina ICT B.V. | MVP & MCTS BizTalk Server 2010

    http://soa-thoughts.blogspot.com/ | @SteefJan

    If this answers your question please mark it accordingly


    BizTalk
    • Marked as answer by Cithine Wednesday, December 7, 2011 1:22 PM
    Tuesday, December 6, 2011 7:46 PM
    Moderator
  • Thanks for your reply!

    This is the message which is routed to the Oracle-Port:

    <ns0:Insert xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/BTTEST/Table/BIZTALK">
      <ns0:RECORDSET>
        <ns0:BIZTALKRECORDINSERT>
          <ns0:BEZEICHNUNG InlineValue="InlineValue_0">Test 1</ns0:BEZEICHNUNG>
        </ns0:BIZTALKRECORDINSERT>
        <ns0:BIZTALKRECORDINSERT>
          <ns0:BEZEICHNUNG InlineValue="InlineValue_0">Test 2</ns0:BEZEICHNUNG>
        </ns0:BIZTALKRECORDINSERT>
        <ns0:BIZTALKRECORDINSERT>
          <ns0:BEZEICHNUNG InlineValue="InlineValue_0">Test 3</ns0:BEZEICHNUNG>
        </ns0:BIZTALKRECORDINSERT>
      </ns0:RECORDSET>
    </ns0:Insert>

    The Sample Table consists of only one Column named "BEZEICHNUNG". I don't know where to look into the resulting SQL which is send to the database. The Ora error seems clear but why can't the adapter create valid SQL from this?

    Wednesday, December 7, 2011 11:17 AM
  • I've got it! - I've missed that each sample Record has the attribute "InlineValue filled". Here is the Part of documentation I've overseen.

    Support for specifying inline values in the Insert operation

    You can use the InlineValue attribute in the Insert operation to insert computed values into tables or views in the Oracle database. This is an optional attribute and is available for all simple data records in a multiple record Insert operation. If you specify a value for this attribute, it overrides the specified value of a record. For more information about the InlineValue attribute, see Performing Basic Insert, Update, Delete, and Select Operations on Oracle Tables and Views.

    Therefore Steef-Jans answer was the right hint for my solution. Thank you very much.

    • Marked as answer by Cithine Wednesday, December 7, 2011 1:22 PM
    Wednesday, December 7, 2011 1:22 PM
  • Hi,

    I met the same problem today.

    Would you please give a sample of the Insert Message?

    How could you handle the InlineValue?

    Thank you very much!

    Catt

    Friday, December 30, 2011 9:33 PM