ORA-01795 - maximum number of expressions in a list is 1000 error

Recently, while loading a lot of data into DWH, I've faced with the ORA-01795 error. Some of my colleagues wrote the following code:
  v_user_name clob;
    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 || ')';
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.

Комментариев нет: