Passing pl/sql tables as a variable

[xrodge]

Hi,

I am having a problem with passing pl/sql tables 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

 

[R1972]

Hi,

Oracle Pl/Sql type can't support the VB. Instead of you can write it as 2 procedures.
eg.

procedure xx(cnt out number); --
procedure yy(x number y out varchar2);

call xx initially...it'll give count.

run the loop using count value and call the yy procedure multiple times.

I hope the above hints helpful to u..

Regards,
Raj

 

[xrodge]

I'll try this today, thanks!