Calling a procedure owned by another schema when using Oracle LOB Adapter

recently I experienced an issue when using the Oracle LOB adapter which I thought I share here….

If you are trying to call a stored procedure using an account that does not own the proc, the adapter fails with an error similar to the below one :

Event Type: Error
Event Source: BizTalk Server 2006
Event Category: BizTalk Server 2006
Event ID: 5754
Description:
A message sent to adapter “WCF-Custom” on send port
Error details:
Microsoft.ServiceModel.Channels.Common.TargetSystemException:
ORA-06550: line 1, column 7:
PLS-00201: identifier ‘[YOUR PROC HERE]’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored —> Oracle.DataAccess.Client.OracleException
ORA-06550: line 1, column 7: PLS-00201: identifier ‘[YOUR PROC HERE]’ must be declared ORA-06550: line 1, column 7:
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.OracleDB.OracleDBXmlParser.ExecuteSingleCommand(OracleCommand command, Boolean recordPresent, Boolean accumateRowAffected)
at Microsoft.Adapters.OracleDB.OracleDBXmlParser.ExecutePackProcFunc(AdapterDataTable parsedMessage)

this happens because the adapter fails to fully qualify the proc call with the schema that owns the proc.

to fix this you will need to apply the hotfix described here : http://support.microsoft.com/kb/950101

The hotfix cannot be download, so you will need to ring Micrsoft and request it. 

After applying it, all works as expected 🙂