DECLARE
l_filter varchar2(100);
BEGIN
SELECT filter INTO l_filter
FROM dashboard
WHERE id=TARGET_ID_1;
I get l_filter as ('016','018','011','014') from this. Now i want to use this l_filter values within IN clause in the query below dynamically.
select a,b from grid
where filter_value in l_filter;
How can I do this in PL/SQL?
You could use regular expressions:
select g.a, g.b
from grid g
where exists (select 1
from dashboard d.
where d.id = TARGET_ID_1 and
regexp_like(d.filter_value, '^(' || replace(d.filter, '''', ''), ',', '|') || ')$')
);
This is not particularly efficient, but it does not require any PL/SQL or dynamic SQL.
That said, you might want to store your "filters" as rows in a table so you can just join things together, dispensing with the complication.
Use LIKE:
DECLARE
l_filter varchar2(100);
cur SYS_REFCURSOR;
a GRID.A%TYPE;
b GRID.B%TYPE;
BEGIN
SELECT filter
INTO l_filter
FROM dashboard
WHERE id = TARGET_ID_1;
OPEN cur FOR
SELECT a,b
FROM grid
WHERE ','||SUBSTR(l_filter,2,LENGTH(l_filter)-2)||',' LIKE '%,'''||filter_value||''',%';
LOOP
FETCH cur INTO a,b;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(a || ', ' || b);
END LOOP;
CLOSE cur;
END;
/
db<>fiddle here
Here's how I understood the question.
Sample data first:
SQL> select * from dashboard;
ID FILTER
---------- -----------------------
1 '016','018','011','014'
2 '111', '222'
SQL> select * from grid;
A B FIL
---------- ---------- ---
100 200 016
101 201 011
200 400 xxx
SQL>
Function that returns refcursor; the key (in this example) is to split filter values into rows so that you could use them as a subquery (lines #15 - 18):
SQL> create or replace function f_get (target_id_1 in dashboard.id%type)
2 return sys_refcursor
3 is
4 l_filter varchar2(100);
5 rc sys_refcursor;
6 begin
7 select filter
8 into l_filter
9 from dashboard
10 where id = target_id_1;
11
12 open rc for
13 select g.a, g.b
14 from grid g
15 where g.filter_value in (select regexp_substr(replace(l_filter, chr(39), ''), '[^,]+', 1, level)
16 from dual
17 connect by level <= regexp_count(l_filter, ',') + 1
18 );
19
20 return rc;
21 end;
22 /
Function created.
Testing:
SQL> select f_get(1) from dual;
F_GET(1)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
A B
---------- ----------
100 200
101 201
SQL>
