Storing a list of codes into a variable in Oracle SQL [duplicate]

Question

There's a way to store a set of codes into a variable in Oracle SQL?

I have these codes and I'll need to use them in different parts of my query.

But I wouldn't repeat this list in many places in my SQL code.

'G31', 'G310', 'G311', 'G312', 'G318', 'G319', 'G239', 'G122', 'G710',
     'B20', 'B22', 'B23', 'B24', 'G35', 'C811', 'G37', 'G375', 'K702', 'K741'

I would like to do something like this idea:

LIST <- ['G31', 'G310', 'G311', 'G312', 'G318', 'G319', 'G239', 'G122', 'G710',
         'B20', 'B22', 'B23', 'B24', 'G35', 'C811', 'G37', 'G375', 'K702', 'K741']

SELECT * FROM TABLE_A where COLUMN IN [LIST];

SELECT * FROM TABLE_B where COLUMN IN [LIST];

Answer 1

A fancy approach is this

WITH CODE_VALUES AS 
  ( SELECT DISTINCT COLUMN_VALUE AS CODE_VALUE
      FROM TABLE (sys.dbms_debug_vc2coll ('G31',
                                      'G310',
                                      'G311',
                                      'G312',
                                      'G318',
                                      'G319',
                                      'G239',
                                      'G122',
                                      'G710',
                                      'B20',
                                      'B22',
                                      'B23',
                                      'B24',
                                      'G35',
                                      'C811',
                                      'G37',
                                      'G375',
                                      'K702',
                                      'K741'))
  )
  SELECT *
    FROM CODE_VALUES -- + the rest of your query

You could do the same thing with successive union's against "dual" too

WITH CODE_VALUES AS 
    ( SELECT 'ABC' AS code_value FROM dual UNION 
      SELECT 'CDE' AS code_value FROM dual
    )

If this is going to get used across multiple operational queries it's probably best just to store them in a table.

Answer 2

Create a global temporary table once and add the desired values in the gtt and then use it in query using join.

Benifit of gtt is that you don't have to worry about data maintance. (Delete - insert). Data added in one session/transaction will be visible in that session/transaction only (based on type of gtt that you have created.

Create global temporary table gtt
(Col1 varchar2(10))
On commit preserve row; -- session specific

Insert into gtt
Select 'G31' from dual union all
Select 'G310' from dual union all
...
...
Select 'K741' from dual;

Now, you can use it anywhere in the same session as follows:

SELECT * 
FROM TABLE_A a
Join gtt g on a.COLUMN = g.col1;

SELECT * 
FROM TABLE_B b
Join gtt g on b.COLUMN = g.col1;