Help needed passing arrays between VB and Oracle

[xrodge]

Hi,

I am having a problem with passing pl/sql tables (arrays) as a variable.
I can pass such a table between Oracle procedures without a problem, but I need to be able to pass it to VB and would also like to be able to read an array passed to me from VB.

I have the following VB code:

[tt]Dim iCommand As Command
Set iCommand = New Command
iCommand.ActiveConnection = DB_CONNECT
iCommand.CommandText = "cbk_picklists.cbp_call_array"
iCommand.CommandType = adCmdStoredProc
Dim prmOut As Parameter
Set prmOut = iCommand.CreateParameter("prmOut", adArray Or adVarChar, adParamOutput, 1)
iCommand.Parameters.Append prmOut
iCommand.Execute[/tt]

which in turn calls the following Oracle procedure:

[tt]create or replace package cbk_picklists
is
TYPE num_tab IS TABLE OF CHARACTER
INDEX BY BINARY_INTEGER;
v_count BINARY_INTEGER;
PROCEDURE cbp_call_array (p_array OUT num_tab);
END cbk_picklists;
/
PROCEDURE cbp_call_array (p_array OUT num_tab)
IS
p_array_dec num_tab;
BEGIN
v_count:=1;
p_array_dec(v_count):='a';
v_count:=2;
p_array_dec(v_count):='b';
p_array := p_array_dec;
END cbp_call_array;[/tt]

So, I'm only trying to pass an array with 2 values in it.
Every time I use VB to call the Oracle procedure to basically read this, it comes back telling me "Type Name is invalid".

At this point, I'm stumped as the error message isn't very helpful.

Has anyone else ever passed arrays between Oracle and VB, and if so can they point out where I'm going wrong or otherwise point me in the right direction.

Thanks in advance,

Ken

 

[HezMac]

If you're out there, xrodge, did you ever get this to work???

Does anybody know of a way to pass an array from VB to PL/SQL???

 

[engineer2100]

How about using Ref Cursor from within your procedure and then returning that cursor back. Catch the cursor in a Recordset. Use that recordset as an array or whatever way you like.

HTH. (& made sense!!!)

-Engi

 

[xrodge]

Wow, this thread was from a while back.

No, I could never get VB to pass Oracle an array correctly.

The best I was able to do the other way was to create a block of XML in the procedure and pass it up to VB or direct to the ASP level as a CLOB. Both VB and ASP were easily able to interpret the XML.

I know a fairly easy (but nasty) fix would have been to use temporary tables, but I have never been a fan of using them. They seem to create more problems than they ever solve.