ORACLE Stored Procedure with an array as parameter

[Reinhard]

Is it really true, that in TD6.1 is not possible to have a stored procedure call with an array as parameter ? Like XYZ_Proc(array) ?

This has been no problem under 3.1.

Best regards

Reinhard

[Reinhard]

...and here is a testcase

In TD3.1 it's ok. In TD6.1, there is no data in anArray (see source).

Regards

Reinhard

[markkuvento]

Hi Reinhard,

Add the first parameter to the TD6.1 code:
Set slCommand = 'TestPck.TestArray( nl, anArray )'

BR, Markku

[Reinhard]

Hi Markku,

sorry, I have uploaded the wrong ZIP-File. Even if you add the nl-parameter, there ist no result in the array.

Best regards

Reinhard

[markkuvento]

Hi,
Got the result as supposed with native router (TD6.1 build 28198 / Oracle 10g).
Tested your code 'cause there have been problems with ora prog params before, and was afraid of degeneration.
No can say, why you have problems.

[Reinhard]

Hi Markku,

I have made a new test and this is the result:

If you specify a dynamic array all elements including element [64] are filled, but element [65] and so on are empty.

Regards

Reinhard

[Jeff Luther]

'64' is soooooo binary... I wonder why.

One suggestion: I think you have a string 'abc' in your test case. Change that to 1 character, like 'a' and see if your results are different. Unicode issue??? What if you change array data type to Number, say. That limited to 64 array elements too?

In scanning this thread I don't see (sorry, if I missed it) where you wrote what Oracle DB version and what Oracle client version you are running with v6.1.
What versions are you running?

Even if you add the nl-parameter, there ist no result in the array.

You mean in the 65th element of the array, right? You have been writing that the first 64 elements are OK. BTW, whenever there's a mis-coding or bug found in a test case, it can be helpful to correct that and then attach an updated version, Reinhard.

[Reinhard]

64 is so binary, but 0 to 64 is 65 and that it's not so binary...

Change that to 1 character, like 'a' and see if your results are different. Unicode issue??? ----> it makes no difference

What if you change array data type to Number, say. That limited to 64 array elements too? ---> here it's ok, the array is filled, but I need a varchar2-array.

Another testresult: the limit for varchar2 in stored procedure is 234, if you specify varchar2(235), array-elements > 68 are empty.

running under ORACLE10g, WindowsXP professional

regards

Reinhard

[markkuvento]

Hi,

So the problem is the max allowed number of elements in dynamic array ora prog param.
There has been an undocumented limitation of max ary elems since TD21? As far as I can remember the limit was something over 100?
(Wild quess: it was 128 and now it's 64 'cause of unicode)
I was forced to split the arrays and use a loop for several procedure calls.

Markku

[Jeff Luther]

64 is binary, because...

limited to 64 array elements

-- is 0 to 63, since arrays are 0-origin. That's what I've been taught and that is what I assumed you meant when you wrote '64' in reference to an array.

(Wild quess: it was 128 and now it's 64 'cause of unicode)
I was forced to split the arrays and use a loop for several procedure calls.

Markku: that may be the issue, though as I think about it, the "U" word (Unicode) could/would have an impact on max. chars for a string. I'd be surprised if it impacted the number of string elements in an array. That could be verified (or not) based on a prior suggestion to change data type to Number and see if that 64 limit was reached. And/or change string to a single char.