Re: EXEC syntax for SP which returns row set

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

To be honest, I am not sure what you are asking me, but I will try to answer. If I don't succeed to your satisfaction, try again.

When you call a stored procedure in some other databases (DB2, SQL Server, and some others) that return result sets, and one is using a command line type interface (not a java program), you don't need to specify the OUT parameters or the result set. You do have to specify the IN parms.

If the stored procedure has an output result set (in these other databases) then it just displays the results in the output as soon as you call the SP, in the same manner SQL*Plus does when the "print results" command does (using my syntax). Obviously, this is primarily for testing purposes using a command line type processor, and a java program would have specify some way to handle the result one row at a time in the program.

If you want an example, I will code one up and post it. Received on Thu Jul 14 2005 - 23:16:27 CDT