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

sqlarraysoraclevariables


Solution 1:[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)) ...

Solution 2:[2]

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

Solution 3:[3]

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).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 OldProgrammer
Solution 2
Solution 3 astentx

Related Questions