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