How can I select from list of values in Oracle

Advanced selection techniques

Dipping deeper into database programming, Oracle offers a delectable array of advanced techniques to proficiently select from a list of values.

Parsing comma-separated lists

Dealing with comma-separated values (CSV)? Oracle answers that with its regexp_substr function that coupled with connect by level breaks down a CSV into an organized table structure:

SELECT 
  trim(regexp_substr('value1,value2,value3', '[^,]+', 1, level)) AS value
FROM dual
CONNECT BY 
  regexp_substr('value1,value2,value3', '[^,]+', 1, level) IS NOT NULL;


<!-- Label this as the "Split Personality Technique". The query splits (see what I did there?) the list into rows. -->

Taming number collections

When values happen to be of numeric type, Oracle suggests using the sys.odcinumberlist that spares you from creating a custom type:

SELECT * FROM TABLE(sys.odcinumberlist(1, 2, 3)); 


For Oracle v12.2 and above, it allows direct collections insertion into tables:

INSERT INTO your_table
SELECT * FROM sys.odcinumberlist(1, 2, 3);


<!--This trick bypasses the need to use the TABLE function. It's like when you find a shortcut to your favorite coffee shop!-->

Custom PL/SQL parsing functions

If the situation gets tangled, writing a PL/SQL function can turn a CSV string into a table for easy manipulation:

CREATE OR REPLACE FUNCTION parse_csv (p_csv IN VARCHAR2)
   RETURN sys.odcivarchar2list PIPELINED IS
BEGIN
   FOR i IN (SELECT 
                trim(regexp_substr(p_csv, '[^,]+', 1, level)) AS value
             FROM dual
             CONNECT BY 
                 regexp_substr(p_csv, '[^,]+', 1, level) IS NOT NULL)
   LOOP
      PIPE ROW(i.value);
   END LOOP;
   RETURN;
END parse_csv;
/


<!-- Surely, what's more satisfying than writing your own parsing function? Maybe, getting free coffee? -->

SELECT * FROM TABLE(parse_csv('value1,value2,value3'));


User-defined type creation

Creating a SQL type adds consistency to your codebase. Define a type as well as matching it with a table type:

CREATE TYPE varchar2_tab AS TABLE OF VARCHAR2(100);
/


<!--This is like baking your own bread - the best part is, it's gluten-free!<--->

Then, you'll see the convenience of using the new type:

SELECT * FROM TABLE(varchar2_tab('value1', 'value2', 'value3')) AS t;


Schema Matching

When incorporating custom solutions, always ensure the database schema aligns with your methods. When adopting a number_tab type approach, always make sure the receiving table can accept these values:

CREATE TYPE number_tab AS TABLE OF NUMBER;
/


INSERT INTO your_number_table (col)
SELECT * FROM TABLE(number_tab(1, 2, 3)) AS t;


<!-- Always remember, "There is no place like the home (database schema) for your data". -->

If dealing with delimited values frequently, you will find favor in the robustness of Justin Cave's regular expression SQL method. Trust us, it makes life easier!