How to put string of values with IN clause using dynamic PL/SQL?

Question

Using Oracle PL/SQL, how can I populate the bind variable :b3 with more than one value for the IN clause? (This code is for demo purposes only -- it may not compile but it does clarify the question if any is needed)

 declare
      type work_rec is record ( 
        work_status varchar2(50),
        work_cd     varchar2(50));

  type work_tab is table of work_rec index by pls_integer;   

  t_work_tab work_tab; 
  sql_stmt varchar2(400); 

begin
  select case    
     when status_desc like '%Employed%' then 'Employed'
     else 'Unknown'
   end as work_status
  ,case 
     when status_cd between '1' and '9' then '1,2,3,4' 
     else '0'
   end as work_cd
  bulk collect into t_work_tab
  from employee_table;

  for i in t_work_tab.first..t_work_tab.last 
  loop
    sql_stmt := 'insert into employee_hist 
                 select name,
                        employer
                 from tax_table
                 where employment_cd in (:b3)';    --< how to populate this with '1','2','3','4'

    execute immediate sql_stmt using t_work_tab(i).work_cd;
    commit;

  end loop;

end;
/

This question has been answered many, many times - tkyte.blogspot.com/2006/06/varying-in-lists.html — OldProgrammer
– OldProgrammer, Commented Feb 12, 2014 at 21:12
Thank you for the link but it's not quite what I was looking for. :-) — tale852150
– tale852150, Commented Feb 14, 2014 at 19:47

Jag · Accepted Answer · 2014-02-12 21:30:37Z

1

When you loop through the values, keep appending the string with ' and , as required to make up the part of in. Then you can use that string as part of your sql statement.

Example

temp = "'"
Loop through values a,b,c,d as str
    temp = temp + str + "'",
End Loop
temp = substr(temp,0,length(temp))  // this is to trim the last , character

Hope it helps!

answered Feb 12, 2014 at 21:30

Jag

2911 silver badge4 bronze badges

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

Comments

San · Accepted Answer · 2014-02-13 05:08:47Z

You need to prepare the in list inside the loop using another variable and then the statement outside it. Something like this may help:

 declare
      type work_rec is record ( 
        work_status varchar2(50),
        work_cd     varchar2(50));

  type work_tab is table of work_rec index by pls_integer;   

  t_work_tab work_tab; 
  sql_stmt VARCHAR2(400); 
  v_in_str varchar2(100);_

begin
  select case    
     when status_desc like '%Employed%' then 'Employed'
     else 'Unknown'
   end as work_status
  ,case 
     when status_cd between '1' and '9' then '1,2,3,4' 
     else '0'
   end as work_cd
  bulk collect into t_work_tab
  from employee_table;

  for i in t_work_tab.first..t_work_tab.last 
  loop
    v_in_str := v_in_str || t_work_tab(i).work_cd || ',';

  END loop;
  v_in_str :=rtrim(v_in_str, ',');
  sql_stmt := 'insert into employee_hist 
               select name,
                      employer
               from tax_table
               where employment_cd in ('||v_in_str||')';

  execute immediate sql_stmt;
  commit;

end;

HP Frei · Accepted Answer · 2014-02-13 07:42:03Z

you could use a plsql collection as bind variable, this seems the nicer solution to me:

    declare
        type t_nbr_tbl is table of number;

        type work_rec is record ( 
          work_status varchar2(50),
          work_cd     t_nbr_tbl;

        type work_tab is table of work_rec index by pls_integer;   

        t_work_tab work_tab; 
        sql_stmt varchar2(400); 

    begin
        select case    
           when status_desc like '%Employed%' then 'Employed'
           else 'Unknown'
         end as work_status
        ,case 
           when status_cd between '1' and '9' then t_nbr_tbl(1,2,3,4)
           else t_nbr_tbl(0)
         end as work_cd
        bulk collect into t_work_tab
        from employee_table;

        for i in t_work_tab.first..t_work_tab.last 
        loop
          sql_stmt := 'insert into employee_hist 
                       select name,
                              employer
                       from tax_table
                       where employment_cd in (select column_value from table(:b3))';    --< how to populate this with '1','2','3','4'

          execute immediate sql_stmt using t_work_tab(i).work_cd;
          commit;

        end loop;

    end;
    /

tale852150 · Accepted Answer · 2014-02-12 23:17:07Z

0

Better solution (at least to my issue) - don't use a bind variable but instead concatenate the string of values (code segment shown only):

for i in t_work_tab.first..t_work_tab.last 
  loop
    sql_stmt := 'insert into employee_hist 
             select name,
                    employer
             from tax_table
             where employment_cd in (' || t_work_tab(i).work_cd || ')';

   execute immediate sql_stmt;
...

You get the idea. Thanks for all your input.

answered Feb 12, 2014 at 23:17

tale852150

1,6303 gold badges18 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to put string of values with IN clause using dynamic PL/SQL?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related