8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for Dynamic IN-Lists


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.

Durga said...

Thank you for the solutions given here.

In the Temporary Table Sub-Select example, the data or the IN-list data has VARCHAR datatype.
It will help us if you demonstrate the same example when the IN-list has Numeric data.
Can you please post an example wherein the IN-list has numeric datatypes?
I had tried to write a code on similar lines but i got ORA-03001 ora-03001 unimplemented feature

Tim... said...

Hi.

As requested, each section has a number in-list example at the end. :)

Cheers

Tim...

Markus said...

You can also use CONNECT BY with REGEX:

SELECT *
FROM emp
WHERE empno IN (SELECT REGEXP_SUBSTR('7782 7566 7369 7521', '\d+', 1, level) FROM DUAL
CONNECT BY REGEXP_SUBSTR('7782 7566 7369 7521', '\d+', 1, level) IS NOT NULL
)
ORDER BY ename

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!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.