PLSQL - Use variable array on where clause IN

Question

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"?

What is w_array_tel(i)? That looks like w_array_tel is a local variable that is a collection, i is the index of the collection, which would imply that you're already in a loop in PL/SQL. Is that the case? What version of Oracle are you using? — Justin Cave
– Justin Cave, Commented Feb 21, 2022 at 0:52

Littlefoot · Accepted Answer · 2022-02-21 13:36:17Z

4

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>

edited Feb 21, 2022 at 13:36

answered Feb 20, 2022 at 21:05

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

4 Comments

Merurz Over a year ago

I don't understand, this solution won't help me to reduce the declaration of values through a single variable

astentx Over a year ago

@Merurz It will. Just wrap this variable with with codes as (select column_value as val from table(sys.odcivarchar2list(...)) and use this CTE as a general table. But you will need to pass a list of codes as a collection (though your programming language should provide some collection wrapper to build a bind variable of such type).

Merurz Over a year ago

How do you declare the variable "codes" with what type?

Littlefoot Over a year ago

I edited the answer and posted some more code; have a look.

OldProgrammer · Accepted Answer · 2022-02-20 21:03:57Z

1

replace the first with clause and str with your string variable, you can do something like this:

with rws as (
  select '''14'',''27'',''50'',''61'',''76''' str from dual
),
item_list as (
  select regexp_substr (
           str,
           '[^,]+',
           1,
           level
         ) value
  from   rws
  connect by level <= 
    length ( str ) - length ( replace ( str, ',' ) ) + 1
)
delete from VIP_ROUTAGE
where vip_TEL_EXT = w_array_tel(i)
AND (
 (w_cp NOT IN  (select value from item_list)) ...

answered Feb 20, 2022 at 21:03

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

3 Comments

Merurz Over a year ago

The PLSQL makes it complicated. I'll try tomorrow. If you think of something simpler, don't hesitate

OldProgrammer Over a year ago

There is no pl/sql in the answer i provided.

Merurz Over a year ago

Except that PLSQL has to go through this operation, because it does not know how to take a classical array as a parameter, unlike other SQL languages

MT0 · Accepted Answer · 2022-02-20 21:44:36Z

0

You can pass in a string (such as '14,27,50,61,76') as a bind variable and use LIKE to compare the delimited string:

DELETE FROM VIP_ROUTAGE
WHERE vip_TEL_EXT = w_array_tel(i)
AND (    ',' || :your_list || ',' NOT LIKE '%,' || w_cp || ',%'
    AND (  ',' || :your_list || ',' LIKE '%,' || SUBSTR(VIP_CODE_POSTAL,1,2) || '%,'
        OR to_number(vip_dest1) < w_tri_ordre))
OR  (    ',' || :your_list || ',' LIKE '%,' || w_cp || ',%'
    AND  ',' || :your_list || ',' LIKE '%,' || SUBSTR(VIP_CODE_POSTAL,1,2) || ',%'
    AND  to_number(vip_dest1) < w_tri_ordre)

db<>fiddle here

edited Feb 20, 2022 at 21:44

answered Feb 20, 2022 at 21:22

MT0

173k12 gold badges70 silver badges136 bronze badges

2 Comments

Merurz Over a year ago

You can't use a like for a value list comparison, in a single value

MT0 Over a year ago

@Merurz This is not, it is comparing two strings (since you state in the question that you are not creating another collection type so you cannot have a list with multiple items; hence, you must be using a scalar instead). You can check the linked db<>fiddle where you can see that the query works.

astentx · Accepted Answer · 2022-02-20 23:08:00Z

If you wish to pass this list of codes as a plain text (not as a bind variable of type collection), then you may use json_table function to expand the list into a table and use it as a general table with a subquery inside in predicate:

with function f
/*Just to emulate bind variable*/
return varchar2
as begin
  return q'('X','Y','Z')';
end;

lkp as (
  select *
  from json_table(
    '[' ||
    /*
      Your string goes here
      instead of invocation f()
    */
    f()
    || ']',
    '$[*]'
    columns (
      col varchar2(100) path '$'
    )
  )
)
select *
from dual
where dummy in (
  /*Filter by list*/
  select * from lkp
)

| DUMMY |
| :---- |
| X     |

db<>fiddle here

If you want to pass this codes as an array (collection in Oracle), then use built-in collection type sys.odcivarchar2list, but you'll need to prepare a bind variable of this type at the application side depending on the programming language (for example, here's an explanation with examples from the official docs for Python).

Collectives™ on Stack Overflow

PLSQL - Use variable array on where clause IN

4 Answers 4

4 Comments

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related