none
Error while using Oracle LOB Adaptor for BizTalk 2006 R2 RRS feed

  • Question

  • All,

     

    I want to do a simple query which retrieve records from an Oracle database table.  When I call the Oracle adaptor I the following 2 errors 1 one warning are written to the event viewer.

     

    Error message and warnings are in order of time

     

    Any help would be greatly appreciated.

     

    ----------- Error #1 --------

     

    Event Type: Error
    Event Source: Microsoft BizTalk Adapters for Enterprise Applications
    Event Category: None
    Event ID: 0
    Date:  3/26/2008
    Time:  2:59:02 PM
    User:  N/A
    Computer: SHUTTLE
    Description:
    System defined exception
    Call to Query@OracleDb://ORCL4BTS/INCIDENT/Tables/EVENTS
        Implementation = OracleDb://ORCL4BTS/INCIDENT/Tables/EVENTS

            Source: OracleDb
            Error Code: 936 (0x3a8)
            Cause: OracleDb://exception=DBException (Unique ID <none>)
        HY000 : [Oracle][ODBC][Ora]ORA-00936: missing expression


        Exception data:
            struct DBException =
                WideString StatusCode = "HY000"
                WideString Description = "[Oracle][ODBC][Ora]ORA-00936: missing expression
    "

     

    ----------- Error #2 --------

     

    Event Type: Error
    Event Source: Microsoft Biztalk Adapters for Enterprise Applications
    Event Category: None
    Event ID: 0
    Date:  3/26/2008
    Time:  2:59:02 PM
    User:  N/A
    Computer: SHUTTLE
    Description:
    Error transmitting message: <SOAP-ENV:Fault xmlnsTongue TiedOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"><faultcode>SOAP-ENVTongue Tiederver</faultcode><faultstring>Request ID: Unknown
    Exception Type: System defined exception
    Exception Info:
        Exception occurred:
                Source: OracleDb
                Error Code: 936 (0x3a8)
                Cause: OracleDb://exception=DBException (Unique ID &lt;none&gtWink
            HY000 : [Oracle][ODBC][Ora]ORA-00936: missing expression


            Exception data:
                struct DBException =
                    WideString StatusCode = "HY000"
                    WideString Description = "[Oracle][ODBC][Ora]ORA-00936: missing expression
    "</faultstring><detail><exposedBig SmileBException xmlns:exposed="http://schemas.microsoft.com"><exposedTongue TiedtatusCode>HY000</exposedTongue TiedtatusCode><exposedBig Smileescription>[Oracle][ODBC][Ora]ORA-00936: missing expression
    </exposedBig Smileescription></exposedBig SmileBException></detail></SOAP-ENV:Fault>

     

    ----------- Warning --------

    Event Type: Warning
    Event Source: BizTalk Server 2006
    Event Category: BizTalk Server 2006
    Event ID: 5743
    Date:  3/26/2008
    Time:  2:59:02 PM
    User:  N/A
    Computer: SHUTTLE
    Description:
    The adapter failed to transmit message going to send port "OracleDB_Port" with URL "OracleDb://ORCL4BTS_ea13eb22-0a8c-413e-a365-c09258f3b2ec". It will be retransmitted after the retry interval specified for this Send Port. Details:"Error transmitting message: <SOAP-ENV:Fault xmlnsTongue TiedOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"><faultcode>SOAP-ENVTongue Tiederver</faultcode><faultstring>Request ID: Unknown
    Exception Type: System defined exception
    Exception Info:
        Exception occurred:
                Source: OracleDb
                Error Code: 936 (0x3a8)
                Cause: OracleDb://exception=DBException (Unique ID &lt;none&gtWink
            HY000 : [Oracle][ODBC][Ora]ORA-00936: missing expression


            Exception data:
                struct DBException =
                    WideString StatusCode = "HY000"
                    WideString Description = "[Oracle][ODBC][Ora]ORA-00936: missing expression
    "</faultstring><detail><exposedBig SmileBException xmlns:exposed="http://schemas.microsoft.com"><exposedTongue TiedtatusCode>HY000</exposedTongue TiedtatusCode><exposedBig Smileescription>[Oracle][ODBC][Ora]ORA-00936: missing expression
    </exposedBig Smileescription></exposedBig SmileBException></detail></SOAP-ENV:Fault>".

     

    Wednesday, March 26, 2008 7:27 PM

