Re: EXEC syntax for SP which returns row set

<fitzjarrell_at_cox.net> wrote in message
news:1121350101.091184.122240_at_g44g2000cwa.googlegroups.com...
>
> 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:
>

I admit that I am new to Oracle stored procedures, but that certainly is not the case in other databases I have worked with htat OUT paramters are supplied when calling a stored procedure. Further, it is not exactly intututive that one must supply an out paramter when calling any type of program. It is intuative that one must supply an IN parmater.

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

I don't know why you are supplying the above example. I supplied the answer before anyone else and I posted the website were I found the answer. You are just copying what I posted

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

I hear you, but I don't believe you. Certainly not everyone who trashed me on this thread knew how to call a SP/function form SQL*Plus using the 3 lines above (I was the first to post the answer on this thread when I found the answer the answer on a website and not in any Oracle manual). If it is explicitly stated in an Oracle manual, I would like to see that (and not talking about "implied" answers to the question). Received on Thu Jul 14 2005 - 18:43:28 CDT