Returning resultsets from Oracle stored procedures via ADO? 
Author Message
 Returning resultsets from Oracle stored procedures via ADO?

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).


Quote:
> 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.

> ---------------------------------------------------------

> Global Information Solutions Phone: 03 343-7366
> PO Box 8956 Mobile: 025 440-979
> Christchurch Fax: 03 343-7359
> New Zealand http://www.*-*-*.com/
> ---------------------------------------------------------



Sat, 11 May 2002 03:00:00 GMT  
 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;
   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.

---------------------------------------------------------

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
---------------------------------------------------------



Sun, 12 May 2002 03:00:00 GMT  
 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

Quote:

> 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.

> ---------------------------------------------------------

> 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
> ---------------------------------------------------------



Sun, 12 May 2002 03:00:00 GMT  
 Returning resultsets from Oracle stored procedures via ADO?

Quote:
> 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

Really? With stored procs that return a resultset !
I had the same problem as Allan, and the only solution I found was
to switch to Oracle Objects (Oradc.ocx) instead of ADO.
I did find some info on the subject in MS Knowledge Base (eg Q176086),
but it states that you MUST use the ODBC driver for Oracle, on top of
ADO!
You would then make one more person happy (and more intelligent!)
if you could take a little time to tell us how you did.
Thank you.
-- Vianney
--
Free audio & video emails, greeting cards and forums
Talkway - http://www.talkway.com - Talk more ways (sm)


Mon, 13 May 2002 03:00:00 GMT  
 Returning resultsets from Oracle stored procedures via ADO?
Vianney

I can't find the articles that preceed this one, so I'm not sure of the exact
details of your problem, but I've called Oracle stored procedures that return
result sets using ADO and Visual J++ (subject to a few limitations - can't get
more than eight fields in the recordset or more than nine other parameters...).  
The article that I looked at from MS Knowledge Base was Q174679 (HOWTO:
Retrieve Resultsets from Oracle Stored Procedures).  This article describes how
to do the calls using VB.

Hope that helps

Sarah



Quote:


>> 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

>Really? With stored procs that return a resultset !
>I had the same problem as Allan, and the only solution I found was
>to switch to Oracle Objects (Oradc.ocx) instead of ADO.
>I did find some info on the subject in MS Knowledge Base (eg Q176086),
>but it states that you MUST use the ODBC driver for Oracle, on top of
>ADO!
>You would then make one more person happy (and more intelligent!)
>if you could take a little time to tell us how you did.
>Thank you.
>-- Vianney
>--
>Free audio & video emails, greeting cards and forums
>Talkway - http://www.talkway.com - Talk more ways (sm)



Fri, 17 May 2002 03:00:00 GMT  
 Returning resultsets from Oracle stored procedures via ADO?
Sarah's is VERY Similar.....  I used KB Article Q176086

http://support.microsoft.com/support/kb/articles/Q176/0/86.asp

Used ODBC Driver....  A Must according to MS.   Now, I'm trying to see if
ADO can receive a cursor from an Oracle Stored Procedure.....  HUH??
Anyone??

Richard Wittmann

Quote:


> > 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

> Really? With stored procs that return a resultset !
> I had the same problem as Allan, and the only solution I found was
> to switch to Oracle Objects (Oradc.ocx) instead of ADO.
> I did find some info on the subject in MS Knowledge Base (eg Q176086),
> but it states that you MUST use the ODBC driver for Oracle, on top of
> ADO!
> You would then make one more person happy (and more intelligent!)
> if you could take a little time to tell us how you did.
> Thank you.
> -- Vianney
> --
> Free audio & video emails, greeting cards and forums
> Talkway - http://www.talkway.com - Talk more ways (sm)



Mon, 20 May 2002 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Returning resultsets from ORACLE stored procedures - problem not in Knowledge Base

2. How to return Parameters and Recordset from Oracle Stored Procedure with ADO

3. RDO Error while retrieving resultset from ORACLE stored procedure using VB5

4. Problem: retrieve resultsets from Oracle Stored Procedures

5. Hwo do I get a resultset from a Oracle stored procedure from my Vb program

6. Resultset from Oracle Stored Procedure

7. AS/400 and RDO/ADO stored procedure with output params and resultsets

8. AS/400 and RDO/ADO stored procedure with output params and resultsets

9. Return recordset from Oracle Stored Procedure to VB.

10. RETURNING RESULTS FROM ORACLE STORED PROCEDURES WITH MSODBC DRIVER

11. Return value from Oracle stored procedure with VB5?

12. VB5 program calls Oracle stored procedure via ODBC

 

 
Powered by phpBB® Forum Software