Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> ADODB call Oracle 817 proc - pass array as arg

ADODB call Oracle 817 proc - pass array as arg

From: Eric L. Finney <efinney_at_mitre.org>
Date: Thu, 09 May 2002 15:39:01 -0400
Message-ID: <3CDAD055.D3B29D0A@mitre.org>


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.

compile error:
only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions.

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


Received on Thu May 09 2002 - 14:39:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US