Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Use select for input to function with nested table parameter

doug8294Apr 16 2013 — edited Apr 18 2013
I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

I have a function that has a nested table parameter, however, I would like to be able to use a select statement rather than a comma separated list of values. An example:


Types:
CREATE OR REPLACE TYPE MATCH_ID_LIST AS TABLE OF NUMBER;

CREATE OR REPLACE TYPE match_type AS OBJECT (        
      first_name VARCHAR2(100),
      last_name VARCHAR2(100),
);

CREATE OR REPLACE TYPE MATCH_TABLE AS TABLE OF MATCH_TYPE;
Package spec:
create or replace
PACKAGE MATCH_PKG AS 

        FUNCTION GET_MATCHES(i_match_ids match_id_list) RETURN match_table PIPELINED;

END MATCH_PKG;
I know I can do something like this:

select * from table(match_pkg.GET_MATCHES(match_id_list(5, 6, 10, 12)));

However, these match_ids are located in a table:

select match_id from matches where acct_id = 10;

Is there any way I can use the 2nd SQL statement in the first SQL statement instead of using the comma separated values?

-d
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 16 2013
Added on Apr 16 2013
16 comments
1,498 views