Is there a way to create an inline anonymous table in Oracle like in PostgreSQL?-CodePudding

I have a postgres query that creates an anonymous table from string values and then joins on them like this:

select distinct anon_table.string1, 
                anon_table.string2, 
                anon_table.string3, 
                rt.string4
from real_table rt
right join (values ('asdf', 'asdf', 'asdf'), ('ghjk','ghjk','ghjk')) AS anon_table (string1, string2, string3)
on rt.string1 = anon_table.string1

Is it possible to do this inline with pl/sql in an oracle database? I need to be able to be able to create an inline anonymous table from a bunch of values with named columns that I can then join on in the outer query. Would much prefer to be able to do it this way instead of with a temp table.

CodePudding user response：

You can do it with SQL if you SELECT from the DUAL table and use UNION ALL for multiple rows:

SELECT DISTINCT
       anon_table.string1, 
       anon_table.string2, 
       anon_table.string3, 
       rt.string4
FROM   real_table rt
       RIGHT OUTER JOIN (
         SELECT 'asdf' AS string1, 'asdf' AS string2, 'asdf' AS string3 FROM DUAL
         UNION ALL
         SELECT 'ghjk','ghjk','ghjk' FROM DUAL
       ) anon_table
       ON rt.string1 = anon_table.string1

Or, using a sub-query factoring clause:

WITH anon_table (string1, string2, string3) AS (
  SELECT 'asdf', 'asdf', 'asdf' FROM DUAL UNION ALL
  SELECT 'ghjk','ghjk','ghjk' FROM DUAL
)
SELECT DISTINCT
       anon_table.string1, 
       anon_table.string2, 
       anon_table.string3, 
       rt.string4
FROM   real_table rt
       RIGHT OUTER JOIN anon_table
       ON rt.string1 = anon_table.string1

Or you can declare a object and a collection type in the SQL scope:

CREATE TYPE string_triplet AS OBJECT(
  string1 VARCHAR2(20),
  string2 VARCHAR2(20),
  string3 VARCHAR2(20)
);

CREATE TYPE string_triplet_list AS TABLE OF string_triplet;

Then:

SELECT DISTINCT
       anon_table.string1, 
       anon_table.string2, 
       anon_table.string3, 
       rt.string4
FROM   real_table rt
       RIGHT OUTER JOIN TABLE(
         string_triplet_list(
           string_triplet('asdf', 'asdf', 'asdf'),
           string_triplet('ghjk', 'ghjk', 'ghjk')
         )
       ) anon_table
       ON rt.string1 = anon_table.string1

db<>fiddle here

Is it possible to do this inline with pl/sql in an oracle database?

Not without some difficulty as you cannot use PL/SQL defined types in the SQL scope. But then you don't really need PL/SQL as you can do it in SQL as demonstrated by the examples above.