How to built a dynamic string PLSQL

Question

I have 2 associative_arrays of number and I am trying to built a dynamic string for a dynamic query.

This is my for statement:

    for indx_opt in 1..IDOPTarray.count loop

         IF indx_opt=1 AND IDFGCFCParray.count=1 THEN
         sql_stmt_2:=sql_stmt_2||' and wopt.id_ft_opt = ';
         sql_stmt_2:=sql_stmt_2|| (IDOPTarray(indx_opt));
         end if;

         if indx_opt=1  AND  IDFGCFCParray.count>1 then
         sql_stmt_2:=sql_stmt_2||' and wopt.id_ft_opt in(';
         sql_stmt_2:=sql_stmt_2||(IDOPTarray(indx_opt));

         elsif indx_opt>=1 AND IDFGCFCParray.count>=0 then
         sql_stmt_2:=sql_stmt_2||','||(IDOPTarray(indx_opt))||')';

With 2 number in IDOPTarray I get a correct result:

    and wopt.id_ft_opt in(27,28)

Instead with more then 2 number in IDOPTarray I get this result:

    ,17228),17229),17230)

What I want to get is this:

     where w.id = 303 and wopt.id_ft_opt in (17228,17229,17230)

if I have 5 numbers, I want to get this for the 'where' clause:

     where w.id = 321 and wopt.id_ft_opt in (17228,17229,17230,17231,17232)

I want a dynamic output of my string.

IDFGCFCParray is the 2nd array, but is not important right now in order to get the output I want.

Does somebody can help me? thank you.

Arkadiusz Łukasiewicz · Accepted Answer · 2017-01-20 09:33:31Z

1

You have to close bracket only if indx_opt is equil to IDOPTarray.count. And simple example.

declare 
 type  list_number is table of number;
  xx list_number := new list_number(1,2,3,5,7,8);
  str varchar2(4000);
begin 

 for i in xx.first .. xx.last loop
  if i = 1 then 
    str := ' condition in ('||xx(i);
  else 
    str := str||','||xx(i);
  end if;

   if i = xx.last then 
   str := str||')';
   end if; 
 end loop;
dbms_output.put_line(str);
end;

If you colleciton is sql levle type you can do this in this way

declare 
  xx list_number := new list_number(1,2,3,5,7,8);
  str varchar2(4000);
begin 
 SELECT  'condition in ('||LISTAGG(column_value, ',') WITHIN GROUP (ORDER BY column_value)||')' into str from table(xx);
 dbms_output.put_line(str); 
end;

answered Jan 20, 2017 at 9:33

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

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

1 Comment

Emanuele Antico Over a year ago

Thank you, I will try this solution. For the moment I think I just solve the issue by modifying the if condition

Collectives™ on Stack Overflow

How to built a dynamic string PLSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related