Re: EXEC syntax for SP which returns row set

<fitzjarrell_at_cox.net> wrote in message
news:1121481703.239612.248740_at_o13g2000cwo.googlegroups.com...
>
>
> It's apparent you missed the point entirely. For Oracle you coded
> this:
>
> CREATE or replace PROCEDURE TEST.SPTEST
> (C1 OUT ReturnCur.REF_CUR)
> AS
> BEGIN
> open C1 for SELECT ID from TEST.EMPLOYEE;
> END;
>
> Yes, the OUT parameter in this particular case is a reference cursor,
> but it need not be. And, yes, it IS an OUT parameter as specified in
> the code creating the procedure. As such, the procedure will error
> when the proper arguments are not supplied, as you well know.
>
> Possibly I misinterpreted this statement from you:
>
> "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."
>
> They are if the procedure is coded to use them. It appears to me
> you're stating you can code a stored procedure with an OUT parameter
> and then call that same procedure absent any variable to receive the
> output without error. As such I am asking you to back up your
> statement with proof. Should I have misinterpreted your statement
> forgive me, for your language was vague.
>
> David Fitzjarrell

I said that I can call a SP using a different DBMS that has a return cursor (with out without any other IN, or OUT, or IN OUT parameters), and when calling such a stored procedure from a command processor tool like SQL*Plus, then the return cursor does not need to be accounted for in the call syntax. Obviously, any other IN, or OUT, or IN OUT parms do need to be accounted for in the call syntax.

The fact that Oracle is different in this regard is of no particular concern to me, once I know what the syntax is that is needed to call a SP from SQL*Plus. The purpose of my OP was to solve a problem, not to compare DBMS products.

The SP I posted in this thread is not the real SP I needed to test. I made up a simple example to post on this forum. The real SP that I needed to test from SQL*Plus is fairly complex and part of an application that processes thousands of transactions per second. Received on Fri Jul 15 2005 - 22:49:10 CDT