How to create dynamic sql by Pairing string in Oracle

Question

What i need

i need to create dynamic sql based on param procedure receives.

Type :[A,B,C]

Code : [1,2,3]

Dynamic sql

  AND ( (in_type  = 'A' and in_code = '1' )
    OR (in_type  = 'B' and in_code = '2')
    OR (in_type  = 'C' and in_code = '3')
   )

Solution i tried

splitting string from comma separated

 CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
 CREATE OR REPLACE
FUNCTION comma_to_table(p_list IN VARCHAR2)
  RETURN t_my_list
AS
  l_string VARCHAR2(32767) := p_list || ',';
  l_comma_index PLS_INTEGER;
  l_index PLS_INTEGER := 1;
  l_tab t_my_list     := t_my_list();
BEGIN
  LOOP
    l_comma_index := INSTR(l_string, ',', l_index);
    EXIT
  WHEN l_comma_index = 0;
    l_tab.EXTEND;
    l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_comma_index - l_index));
    l_index            := l_comma_index + 1;
  END LOOP;
  RETURN l_tab;
END comma_to_table;

/

     FOR x IN (select * from (table(comma_to_table(in_type)) ) )
         LOOP
           dbms_output.put_line(x.COLUMN_VALUE);
           
            FOR y IN (select * from (table(comma_to_table(in_code )) ) )
            LOOP
                dbms_output.put_line(y.COLUMN_VALUE);
                    IF x.COLUMN_VALUE = 'A' THEN 
                l_where := l_where||' AND UPPER(column_name) IN  (' || upper(in_code) || ')';
            END IF;
             END LOOP;
       END LOOP;

Elzzz · Accepted Answer · 2020-11-27 14:36:51Z

1

You can define a single collection of objects and then do additional processing:

CREATE OR REPLACE TYPE t_my_item as object(
name varchar2(1),
code number
);
/
 CREATE OR REPLACE TYPE t_my_list AS TABLE OF t_my_item;

declare
l_my_list t_my_list; 
begin
select cast(multiset(
select t1.val, t2.val from (
select regexp_substr('A,B,C','[^,]+', 1, level ) val , level lvl
from dual
connect by regexp_substr('A,B,C', '[^,]+', 1, level) is not null) t1,
(select regexp_substr('1,2,3','[^,]+', 1, level) val , level lvl
from dual
connect by regexp_substr('1,2,3', '[^,]+', 1, level) is not null) t2
where t1.lvl = t2.lvl)  as t_my_list) into l_my_list from dual;
end;
/

and here's a single select stmt:

select ' AND ( '||LISTAGG(
        stmt,
        ' or '
    ) WITHIN GROUP(
    ORDER BY lvl) || ' )'
from (    
select t1.lvl, '(in_type = ''' || t1.val||''' and in_code = ''' || t2.val || ''' )' stmt
from (
select regexp_substr('A,B,C','[^,]+', 1, level ) val , level lvl
from dual
connect by regexp_substr('A,B,C', '[^,]+', 1, level) is not null) t1,
(select regexp_substr('1,2,3','[^,]+', 1, level) val , level lvl
from dual
connect by regexp_substr('1,2,3', '[^,]+', 1, level) is not null) t2
where t1.lvl = t2.lvl);

just copy paste what's above to your method:

create or replace FUNCTION comma_to_table(p_type IN VARCHAR2, p_code IN VARCHAR2)
  RETURN t_my_list
AS
l_my_list t_my_list; 
begin
select cast(multiset(
select t1.val, t2.val from (
select regexp_substr(p_type,'[^,]+', 1, level ) val , level lvl
from dual
connect by regexp_substr(p_type, '[^,]+', 1, level) is not null) t1,
(select regexp_substr(p_code,'[^,]+', 1, level) val , level lvl
from dual
connect by regexp_substr(p_code, '[^,]+', 1, level) is not null) t2
where t1.lvl = t2.lvl)  as t_my_list) into l_my_list from dual;
return l_my_list;
end;
/


select comma_to_table(p_type => 'A,B,C', p_code => '1,2,3') from dual;

edited Nov 27, 2020 at 14:36

answered Nov 27, 2020 at 13:06

Elzzz

1315 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

afeef Over a year ago

Thanks i will try this solution

Elzzz Over a year ago

@afeef sure; you need to be careful about listagg and string length here's more info: listagg oracle19c

afeef Over a year ago

Yes 2nd solution sql give me correct sql but i need to implement this procedure where i will pass type & code,in Type :[A,B,C] Code : [1,2,3] i was trying to do for loop

afeef Over a year ago

Last issue cannot fetch object l_my_list := t_org_mem_list(comma_to_table(in_type,in_code)); FOR i IN 1..l_my_list.COUNT dbms_output.put_line('Field '||l_my_list(i).name); END LOOP;

Elzzz Over a year ago

use this l_my_list := comma_to_table(p_type => 'A,B,C', p_code => '1,2,3'); ; btw I don't know what's t_org_mem_list you did not provide this type in your question..

Collectives™ on Stack Overflow

How to create dynamic sql by Pairing string in Oracle

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related