Passing Arrays to a oracle stored proc 1

[PongPing]

Im posting this for the third time now. It seems that hardly anyone uses the "new" features of oracle 8. If you do - please answer this:

Did you ever succeed in passing an array of variables to an oracle stored procedure? This operation should be possible since oracle 8 and mdac 2.5. Its not that urgent but i'd like to know whats wrong over here - I'm attaching a sample of code that doesn't work to make clear what i'm trying to reach...


Dim CON As New ADODB.Connection
Dim QSQL As String
Dim COM As New ADODB.Command
Dim Param1 As ADODB.Parameter
Dim RS As New ADODB.Recordset

Dim a(255) As Integer, i As Long

' Connect Database
CON.ConnectionString = "provider=MSDAORA;DATA SOURCE=x;USER ID=y;PASSWORD=z"
CON.CursorLocation = adUseServer
CON.Open

' Prepare stored procedure
COM.ActiveConnection = CON
COM.CommandText = "package.ProcName"
COM.CommandType = adCmdStoredProc

' Prepare parameters
Set Param1 = COM.CreateParameter("Input", adArray Or adInteger, adParamInput)
COM.Parameters.Append Param1

For i = 1 To 255
a(i) = i
Next i
COM.Parameters(0).Value = a

' Execute Procedure
RS.CursorLocation = adUseServer
RS.CursorType = adOpenKeyset
RS.LockType = adLockReadOnly
Set RS = COM.Execute


The procedure is defined like this:
TYPE T_NumList IS VARRAY(255) OF NUMBER;
PROCEDURE ProcName(ar IN T_NumList);



This throws a: Type name is invalid

Any help is appreciated - thanx

 

[PongPing]

Maybe some day...

 

[PongPing]

Just refreshing to see if somebody knows by now...
You don't have to answer in detail - just attach a working pece of code ( if you can ;-)

 

[listener22]

Assuming that the type name invalid comes from the line
TYPE T_NumList IS VARRAY(255) OF NUMBER;
how about reviewing the syntax for TYPE declarations? Jim

oracle, vb

 

[Frodo123]

Here is a link to how it's done.

http://www.classicity.com/oracle/htdocs/forums/ClsyForumID124/6.html

 

[GnuKemist]

Hi PongPing.

I also have the same problem as you and was wondering if you were able to solve it? I have also done the same thing that you did. The only difference was that my procedure expected a table-type of numbers as the IN argument. Any feedback will be greately appreciated!

Sincerely,
Gnu

 

[Guest_imported]

I'm having the similar problem... I'm trying to call a stored procedure and pass it some values from variable I declared... PongPing did you ever figure this out??? Is please point me in the right direction. cwilson50@hotmail.com