Storing a list of codes into a variable in Oracle SQL

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];

You can put them in a table and then join to it or subquery it. — dfundako, Feb 03 '21 at 17:20
I would hope that there would be a lookup table in the database that defines all the possible codes. If you have queries where you want to look for a bunch of different codes, that strongly implies that there is some attribute that should be defined as a column of that lookup table which your queries can reference. So if these are all codes for ice cream flavors and you've got a list of all the codes that involve peanuts that you use for an allergies query, it would make sense to add a `has_peanuts` flag to the `ice_cream` table where all the possible codes are stored. — Justin Cave, Feb 03 '21 at 18:35
What is 'SQL code'? SQL is a query language, it doesn't really have variables as in your example. PL/SQL does because it's a programming language, but that's a whole different question. — William Robertson, Feb 03 '21 at 22:30

score 1 · Accepted Answer · answered Feb 03 '21 at 17:28

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.

score 1 · Answer 2 · answered Feb 03 '21 at 17:36

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;

Storing a list of codes into a variable in Oracle SQL

2 Answers2