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:-

1. we create an object type that is based on the columns you would expect to get back from your function
2. we create a collection (nested table) type of this object type
3. we create a function to load a collection with the data we require and pass this back out to SQL. I've used a query against user_objects to determine the ids of all objects created between a min and max date passed in as parameters.
4. we can query this collection as if it were a table (using TABLE and CAST). I've given you SQL and PL/SQL examples. You can compile your code with this syntax in as well, as long as the collection type exists on the database.

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;

• Return the collection to the database - it will recognise this datatype... RETURN t_myTableOfRecords; END; / sho err
  • Use the collection in SQL using the TABLE and CAST expressions.
  • Note the alias and the fact that we can reference attribute names... SELECT t.id , o.object_type FROM TABLE(CAST(myFunction(SYSDATE-100, SYSDATE) AS myCollectionType)) t , user_objects o WHERE o.object_id = t.id;
  • Use the collection in PL/SQL also... BEGIN FOR myRec IN (SELECT o.object_type FROM TABLE(CAST(myFunction(SYSDATE-100, SYSDATE) AS myCollectionType)) t , user_objects o WHERE o.object_id = t.id) LOOP DBMS_OUTPUT.PUT_LINE(myRec.object_type); END LOOP; END; /

Regards

Adrian

--

Posted via dBforums
http://dbforums.com Received on Wed May 01 2002 - 13:14:37 CDT