Home > BizTalk Server > The Oracle Adapter in BizTalk Adapter Pack v2.0

The Oracle Adapter in BizTalk Adapter Pack v2.0

Has anyone been successful creating schemas based off of an Oracle package using the new Oracle adapter (BAP v2.o)?  We had a Business Adapter Pack v1.0 send port that utilized an Oracle package.  When we upgraded to v2.0, it stopped working showing an error like this:

Event Type: Warning
Event Source: BizTalk Server 2006
Event Category: (1)
Event ID: 5743
Date:  2/12/2010
Time:  11:57:06 AM
User:  N/A
Computer: YOUR_SERVER
Description:
The adapter failed to transmit message going to send port “ProcessGSTUpsert.WCF” with URL “oracledb://oracle_server_name/”. It will be retransmitted after the retry interval specified for this Send Port. Details:”Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to ‘UPDATE_PROCEDURE’
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored —> Oracle.DataAccess.Client.OracleException ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to ‘UPDATE_PROCEDURE’
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at Microsoft.Adapters.OracleCommon.OracleCommonUtils.ExecuteNonQuery(OracleCommand command, OracleCommonExecutionHelper executionHelper)
— End of inner exception stack trace —
<Server stack trace:
at System.ServiceModel.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)”.

I tried recreating the schema used by the Oracle adapter, but it complains as follows:

The categories in the ‘Added categories and operations’ list contain no operations for metadata generation.

This worked before the upgrade.  Any ideas?

15-Feb-2010 Update:

See my reply to Antti – the runtime aspect of the problem was figured out.  I’ll see if I can find a solution to the schema-generation problem.

Advertisements
Categories: BizTalk Server
  1. manas
    February 12, 2010 at 8:52 pm

    Maybe you need to change the value of the binding property “SkipNillNodes”.

    If that doesnt solve the issue, can you send me an email with:

    1. Screenshot of the add adapter service reference wizard when you try generating schemas
    2. Input XML
    3. SP definition.

    • February 15, 2010 at 9:10 am

      I tried setting SkipNilNodes to false (default was true), but that didn’t help. I’ll send you an email with the details as soon as I can get a definition of the package containing the stored procedure (I need to get this from a DBA). Thanks for offering to help.

  2. Antti Somersalo
    • February 15, 2010 at 12:16 pm

      Yes, it appears the runtime error we receive is the same. You prompted me to try the xsi:nil approach using XSL to determine whether to pass the value if provided or pass xsi:nil=”true” if not provided, like what you did – something like this:

      <b:P_USER_ID>
      <xsl:choose>
      <xsl:when test=”a:UserID”>
      <xsl:value-of select=”a:UserID”/>
      </xsl:when>
      <xsl:otherwise>
      <xsl:attribute name=”xsi:nil”>true</xsl:attribute>
      </xsl:otherwise>
      </xsl:choose>
      </b:P_USER_ID>

      That fixed the runtime problem, which is great news. However, why can’t we generate a new schema from the Oracle package? That’s still an issue; however, since we happened to have generated a schema with BAP v1, we at least have a schema we can work with (and apply the runtime fix described above). We’ll only have a problem with other packages we may need to generate a schema from (assuming the problem repeats).

      I think Microsoft should have made the BAP v2 behavior identical to v1. This difference doesn’t allow customers with v1 projects to simply update to v2 – code changes will be required. That’s my 2 cents anyway.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: