Use MEMBER OF instead of IN

Oracle Database 10g offers a new statement in SQL: the MEMBER OF clause. This offers a very nice alternative to IN, geared specifically to use with collections. The result is a very simple and elegant implementation:
FUNCTION in_clause.member_of_list (list_in IN pky_nt)
RETURN sys_refcursor
IS
retval sys_refcursor;
BEGIN
OPEN retval FOR
SELECT *
FROM otn_question
WHERE ID MEMBER OF list_in;

RETURN retval;
END member_of_list;

Notes on this implementation:
• It is not available in Oracle 9i Database Release 2.
• Clearly, this would be preferable to writing the extra code required with native dynamic SQL or the TABLE operator approach.
• Make sure that you test this approach carefully in your database. There is currently at least one documented bug with MEMBER OF, which results in suboptimal performance, as you could see in the table I showed you earlier.
• Regardless of the number of items in the collection, the SQL statement executed does not change, so you will not have to worry about the possible overhead of repeated hard parses, as you do with both the NDS (above) and DBMS_SQL (below) implementations.