declare v_user_name clob; begin ... select wm_concat('''' ||username) || ''''), into v_user_name from customers; ... execute immediate 'insert into temp_customer (user_name) select username from tab_1 where username in (' || v_user_name || ')'; ... end;As you can see, v_user_name variable is populated by the first select statement. It contains number of customers divided by comma (why has my colleague decided to use undocumented "wm_concat" function - its another question, I hope there was a reason for that :) ) and after that, we use v_user_name variable into dynamic sql. This code had been working fine for years, but recently we had to load a lot of data (a lot of customers) into DWH and we've faced with the ORA-01795. I found a topic in OTN forum about this issue.
First of all, I decided to use select * from table (sys.odcivarchar2list ... ) construction (I am a very lazy person :), and didn't want to rewrite this code), but when I tried to use the above construction I received another error - ORA-00939: too many arguments for function. This approach didn't work. After that, I decided to divide the code into small parts - I had to rewrite the package a bit and it works fine. This solution works fine.
Of course, there is one more approach - create a temp table and just join it to the query. I think it is the best option.