PLSQL – Use variable array on where clause IN

I am looking to use an array of values in the WHERE IN condition.

After research, I saw that I had to make a “CREATE TYPE”, since a “TYPE” in local does not work. This solution does not suit me, I do not want to make a new TYPE in Oracle, since the database is used for an ERP/PGI I do not wish to pollute with my little need.

My request is the following:

DELETE vip_routage
 WHERE vip_tel_ext = w_array_tel(i)
   AND ((w_cp NOT IN ('14', '27', '50', '61', '76') 
   AND SUBSTR(VIP_CODE_POSTAL, 1, 2) IN ('14', '27', '50', '61', '76')) 
    OR (w_cp IN ('14', '27', '50', '61', '76') 
   AND SUBSTR(VIP_CODE_POSTAL, 1, 2) IN ('14', '27', '50', '61', '76') 
   AND TO_NUMBER(vip_dest1) < w_tri_ordre) 
    OR (w_cp NOT IN ('14', '27', '50', '61', '76') 
   AND SUBSTR(VIP_CODE_POSTAL, 1, 2) NOT IN ('14', '27', '50', '61', '76') 
   AND TO_NUMBER(vip_dest1) < w_tri_ordre));

It is the value “(’14’,’27’,’50’,’61’,’76’)” that I would like to set as a variable, but only in my program.

Do you have any ideas other than “CREATE TYPE”?

Answer

As you don’t want to create your own type, use built-in one – sys.odcivarchar2list. For example, fetch employees who are either clerks or managers:

SQL> select deptno, empno, ename, job, sal
  2  from emp
  3  where job in (select *
  4                from table(sys.odcivarchar2list('CLERK', 'MANAGER'))
  5               );

    DEPTNO      EMPNO ENAME      JOB              SAL
---------- ---------- ---------- --------- ----------
        10       7934 MILLER     CLERK           1300
        30       7900 JAMES      CLERK            950
        20       7876 ADAMS      CLERK           1100
        20       7369 SMITH      CLERK            800
        10       7782 CLARK      MANAGER         2450
        30       7698 BLAKE      MANAGER         2850
        20       7566 JONES      MANAGER         2975

7 rows selected.

SQL>

---

If you want to declare a variable whose datatype is sys.odcivarchar2list (so, you’re in a PL/SQL procedure), then

SQL> declare
  2    l_job sys.odcivarchar2list := sys.odcivarchar2list('CLERK', 'MANAGER');
  3    l_cnt number;
  4  begin
  5    select count(*)
  6      into l_cnt
  7      from emp
  8      where job in (select * from table(l_job));
  9
 10    dbms_output.put_line('Found ' || l_cnt || ' employees');
 11  end;
 12  /
Found 7 employees

PL/SQL procedure successfully completed.

SQL>