ADODB call Oracle 817 proc - pass array as arg

Hi,

I have a multi-column list of records (e.g. array, recordset, table, collection) that I would like to pass to an oracle 817 procedure that accepts an argument of a oracle collection object.

I have an MS access 2002 client connecting to an oracle 817 db using the Oracle 8.01.75 ODBC driver. I've created a test package containing a save_depts proc that accepts one input argument. On the client side, I have a class module containing a sub routine called saveDepts that selects from an access table and attempts to pass the recordset or array of dept records to the procedure. I've tried a few things like creating a parameter object and specifying the parameter's data type as adArray but without luck. I'm getting a message about the udt not being public. The deptList() array is based on a public type typDept created in the class module. II'm not sure how to resolve this error.

[Image]

So, is this possible? If it is, is this a good approach? Any help is appreciated.

Eric

Here's the oracle types and procedure definition.

     create type dept_type
        as object (
            dept_code varchar2(50)
           ,mgr_id varchar2(50)
           );

     create type dept_tab_type
        as table of dept_type;


     PROCEDURE save_depts (
         p_depts IN dept_tab_type
        )...

Here's the sub routine from my class module.

     Public Sub saveDepts()

         Dim cn As ADODB.Connection
         Dim cmd As ADODB.Command
         Dim rstGet As ADODB.Recordset
         Dim prmDepts As ADODB.Parameter

         Dim strConn As String
         Dim strGetSql As String
         Dim strSaveSql As String

         Dim thisDept As typDept
         Dim deptlist() As typDept

         Dim i As Integer, lower As Integer, upper As Integer

         Set rstGet = New ADODB.Recordset



         ' get dept list to save

         strGetSql = "SELECT dept_code ,mgr_id " & _
                    "FROM tblDept order by dept_code "
         rstGet.Open strGetSql, CurrentProject.Connection
         Debug.Print "call rstget.getstring"
         Debug.Print rstGet.GetString
         Debug.Print "done rstget.getstring"

     '    If rstGet.RecordCount = 0 Then
     '       MsgBox "The record for " & strDeptCode & " was not
     found", vbExclamation
     '    Else
     '    ReDim deptlist(rstGet.RecordCount)
     'Debug.Print "build deptlist array"
     '       For i = 1 To UBound(deptlist)
     '         deptlist(i).dept_code = rstGet!dept_code
     '         deptlist(i).mgr_id = rstGet!mgr_id
     '         Debug.Print i & "," & rstGet!dept_code & "," &
     rstGet!mgr_id
     '         rst.MoveNext
     '      Next i
     '    End If



         ' define connection string

         strConn =
     "ODBC;DATABASE=testdb;UID=test;PWD=1234;DSN=testdsn;"


         ' define stored procedure call string

         strSaveSql = "{call itams.save_pkg.save_depts(?)}"



         ' establish connection

         Set cn = New ADODB.Connection
         cn.ConnectionString = strConn
         cn.Open



         ' initialize command object

         Set cmd = New Command
         cmd.CommandType = adCmdText
         cmd.CommandText = strSaveSql
         cmd.ActiveConnection = cn

         Set prmDepts = cmd.CreateParameter("deptlist",
     Array(adArray, typDept), adParamInput)
         cmd.Parameters.Append prmDepts
         prmDepts.Value = deptlist()



         ' initialize recordset by executing the procedure call

         Debug.Print "executing..."
          cmd.Execute adExecuteNoRecords
         Debug.Print "completed!!!"



         ' close recordset

         rstGet.Close

     End Sub







text/x-vcard attachment: Card for Eric L. Finney