database - It's possible to use a collection in WHERE clausule and condition IN?

Question

It's possible to do something like the following?

create or replace TYPE "TypeKeys" IS TABLE OF INTEGER;
/
Declare
    Keys TypeKeys;
    personT PERSON%ROWTYPE;
BEGIN
    Keys := TypeKeys();
    Keys.EXTEND(2);
    Keys(1) := 14;
    Keys(2) := 21;

    SELECT * INTO personT FROM PERSON WHERE ID IN (Keys);
END;
/

I need to find rows by multiple id's

question from:https://stackoverflow.com/questions/65602953/its-possible-to-use-a-collection-in-where-clausule-and-condition-in

Answer 1

You could use member of:

SELECT * INTO personT FROM PERSON WHERE ID MEMBER OF Keys;

or a table collection expression:

 SELECT * INTO personT FROM PERSON WHERE ID IN (SELECT * FROM TABLE(Keys));

db<>fiddle

Of course, as you're selecting into a single record you'll get an error if there isn't exactly one matching row, however many key values you include in the collection.