Re: EXEC syntax for SP which returns row set

Maxim Demenko wrote:
> m0002a_at_yahoo.com wrote:
>

[snip]

>
> scott_at_ORA92> create or replace package returncur is
> 2 type ref_cur is ref cursor;
> 3 end returncur;
> 4 /
>
> Package created.
>
> scott_at_ORA92> create or replace procedure sptest(c1 out returncur.ref_cur)
> 2 as
> 3 begin
> 4 open c1 for select id from employee;
> 5 end;
> 6 /
>
> Procedure created.
>
> scott_at_ORA92> declare
> 2 t returncur.ref_cur;
> 3 l_id employee.id%type;
> 4 begin
> 5 sptest(t);
> 6 loop
> 7 fetch t into l_id;
> 8 dbms_output.put_line(l_id);
> 9 exit when t%notfound;
> 10 end loop;
> 11 end;
> 12 /
> 7369
> 7499
> 7521
> 7566
> 7654
> 7698
> 7782
> 7788
> 7839
> 7844
> 7876
> 7900
> 7902
> 7934
> 7934
>

Nice example, however since the OP asked about SQL*Plus the short answer is

SQL> variable test refcursor
SQL> exec sptest(:test)

PL/SQL procedure successfully completed.

SQL> print test

To the OP:

All the answers *are* in the manuals, you just have to get used to use them. The reactions in this thread (and some others, too) were quite harsh because we see more and more lazy (or ignorant) people around here. Laziness in the database industrie leads to lost data or abysmal performance (often both), and people here try to protect them and even you from this. The method sometimes is to yell at people asking, just as a loving father will sometimes give up answering the 450th 'Why' question in a row from his child and yell. It's meant to help you think for yourself, or research because most answers are already somewhere on the net.

Now, I don't attribute laziness or ignorance to you, at least you showed some effort, you just didn't think properly. So please take the reactions in this group as a stimulation for a more careful reading of error messages in the future.

Oracles error messages are often very verbose and not too hard to understand, and then there is always http://tahitit.oracle.com with the fine manuals *and* a searchable list of the error messages including explanation and solutions.

Cheers,
Holger Received on Thu Jul 14 2005 - 02:58:21 CDT