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.

DO NOT ask technical questions here, that's what my forum is for!

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!

Add your comments here.
Name
Comment
(max 400 chars - plain text)