CODEWITHSUNDEEP
sqloracle-databaseplsql
tale852150
tale852150

Reputation: 1628

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

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;
/

Upvotes: 0

Views: 2527

Answers (4)

HP Frei
HP Frei

Reputation: 126

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;
    /

Upvotes: 1

San
San

Reputation: 4538

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;

Upvotes: 1

tale852150
tale852150

Reputation: 1628

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.

Upvotes: 0

Jag
Jag

Reputation: 291

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!

Upvotes: 1

Related Questions