Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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.
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
End Sub