Answered by:
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
- Edited by Vivekanandh007 Monday, January 18, 2016 3:10 PM
Monday, January 18, 2016 3:09 PM
Answers
-
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
- Proposed as answer by Abhishek0127[Abhishek kumar]MVP Monday, January 18, 2016 8:16 PM
- Marked as answer by Angie Xu Thursday, January 21, 2016 2:42 AM
Monday, January 18, 2016 4:23 PM
All replies
-
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
Monday, January 18, 2016 3:41 PM -
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
- Proposed as answer by Abhishek0127[Abhishek kumar]MVP Monday, January 18, 2016 8:16 PM
- Marked as answer by Angie Xu Thursday, January 21, 2016 2:42 AM
Monday, January 18, 2016 4:23 PM