Re: Howto generate records in stored procedure

John Russell (netnews4_at_johnrussell.mailshell.com) wrote:
: 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/

Thanks for pointing this out.

I'm copying that url and your note to my "useful notes" folder, and will be reading this later for sure. It is not something I have commonly wished to do, but when you want it you sure miss not having it! Received on Thu Sep 26 2002 - 14:13:54 CDT