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!

select from a function that returns a collection (nested table type)

user3292382Oct 6 2020 — edited Oct 6 2020

Hello,
Oracle 12c.
I have to select from a collection of values returned from a function:

select * from mytable
where myfield member of x_test.getList;

The problem is that I get the ORA-00902: invalid datatype error.
Following is what I did:

CREATE OR REPLACE PACKAGE x_test AS
    type myList IS TABLE OF VARCHAR2(6);
END x_test;


CREATE OR REPLACE PACKAGE BODY x_test as

function getList return myList is
  v1 myList;
begin
 SELECT Nickname bulk collect INTO v1
 FROM SoftwareTable
 where DateLic > trunc(sysdate)-90;
 /** this is just an example. Actually the query is more complex, thus I dont want to 
     directly incapsulate it into the IN clause of the calling select statement **/

return v1;
end getList;

END x_test;

How can I select from mytable by filtering only whose records where myfield is in the list of values returned by the myList function?
Thank you!!

This post has been answered by Billy Verreynne on Oct 6 2020
Jump to Answer
Comments
Post Details
Added on Oct 6 2020
7 comments
547 views