Re: EXEC syntax for SP which returns row set

Comments embedded.
Mark A wrote:
> <fitzjarrell_at_cox.net> wrote in message
> news:1121397866.386844.278650_at_g43g2000cwa.googlegroups.com...
> >
> > 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.
> >
> Are you serious? It looks pretty close to me. But if you don't think so,
> let's just drop it.
>
> > 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.
> > <snip>
>
> Albeit? I specifically said in my OP that I need to know how to do it with
> SQL*Plus.
>

Your 'snipping' certainly took my message and distorted it by removing the remainder of the thought:

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

And you, of course, failed to provide an answer to my question. You have yet another opportunity.

> I was testing the SP in SQL*Plus not PL/SQL. The SP will be called by a
> java program (the java programmer knows how to call it with a result set)
> but I just needed to run a quick test in SQL*Plus.
>
> I asked 3 experienced Oracle DBA's about this, all of whom said they knew
> PL/SQL, but none knew the answer. None referred me to the SQL*Plus manual,
> but recommended some PL/SQL manuals and books they gave me to look at.
>
> The link I found and posted in this thread previously (repeated below) led
> me to the correct answer. However I do thank Maxim for his response and his
> high level of courtesy and professionalism.
>
> Here is where I got the answer that I posted previously, and it works
> perfectly:
>
> Returning a JDBC result set from an Oracle stored procedure, by Bruce P.
> Blackshaw
> http://www.enterprisedt.com/publications/oracle/result_set.html

And, yet, it doesn't answer the question of how OUT parameters are utilized if they are not passed to the program unit. I'm certain we'd all like to know your answer.

Unless you don't have one.

David Fitzjarrell Received on Thu Jul 14 2005 - 23:04:44 CDT