Re: EXEC syntax for SP which returns row set

Comments embedded.
Mark A wrote:
> <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
>

This is what YOU posted after a re-write changed the procedure to a function:

var results refcursor
exec :results := TEST.SPTEST
print results

Show me where THAT matches what I posted above, using a stored procedure.

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

You may not believe any of us, since you're so lost in your world of DB2, however all who posted to your initial query attempted to guide you to the proper answer, even Oracle itself. And, no, you weren't the first to post the answer; Maxim Demenko was the first to flesh out the responses and lead you by the nose to the correct solution, albeit with PL/SQL instead of SQL*Plus. It was his example I used, using only SQL*Plus, to illustrate HOW you'd call a stored procedure and return data using an OUT parameter. By definition pararameters are variables or values passed to a program unit. Therefore an OUT parameter MUST be passed to a procedure coded to accept one. Yet you stated:

 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.

If they are not supplied how does one populate them with data for use elsewhere? Telepathy? Such must be new technology known to none but you.

You may cease your lame excuses and false claims. They don't hold up well under scrutiny.

David Fitzjarrell Received on Thu Jul 14 2005 - 22:24:26 CDT