Re: Returning resultsets from Oracle stored procedures via ADO?

Sorry, I'm don't have the time to really look at your problem but Microsoft have some examples that may help in their knowledgebase. I was able to get everything going with stored procedures with VB, ADo and Oracle.

Richard Wittmann

Allan Taunt wrote:

> 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;
> END;
> END;
>
> This returns '55' as a return value and an Oracle cursor containing one
> record, the value '17'.
>
> 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.
>
> ---------------------------------------------------------
> DBA / Data Architect E-mail: AllanT_at_gis.co.nz
> Global Information Solutions Phone: 03 343-7366
> PO Box 8956 Mobile: 025 440-979
> Christchurch Fax: 03 343-7359
> New Zealand http://www.gis.co.nz
> ---------------------------------------------------------
Received on Wed Nov 24 1999 - 16:27:24 CET