PLSQL table out of range error While getting result from Oracle using Out parameter of type TABLE

Question

In Oracle Package, below is the code that used to define Table , this type is used as out parameter in SP

TYPE someTable IS TABLE OF someRec INDEX BY BINARY_INTEGER;

While Calling this SP using PL/SQL code directly , it works without issues, but when calling from BizTalk , it is throwing below error

An error occurred while processing the message refer to the details section for more information Message ID 7179C2F1-88FA-4A61-997D-4E05451687CDInstance ID 65A82236-F60F-4B73-8BF8-71658F04D235Error Description Microsoft.ServiceModel.Channels.Common.TargetSystemException ORA-06513 PLSQL index for PLSQL table out of range for host language arrayORA-06512 at line 9 --- Oracle.DataAccess.Client.OracleException ORA-06513 PLSQL index for PLSQL table out of range for host language arrayORA-06512 at line 9 at Oracle.DataAccess.Client.OracleException.HandleErrorHelperInt32 errCode OracleConnection conn IntPtr opsErrCtx OpoSqlValCtx pOpoSqlValCtx Object src String procedure at Oracle.DataAccess.Client.OracleException.HandleErrorInt32 errCode OracleConnection conn String procedure IntPtr opsErrCtx OpoSqlValCtx pOpoSqlValCtx Object src at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery at Microsoft.Adapters.OracleCommon.OracleCommonUtils.ExecuteNonQueryOracleCommand command OracleCommonExecutionHelper executionHelper --- End of inner exception stack trace ---Server stack trace at System.Runtime.AsyncResult.EndTAsyncResultIAsyncResult result at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.EndSendAsyncResult result at System.ServiceModel.Channels.ServiceChannel.EndCallString action Object outs IAsyncResult result at System.ServiceModel.Channels.ServiceChannel.EndRequestIAsyncResult resultException rethrown at 0 at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessageIMessage reqMsg IMessage retMsg at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvokeMessageData msgData Int32 type at System.ServiceModel.Channels.IRequestChannel.EndRequestIAsyncResult result at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient2.RequestCallbackIAsyncResult result

I did some google search, below recommendation is repeated everywhere, I don't know if we have such option in BizTalk to increase array size !?!? we just send a request message and receive response message, no array size specified , can anyone give some clue? Since it says Oracle.DataAccess.Client.OracleException, it might be common problem outside BizTalk I guess

ORA-06513: PL/SQL: index for PL/SQL table out of range for host language array

Cause: An attempt is being made to copy a PL/SQL table to a host language array. But an index in the table is either less than one or greater than the maximum size of the host language array. When copying PL/SQL tables to host language arrays, the table entry at index 1 is placed in the first element of the array, the entry at index 2 is placed in the second element of the array, etc. If an table entry has not been assigned then the corresponding element in the host language array is set to null.

Action: Increase size of host language array, or decrease size of PL/SQL table. Also make sure that you don't use index values less than 1.

Thanks in advance 

Vivek

Answer 1

Hi,

When the return table object contains 37 rows, it is able to give response message in BizTalk without any issue , but when it crosses the count say 38 rows, this error  pops-up, any idea you have , please share , it is urgent thing that has to be fixed immediately.

I thought it would be memory issue and reduced the content of the response table inside PL/SQL, no use, some how it restricts row count to 37 ?!?!

Regards

Vivek

Answer 2

Hi ,

I landed into a page (http://biztorque.net/archive/2010/08/11/76.aspx) which says

Resolution 1: Make sure MaxOutputAssociativeArrayElements is big enough to hold your result arrays. (we used 32000)

Resolution 2: Make sure any loops in the PL/SQL packages are not initialising from zero.

I tried resolution 1 , it worked 

Regards

Vivek