Re: Procedure to return a result set

hello,
  You can't return a result set directly from a procedure or function!

Suggestions:

1. Use a temporary table (which is permanent and can hold result sets from different invocations of your procedure - remember to cleanup the table after use!).
2. If using version 7.3 or greater then it is possible to return a cursor variable. This i haven't tried as i use version 7.2 or less of Oracle.
3. Version (8) may allow it, in which case you will have to wait a bit.
4. It may be possible to use PL/SQL tables as long as only PL/SQL routines are involved.

i would be very tempted to use (1) in any case currently.

Yes, i know, it is a step backward from your point of view! There are many advantages to using Oracle. Alas, this is not one of them.

graham

"Bob Weber" <bweber_at_onramp.net> wrote...

| Ok! I give up!!
| It's very simple under MS SQL to write a stored procedure which returns a
| result set. Unfortunately I can't seem to figure out how to do the same
| thing under ORACLE!!!
|
| Could anyone help? let's say I want a procedure to
| SELECT PRICE FROM PRICE_TABLE WHERE VENDOR = :VEND
| how can i create a procedure to do this and how can i successfully execute
| ie. EXECUTE SP_PRICE( "ORACLE");
| and then get back
| PRICE
| ----------
| 1.00
| 2.00
| 3.45
| etc...
|
| Sorry to seem so dense, but I seem to be missing something very basic!
| Thanks,
| Bob Weber
| bweber_at_onramp.net

Graham Miller ...
Opinions expressed are mine, they are free, and worth exactly what they cost. Received on Sat May 10 1997 - 00:00:00 CDT