| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Returning resultsets from Oracle stored procedures via ADO?
Hi All,
This is not a trivial exercise... Consider the Package and package body below:
CREATE PACKAGE PKG_TEST
IS
TYPE REF_CURSOR_TYPE IS REF CURSOR;
FUNCTION TEST(PARAMETER1 OUT REF_CURSOR_TYPE) RETURN NUMBER;
END;
CREATE PACKAGE BODY PKG_TEST
IS
FUNCTION TEST(PARAMETER1 OUT REF_CURSOR_TYPE) RETURN NUMBER
IS
BEGIN
OPEN PARAMETER1 FOR SELECT 17 FROM DUAL;
RETURN 55;
In SQL*PLUS we can correctly retrieve the results with:
VARIABLE C REFCURSOR
VARIABLE R NUMBER
EXEC :R := PKG_TEST.TEST(:C)
PRINT R
PRINT C
With ADO (and Visual Basic) I was expecting something like:
Option Explicit
Private Sub Form_Load()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open "Provider=MSDAORA;Data Source=ORACLESERVER;User
ID=USERNEAME;Password=password"
Dim Cmd As Object
Set Cmd = CreateObject("ADODB.Command")
Set Cmd.ActiveConnection = con
Cmd.CommandText = "PKG_TEST.TEST"
Cmd.CommandType = adCmdStoredProc
'Cmd.Parameters.Refresh
With Cmd
.Parameters.Append .CreateParameter("RETURN_VALUE", adNumeric,
adParamReturnValue)
.Parameters.Append .CreateParameter("PARAMETER1", adArray, adParamOutput)
End With
Dim RS As Object
Set RS = CreateObject("ADODB.RecordSet")
RS = Cmd.Execute
MsgBox "Return Value: " + Cmd.Parameters(0).Value MsgBox "Parameter1 Value: " + Cmd.Parameters(1).Value
End Sub
However the adArray type is not appropriate for PARAMETER1 throws and error at that line "Run-time error '30001': Arguments are of the wrong type, are out of acceptable range, or in conflict with one another"
If I set the type to adVariant or adNumeric for PARAMETER1 I get the message "Run-time error '-2147217900 (80040e14)': ORA-06550: line 1, column 17: PLS-00306: wrong number or types of arguments in call to 'TEST' ORA-06550: line 1, column 17: PL/SQL: Statement ignored". This error occurs at the Cmd.Execute line.
Cmd.Parameters.Refresh doesn't seem to provide this info either.
I have successfully returned single values back from the procedure (eg. NUMBER data type) but not ref cursors.
Does anyone have any ideas (preferably sample code) on how to do this. Will consider any OLEDB providers (Merant, Oracle or MS). I've looked at the MS article Q176086 and the idea of PL/SQL tables does not sound the most efficient approach.
Thanks,
Allan.
 |
 |