Re: EXEC syntax for SP which returns row set

Comments embedded.
m0002a_at_yahoo.com wrote:
> First, I did RTFM. More specifically the 10g manual which does not say
> anything about how to test it from SQL*Plus. I have also asked about 10
> Oracle experts, none of whom seem to know.
>

You still didn't read the error message or undestand its meaning. ANY parameter coded in a procedure MUST be supplied. Period. You coded an OUT parameter and you need to SUPPLY that for the procedure to work. As you've been told before:

SQL> create table employee as select empno id from emp;

Table created.

SQL> create or replace package returncur is   2 type ref_cur is ref cursor;
  3 end returncur;
  4 /

Package created.

SQL> create or replace procedure sptest1(c1 out returncur.ref_cur)   2 as
  3 begin
  4 open c1 for select id from employee;   5 end;
  6 /

Procedure created.

SQL> variable mytest refcursor
SQL> exec sptest1(:mytest);

PL/SQL procedure successfully completed.

SQL> print mytest

        ID

      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

        ID
----------
      7900
      7902
      7934
      7999

15 rows selected.

SQL>
> Second, it you look closely, I tried 3 differernt commands. The first
> one was "exec sec.test2" (no input parms) and since the SP has no IN
> variables, I thought that would work, but it did not and I was
> experimenting with other options (see my orginal post).

None of which supplied a PARAMETER to the procedure.

>
> I did find a solution that seems to work if I create a function
> (instead of SP) according to this link, which also shows how to execute
> the function from SQL*Plus (it worked for me):
> http://www.enterprisedt.com/publications/oracle/result_set.html
>

Certainly if you convert it to a function you eliminate having to learn how to pass parameters to a procedure. Which, of course, doesn't do you much good when you need a stored procedure to return values.

> Never seen so many people answer questions who don't know the answer.

We know the answer, and it was provided in numerous ways, most as indirect hints. We were ATTEMPTING to get YOU to THINK about the errors your failed procedure calls created. Even Oracle told you WHAT was wrong, long before we tried. It's truly a shame you can't take suggestions and deduce the solution.

Do not blame us for your inabilities.

David Fitzjarrell Received on Thu Jul 14 2005 - 09:08:21 CDT