Passing Arrays to Oracle Stored Proc through ADO (VB6)

[kanchana]

I need help on passing Arrays as parameters to Oracle Stored Procedures
using ADO in Visual Basic 6.

The Stored Procedure in Oracle has the following parameters:

IN:
abc1 : char
abc2 : char
abc3 : char


OUT
abd1 :char
abd2 :varchar2
abd3 :char10_array
abd4 :vc2_array

Iam using the parameters collection of the command object using the create
and append methods.

But iam facing problems regarding this.

I even tried using the refresh method but no success.

If sample cud be also provided then double thanks.
regards
kanchana

 

[woyler]

Dim cmd as ADODb.Command
Dim rs as adodb.recordset

set cmd = CreateObject("ADODb.Command&quot
set rs = CreateObject("ADODb.Recordset&quot

cmd.ActiveConnection = YourConnectionString
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_YourProcedure"
cmd.Parameters.Append cmd.CreateParameter("@abc1", adVarChar, adParamInput, 50, vArray(0))
cmd.Parameters.Append cmd.CreateParameter("@abc2", adVarChar, adParamInput, 50, vArray(1))
cmd.Parameters.Append cmd.CreateParameter("@abc3", adVarChar, adParamInput, 50, vArray(2))

Set rs = cmd.Execute()

 

[kanchana]

During the course of finding a solution i found that its not possible to pass an array(directly) to a stored procedure in sql-server from ado(vb).

When vb(ado) doesnot support passing of arrays directly to sql-server stored procedures then oracle seems to be more remote.

There are plenty of workarounds though,one could pass the arrays as a string where each array element would have to be delimited by a delimiter.
Say the array ={1,2,3,4,5}
then this could be passed as a string "1#2#3#4#5"
where # is a delimiter,
Then in the stored procedure , this string needs to be unparsed.

If you come to know of a better solution then do let me know.
thanks
kanchana

 

[woyler]

I did not realize you were trying to pass the array as a parameter without specifying each element.