Using Visual Basic 6 ADODB, I am struggling try to get data from temporary table:
Please note that we had no problema with client 11.2, but we are receiving error with Oracle CLient 12.1.
The stored procedure code:
In the package is defined an user type from column type of table:
TYPE tpart_num IS TABLE OF mais.tb_vendors.part_num%TYPE
INDEX BY BINARY_INTEGER;
The user type tpart_num (varchar) is used in following stored procedure belonging to same package:
PROCEDURE sel_part_num(
part_num OUT tpart_num
) IS
CURSOR partnum_cur IS
SELECT part_num
FROM mais.tb_vendors
ORDER BY part_num;
pcnt NUMBER DEFAULT 1;
BEGIN
FOR singlerec IN partnum_cur
LOOP
part_num(pcnt) := singlerec.part_num;
pcnt := pcnt + 1;
END LOOP;
END;
The following code is working for the Oracle Client 11.2:
Private Sub Command2_Click()
Dim vOut As Variant
Dim lRec As Long
Dim moCn As ADODB.Connection
Dim moOraConStr As String
moOraConStr = "DSN=xxxxxxxxxx;DB=yyyyyyyyy;UID=zzzzzzzzz;PWD=aaaaaaaaaaaaaa;"
Set moCn = New ADODB.Connection
With moCn
.ConnectionString = moOraConStr
.CursorLocation = adUseClient
.Open
End With
Dim aAux()
Dim iRec As Integer
Dim oRs As ADODB.Recordset
Dim oCmd As ADODB.Command
' On Error GoTo ErrorHandler
msStatusMessage = ""
msErrorMessage = ""
Set oCmd = New ADODB.Command
With oCmd
Set .ActiveConnection = moCn
.CommandType = adCmdText
.CommandText = "{call mais.mais_pkg.sel_part_num({resultset 50000, part_num})}"
End With
Set oRs = New ADODB.Recordset
With oRs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
Set oRs.Source = oCmd
oRs.Open
If oRs.EOF Then
msStatusMessage = "No records found."
Else
oRs.MoveFirst
oRs.MoveLast
lRec = oRs.RecordCount
oRs.MoveFirst
ReDim aAux(lRec, 1)
For iRec = 1 To lRec
aAux(iRec, 1) = Trim("" & oRs!PART_NUM)
oRs.MoveNext
Next
msStatusMessage = "SUCCESS"
End If
oRs.Close
vOut = aAux
Set oRs = Nothing
Set oCmd = Nothing
End Sub
but changing Oracle Client from 11.2 to 12.1, an error is raised at oRs.Open:
So, I have done several attempts to define an adodb output parameter:
Dim oPar As New Parameter
Set oPar = oCmd.CreateParameter("part_num", <???????>, adParamOutput, 500)
oCmd.Parameters.Append oPar
but I don't know how to define a:
TABLE OF mais.tb_vendors.part_num%TYPE as type in Create Parameter call; as far also changing the CommandText property from "{call mais.mais_pkg.sel_part_num({resultset 50000, part_num})}" to "{call mais.mais_pkg.sel_part_num({resultset 50000,?})}" but the error however raising.
Please, could you help me in coming out of this roadblock?
Thanks,
Cosimo
Senior Software Engineer