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