Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: NEED FUNCTION to accept parameter, return "query-able" recordset

Re: NEED FUNCTION to accept parameter, return "query-able" recordset

From: contrapositive <contrapositive_at_hotmail.com>
Date: Thu, 2 May 2002 16:50:48 -0400
Message-ID: <3cd1aaa4$1_1@nopics.sjc>


Good stuff... Thanks for the help!

"billia" <member_at_dbforums.com> wrote in message news:3cd0308d$1_at_usenetgateway.com...
> 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 Thu May 02 2002 - 15:50:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US