Answers

  • Hi Kevin,

     

    I just checked your table description and found "COMMENT" field name is RESERVED word in Oracle. So try to Alter your table and replace "COMMENT" with some other name which is not a reserved word in Oracle. I am sure this will solve your problem.

     

    Regards,

    Sanjay

     

    Monday, March 31, 2008 10:48 AM

All replies

  • This looks like a query syntax error.Did you use the query builder to create your Oracle query?

    This error occurs when a required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE.

    Try to execute it on SQLPlus, it will give you exception if query is incorrect.

     

    Regards,

    Sanjay

    Thursday, March 27, 2008 12:59 PM
  • Sanjay,

     

    This is my first attempt to do this and I am very new with BizTalk in general.  Basically,  I am trying to reproduce a lab exercise in my own environment to support some prototyping that I am doing. Here is what I've done.

     

    I read in an XML file using the File Adaptor,  this message is then sent to the OracleDB send port to the Query Operation on that port.  The XML message was greated by generating an instance of the schema which was created when I added the "genearted item" for my database table.

     

    The XML message looks like

     

    <ns0:Query xmlns:ns0="http://schemas.microsoft.com/[OracleDb://ORCL4BTS/INCIDENT/Tables/EVENTS]">
      <ns0:Filter></ns0:Filter>
      <ns0:MaxRows>10</ns0:MaxRows>
    </ns0:Query>

     

    The tutorial that I am working from can be found at http://msdn2.microsoft.com/en-us/library/bb226425.aspx

     

    Regards, Kevin

     

     

     

     

    Thursday, March 27, 2008 2:04 PM
  • I hope you have followed all the steps given in lab exercise properly. The XML message pasted here is correct only. The only doubt i have here is your table "EVENTS" description. Please check it once again, it should not contain any reserved word like i mentioned earlier, otherwise it looks OK.

     

    And try query any existing table like Emp or Dept to check whether it works properly or not. Send me the table description of EVENTS table if possible.

     

    Regards,

    Sanjay

    Friday, March 28, 2008 5:04 AM
  • Hi Kevin

     

    You could also try out the new WCF-based Oracle DB adapter, now that you are using BizTalk Server 2006 R2. Built on ODP.NET, it has a lot of enhancements over the existing BizTalk Oracle adapter.

     

    Friday, March 28, 2008 9:13 AM
  •  

    Sanjay,

     

    Thanks for you help with this

     

    Below is a description of the table in my database

     

    SQL> desc events
     Name                                   Null?           Type
     -----------------------------------------  --------------      ----------------

     ID                                        NOT NULL    NUMBER(38)
     PRIORITY                            NOT NULL    NUMBER(38)
     COMMENT                                             VARCHAR2(4000)
     LATITUDE                            NOT NULL    NUMBER
     LONGITUDE                         NOT NULL    NUMBER
     STATUS                               NOT NULL   VARCHAR2(1)

     

    ...Kevin

    Friday, March 28, 2008 1:00 PM
  • Hi Kevin,

     

    I just checked your table description and found "COMMENT" field name is RESERVED word in Oracle. So try to Alter your table and replace "COMMENT" with some other name which is not a reserved word in Oracle. I am sure this will solve your problem.

     

    Regards,

    Sanjay

     

    Monday, March 31, 2008 10:48 AM
  • Sanjay,

     

    Who knew that COMMENT was a reserved work in Oracle - Obviously not I Smile

     

    I works like a champ now.

     

    Thanks for your help with this. 

     

    Best Regards, Kevin

     

     

    Monday, March 31, 2008 2:40 PM