Hi folks,

In this post I talk about how to create literal tables in Oracle.

You may have a list of values and want to run an Oracle query against those values.

A convenient way to do this is to create a literal table in an Oracle SQL using those values.

Single-column

If you want to create a single-column literal table,

you can use either dbms_debug_vc2coll or sys.ODCIVarchar2List

e.g.

select * from table (sys.ODCIVarchar2List('AAA', 'BBB', 'CCC'));

This will return an inline table with a single column named COLUMN_VALUE and rows with values AAA, BBB and CCC.

i.e.

COLUMN_VALUE
==
AAA
BBB
CCC

Second example

select column_value from table(sys.dbms_debug_vc2coll(‘AAA’, ‘BBB’, ‘CCC’))

Multiple colums

If you want to generate a literal table with two columns, you can use a query like:

select objectschema m, objectname n
from
table(sys.ODCIObjectList(
sys.odciobject('APPLE', 'FRUIT'),
sys.odciobject('CARROT', 'VEGGIE')
));

For more tahan 2 columns, you can use a query like:

create type t as object (a varchar2(10), b varchar2(10), c number);
create type tt as table of t;

select * from table( tt (
    t('APPLE', 'FRUIT', 1),
    t('APPLE', 'FRUIT', 1122), 
    t('CARROT', 'VEGGIE', 3),
    t('PEACH', 'FRUIT', 104),
    t('CUCUMBER', 'VEGGIE', 5),
    t('ORANGE', 'FRUIT', 6) ) )

That’s all for now. Happy database querying.

Sources

Oracle SQL Tip: Using dbms_debug_vc2coll and sys.ODCIVarchar2List. https://training.fusionapplied.com/2017/08/23/oracle-sql-tip-using-dbms_debug_vc2coll-and-sys-odcivarchar2list/

Is there a non-ugly way to use a multi-column, multi-row table literal in an Oracle 11g query? https://stackoverflow.com/questions/29903397/is-there-a-non-ugly-way-to-use-a-multi-column-multi-row-table-literal-in-an-ora/29904128