Re: Howto generate records in stored procedure

On 24 Sep 2002 12:13:11 -0800, yf110_at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
>I think he means he wants to write SQL statements like the following
>
> SELECT * FROM THIS_IS_A_PROCEDURE_NOT_A_TABLE ;
>
>I don't think Oracle can do this.
>
>The code for the procedure looks sort of like this
>
> PROCEDURE THIS_IS_A_PROCEDURE_NOT_A_TABLE
>
> while I-havent-finished
>
> build-one-row
> pass-it-back-to-the-caller
> yield
>
> end loop
>
> END PROCEDURE
>
>You can do this in various other databases, but I have not seen a
>technique to allow this in oracle. Note that contrary to what some oracle
>programmers seem to thing when they see this, this is nothing like
>`select my-function() from what-ever' because is this latter case you have
>no way to make the select loop over a number of rows controlled by the
>function.
>

You can do it in Oracle9i using what are known as table functions. The function issues PIPE ROW to send individual rows back. It can be batched and parallelized in various ways. See the 9i PL/SQL Guide, Chapter 8:

http://tahiti.oracle.com/pls/db92/db92.docindex?remark=homepage#index-PL/

John Received on Thu Sep 26 2002 - 00:56:53 CDT