How to declare an array in PL/SQL so that it can be used later in IN?

Question

Oracle

Good afternoon. Suppose there is a query with an IN condition (below is a simple example). Can I declare a collection of items ('IBM', 'Hewlett Packard', 'Microsoft') ahead of time in DECLARE as a constant?

SELECT *
FROM customers
WHERE customer_name IN ('IBM', 'Hewlett Packard', 'Microsoft');

Answer 1

Yes.
You can write your own utility type like:

create or replace type string_tab as table of varchar2(4000 char)
/

Declare a constant at method level, package body, or package spec
And use in select

-- Если константа пакетная, то <package_name>.c_customers
select *
from customers
where customer_name in (select column_value from table(c_customers));

alternative, but with its drawbacks :-)

select *
from customers
where customer_name member of c_customers;