8i | 9i | 10g | 11g | 12c | Misc | PL/SQL | SQL | RAC | Linux

Home » Misc » Here

Comments for Dynamic IN-Lists

Dynamic IN-Lists - This article presents a number of methods for parameterizing the IN-list of a query.



Nicole said...

I used something like this and it worked fine for me:

CREATE OR REPLACE TYPE in_list_t AS TABLE OF VARCHAR2 (4000);
/

CREATE OR REPLACE TYPE in_list_v AS VARRAY(10) OF VARCHAR2 (4000);
/

SELECT *
FROM emp
WHERE job IN (SELECT pl.column_value FROM TABLE(CAST(in_list_v('SALESMAN, MANAGER') AS in_list_t)) pl)
ORDER BY ename;

DROP TYPE in_list_t;

DROP TYPE in_list_v;

Mark said...

I think it might be worth mentioning that use of the "MEMBER OF" construct is not 100% equivalent to the "sub-SELECT from TABLE" construct in the sense that the former will cause the table function to be called for EACH row/value that is tested! (Tested on 10gR2 by including some dbms_output to the function to show each invocation.)

Regards markdv77 at gmail.

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!