Problem with collection of raws

[azp74]

I have a stored procedure which takes two collections amongst its parameters. The first is a collection of raw(32) and the second a collection of ints (both of these are defined as types using varrays).

In my test script both are declared & initialised in the same way:

declare
carray catarray; -- the varray of raws
iarray itemarray; -- the varray of ints
begin
carray := catarray('ABC', '123');
iarray := itemarray(0, 1);

However - when I get in to my stored procedure carray is shown up as a null collection although iarray is a valid collection.

Why is this? Should I explicitly be converting those strings to raws before initialising the collection?

Thanks
Alex

 

[Marco Kalter]

I'm not sure about this, and I haven't tried, but shouldn't you pass the raw values as hexadecimal strings? In this case:

carray := catarray('414243', '313233');

This is 'ABC' and '123' as hexadecimal strings.

 

[azp74]

Sorry - I see where the ambiguity lay in my original post - I gave 'ABC' and '123' as random examples of hex (rather than decimal or otherwise) strings ... the real values I'm using are actually guids which are getting passed in as (for example) '7E1C8CAE58C04194B7DF2C94A054EC31'.

In addition, in my test script I can get a count of carray and pass this value through to the sp and this value is correct - so at the test script level everything is fine.

Elsewhere I am actually passing values like this in to stored procedures which take raw(32) (rather than a collection) and it is working OK, which is what has rather confused me.

In addition, I have just tried changing my catarray from being an array of raws to being a varray of varchar2(32) and attempted to pass this in, with exactly the same result. But my integer array still works just fine.

Hope this extra information is useful.

Alex

 

[azp74]

After some rijigging I have successfully passed in a collection of both varchar2 and raw so there was obviously a bug in my PL/SQL which I was looking for far too hard!

Sorry about that!
Thanks
Alex