Re: EXEC syntax for SP which returns row set
- From: fitzjarrell@xxxxxxx
- Date: 14 Jul 2005 20:24:26 -0700
Comments embedded.
Mark A wrote:
> <fitzjarrell@xxxxxxx> wrote in message
> news:1121350101.091184.122240@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> >
> > Comments embedded.
> > m0002a@xxxxxxxxx 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
.
- Follow-Ups:
- Re: EXEC syntax for SP which returns row set
- From: Mark A
- Re: EXEC syntax for SP which returns row set
- References:
- Re: EXEC syntax for SP which returns row set
- From: fitzjarrell
- Re: EXEC syntax for SP which returns row set
- From: Mark A
- Re: EXEC syntax for SP which returns row set
- Prev by Date: Re: EXEC syntax for SP which returns row set
- Next by Date: Re: EXEC syntax for SP which returns row set
- Previous by thread: Re: EXEC syntax for SP which returns row set
- Next by thread: Re: EXEC syntax for SP which returns row set
- Index(es):
Relevant Pages
|
