Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

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.
Prepared Statement Error: Table './oraclebasecms/cms_page_comment_uuids' is marked as crashed and should be repaired