Re: function/procedure returning result set

Here's some code snippet I found on this page

PL/SQL :

 ' CREATE OR REPLACE ' PACKAGE DEPARTMENT AS
 '   TYPE  CURSOR_TYPE IS REF CURSOR;
 '   PROCEDURE GET_EMPS (I_DEPTNO     IN  NUMBER,
 '                       O_RESULT_SET OUT CURSOR_TYPE);
 ' END;
 ' /
 ' CREATE OR REPLACE ' PACKAGE BODY DEPARTMENT AS
 '   PROCEDURE GET_EMPS (I_DEPTNO     IN  NUMBER,
 '                       O_RESULT_SET OUT CURSOR_TYPE)
 '   AS
 '   BEGIN
 '     OPEN O_RESULT_SET FOR
 '     SELECT EMPNO, ENAME
 '     FROM EMP
 '     WHERE DEPTNO = I_DEPTNO;
 '   END;
 ' END;
 ' /

Visual Basic :

 Set cmdStoredProc = Server.CreateObject ("ADODB.Command")  Set cmdStoredProc.ActiveConnection = objConn  cmdStoredProc.CommandText = "Department.Get_Emps"  cmdStoredProc.CommandType = adCmdStoredProc  Set param1 = cmdStoredProc.CreateParameter ("Dept_ID", adInteger, adParamInput)
 cmdStoredProc.Parameters.Append param1 param1.Value = testDeptNo  Set rs = cmdStoredProc.Execute

As you can see, the procedure is created using 2 parameters : the Departement No and the resultset

but in VB, it's called with only the DeptNo, and it seems that the execute returns the recordset in the rs object,

But having tried the EXACT SAME THING, I always get an Oracle error saying "Wrong number of parameter or wrong datatype"

First I thought it was because I'm using OLE DB, but I switched to ODBC and it's still the same

any idea ?

Ms. D.H. Harvey wrote in message <8fbpk7$l32$1_at_news.liv.ac.uk>...
>Patrick Joyal (please.reply_at_to.the.newsgroup) wrote:
>: >c http://osi.oracle.com/~tkyte/resultsets/index.html
 

>: "The requested URL was not found."
>
>http://osi.oracle.com/~tkyte/ResultSets/index.html
>
>HTH Helen
>
>: Please someone else, if you have any idea how to solve my problem,
>: (returning recordset to VB), please reply.
 

>: I'm sure Mr. Bakker wanted to help, but none of these links worked,
>: (except deja.com, but I can't find any archives)
 

>: Mean while, I'll be looking in my doc.
 

>: thanks y'all
 

>: Patrick
 

>: >
>: >Your question is a FAQ. Apart from FAQ sites like www.orafaq.com this
>: >newsgroups archives at www.deja.com are also capable of answering such
>: >questions.
>: >
>: >Hth,
>: >
>: >Sybrand Bakker, Oracle DBA
>: >
>: >
>: >Patrick Joyal <please.reply_at_to.the.newsgroup> schreef in berichtnieuws
>: >39188302_at_news...
>: >> Hi,
>: >>
>: >> does someone have an example of a function/procedure in PL/SQL
>: >> that return a result set?
>: >>
>: >> I'm using VB/ADO with Oracle OLE DB provider, so I want to be able
>: >> to store the result set in a Recordset object.
>: >>
>: >> thanks
>: >>
>: >> Patrick
>: >>
>: >>
>: >>
>: >
>: >
Received on Wed May 10 2000 - 00:00:00 CDT