Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: NEED FUNCTION to accept parameter, return "query-able" recordset
JK
This is possible in 8i using collections. In the example I have given you:-
Hope this helps...
--Create an object-type that defines the data you wish to build inside
the function... CREATE OR REPLACE TYPE myObjectType AS OBJECT ( id
NUMBER , value VARCHAR2(128) ); / sho err
--Create a collection based on this object type... CREATE OR REPLACE
TYPE myCollectionType AS TABLE OF myObjectType; / sho err
--A representation of the function you will require. It loads up a
PL/SQL nested table of records --that will then be available for you to
query on the database, in your package etc... CREATE OR REPLACE FUNCTION
myFunction ( min_date_in IN DATE, max_date_in IN DATE ) RETURN
myCollectionType IS t_myTableOfRecords myCollectionType :=
myCollectionType(); BEGIN -- Load up the table of records based on your
input SELECT myObjectType(object_id,object_name) BULK COLLECT INTO
t_myTableOfRecords FROM user_objects WHERE created BETWEEN min_date_in
AND max_date_in;
Regards
Adrian
--
Latest Oracle DBA forums at:
http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi
Posted via dBforums
http://dbforums.com
Received on Wed May 01 2002 - 13:14:37 CDT