I've never tried it, but I just happened to be reading about it in a book.
They have examples. It's called Oracle Programming with Visual Basic by Nick
Snowdon (ISBN 0-7821-2322-8).
Allan Taunt <AllanT_at_gis.co.nz> wrote in message
news:sVE_3.703$tG1.14726_at_news.clear.net.nz...
> 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 Tue Nov 23 1999 - 23:48:12 CST
