Re: stored proc problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 25 Feb 2000 22:04:31 -0500
Message-ID: <1ggebs4p1hdbbs7vafo2duie1fftcdaffu@4ax.com>

A copy of this was sent to kev <kevin.porter_at_fast.no> (if that email address didn't require changing) On Fri, 25 Feb 2000 15:26:18 +0000, you wrote:

>Hi,
>
>I am trying to get a stored proc to return a ref cursor. Here's the code
>I have at the moment:
>
>create or replace package types
>as
> type cursorType is ref cursor;
>end;
>/
>
>create or replace procedure retcurs_proc
>(mycurs out types.cursorType)
>is
>begin
>open mycurs for select * from emp;
>
>end retcurs_proc;
>/
>
>
>This compiles with no errors. When I do exec retcurs_proc(), I get this
>error:
>
>PLS-00306: wrong number or types of arguments in call to 'RETCURS_PROC'
>
>Why do I get the error? The procedure has no IN variables so how is
>there a wrong number of parameters in the above call?
>

it has an out parameter -- a parameter by any other name is still a parameter ;)

ops$tkyte_at_8i> exec retcurs_proc()
BEGIN retcurs_proc(); END;

*
ERROR at line 1:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'RETCURS_PROC'
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

ops$tkyte_at_8i> 
ops$tkyte_at_8i> variable x refcursor
ops$tkyte_at_8i> exec retcurs_proc(:x)

PL/SQL procedure successfully completed.

ops$tkyte_at_8i> print :x

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------

---

      7369 SMITH CLERK 7902 17-DEC-80 800 20
[snip]

      7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

>thanks,
>
>- Kev

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Feb 25 2000 - 21:04:31 CST