Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Returning resultsets from Oracle stored procedures via ADO?

Returning resultsets from Oracle stored procedures via ADO?

From: Allan Taunt <AllanT_at_gis.co.nz>
Date: Wed, 24 Nov 1999 11:53:11 +1300
Message-ID: <sVE_3.703$tG1.14726@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 - 16:53:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US