Re: function/procedure returning result set

All I want to do is a view accepting parameters, it doesn't have to be a procedure, it can be a view calling a function or whatever...

Patrick Joyal wrote in message <3919943e_at_news>...
>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