OraFAQ Forum: SQL & PL/SQL » ORA-06502: Host bind array to small (web service callout)

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: Host bind array to small (web service callout)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-06502: Host bind array to small (web service callout) [message #248843]

Mon, 02 July 2007 06:06

marwi
Messages: 7
Registered: July 2007

Junior Member

The following happens on an Oracle 10.2.0.3 on a Sun Solaris 10 server.

I have used JPub to auto-generate PL/SQL proxy classes (and stored procedures/functions) for an external web service, defined by a WSDL file.

One of the PL/SQL procedures generated is supposed to list members on the web service server. The IN/OUT parameters for this are based on a (also auto-generated) type called TBL_VARCHAR2, which is defined as:

CREATE TYPE TBL_VARCHAR2 AS TABLE OF VARCHAR2(256);

The maximum length of the strings returned are 100, so 256 should be large enough.

The definition of the member_list function is:

FUNCTION member_list(
arg0 IN OUT TBL_VARCHAR2,
arg1 IN OUT TBL_VARCHAR2,
arg2 IN OUT TBL_VARCHAR2,
arg3 IN OUT TBL_VARCHAR2,
arg4 IN OUT TBL_VARCHAR2,
arg5 IN OUT TBL_VARCHAR2,
arg6 IN OUT TBL_VARCHAR2,
arg7 IN OUT TBL_VARCHAR2,
arg8 IN OUT TBL_VARCHAR2,
arg9 IN OUT TBL_VARCHAR2,
arg10 IN OUT TBL_VARCHAR2,
arg11 IN OUT TBL_VARCHAR2,
arg12 IN OUT TBL_VARCHAR2,
arg13 IN OUT TBL_VARCHAR2,
arg14 IN OUT TBL_VARCHAR2,
arg15 IN OUT TBL_VARCHAR2,
arg16 IN OUT TBL_VARCHAR2,
arg17 IN OUT TBL_VARCHAR2,
arg18 IN OUT VARCHAR2,
arg19  VARCHAR2,
arg20  VARCHAR2,
arg21 IN OUT VARCHAR2) 
RETURN NUMBER;

Each member property is placed in its own TBL_VARCHAR2, hence the large number of IN/OUT parameters!

The function is called by the following test code:

CREATE PROCEDURE TEST
IS
list_1 TBL_VARCHAR2; 
list_2 TBL_VARCHAR2; 
list_3 TBL_VARCHAR2; 
list_4 TBL_VARCHAR2; 
list_5 TBL_VARCHAR2; 
list_6 TBL_VARCHAR2; 
list_7 TBL_VARCHAR2; 
list_8 TBL_VARCHAR2; 
list_9 TBL_VARCHAR2; 
list_10 TBL_VARCHAR2; 
list_11 TBL_VARCHAR2; 
list_12 TBL_VARCHAR2; 
list_13 TBL_VARCHAR2; 
list_14 TBL_VARCHAR2; 
list_15 TBL_VARCHAR2; 
list_16 TBL_VARCHAR2; 
list_17 TBL_VARCHAR2;
list_18 TBL_VARCHAR2;
var1 VARCHAR2(1000);
var2 VARCHAR2(1000);
var3 VARCHAR2(1000); 
out_message VARCHAR2(1000);
RetVal NUMBER;
BEGIN
list_1 := TBL_VARCHAR2('%'); 
list_2 := TBL_VARCHAR2('%'); 
list_3 := TBL_VARCHAR2('%'); 
list_4 := TBL_VARCHAR2('%'); 
list_5 := TBL_VARCHAR2('%'); 
list_6 := TBL_VARCHAR2('%'); 
list_7 := TBL_VARCHAR2('%'); 
list_8 := TBL_VARCHAR2('%'); 
list_9 := TBL_VARCHAR2('%'); 
list_10 := TBL_VARCHAR2('%'); 
list_11 := TBL_VARCHAR2('%'); 
list_12 := TBL_VARCHAR2('%'); 
list_13 := TBL_VARCHAR2('%'); 
list_14 := TBL_VARCHAR2('%'); 
list_15 := TBL_VARCHAR2('%'); 
list_16 := TBL_VARCHAR2('%'); 
list_17 := TBL_VARCHAR2('%');
list_18 := TBL_VARCHAR2('%');
out_message := ' ';
var1 := '0';
var2 := ' ';
var3 := 'YYYYYYYYYYYYYYYYYY';
RetVal := MEMBER_LIST( 
        list_1, 
        list_2, 
        list_3, 
        list_4, 
        list_5, 
        list_6,
        list_7,
        list_8, 
        list_9,
        list_10, 
        list_11, 
        list_12,
        list_13, 
        list_14, 
        list_15,
        list_16,
        list_17,
        list_18,
        var1,
        var2,
        var3, 
        out_message );
END;

This test procedure compiles without problems.

As a note, there are only a few (16) members available on the web service server, so the list can only be 16 entries long.

When I run this test procedure in SQL*Plus, the result is:

SQL> exec osptest;
ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1

And that is my problem!! I can't figure out why or how to initialize the bind arrays sufficiently.

I have tried initializing the TBL_VARCHAR2 with more than one entry:

list_1 := TBL_VARCHAR2('%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%',...);

In fact up to 100 rows, with the same result.

As a curiosity, the variables passed to the procedure must contain at least 1 character. Otherwise the resulting SOAP request produces a closed tag:

<arg1/>

instead of

<arg1> </arg1>

And the closed tag is unfortunately not understood by the web server... Go figure...

I have not been able to find anything on the web or in other forums, so if anyone can shed some light on the "Host array to small" problem, I would greatly appreciate it.

Best regards
Martin

Report message to a moderator

Re: ORA-06502: Host bind array to small (web service callout) [message #251091 is a reply to message #248843]

Thu, 12 July 2007 06:45

marwi
Messages: 7
Registered: July 2007

Junior Member

As it turns out, this had nothing to do with the table declarations or initializations...

At the end of the test procedure I printed the out_message with DBMS_OUTPUT. (Not included in the example code)

And although the message was short enough to be contained in the allocated VARCHAR2, the error appeared when trying to print it, due to the restrictions of 255 characters on DBMS_OUTPUT.PUT_LINE.

Not a very accurate error message in my opinion...

Report message to a moderator

Re: ORA-06502: Host bind array to small (web service callout) [message #441910 is a reply to message #251091]

Wed, 03 February 2010 19:16

random_bell
Messages: 1
Registered: February 2010

Junior Member

I had the same problem.
I removed all the dbms_output statements and the code executed fine.

Thanks for the tip.

Report message to a moderator

Previous Topic:	ORACLE KEEP FIRST to MS SQL
Next Topic:	using self join to get such order,why?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 10:06:43 CDT 2024