Selecting static values to union into another query

Question

Essentially, my problem is that I need to run a query in Oracle that unions a static list of values ('Static' meaning it's obtained from somewhere else that I cannot get from the database, but is actually an arbitrary list of values I plug into the query) with a dynamic list of values returned from a query.

So, my initial query looks like:

select * from (select ('18776') as instanceid from dual) union (<more complex query>)

I think, hooray! And then try to do it with a longer list of static values. Turns out, I get 'Missing Right Parenthesis' if I try to run:

select ('18776','18775') as instanceid from dual

So, my basic issue is how can I integrate a list of static values into this union?

NOTE: This is a simplified example of the problem. The actual list is generated from an API before I generate a query, and so this list of "static" values is unpredictably and arbitrarily large. I'm not dealing with just 2 static values, it is an arbitrary list.

Answer 1

select '18776' as instanceid from dual union all
select '18775' as instanceid from dual 

or

select column_value from table(sys.odcivarchar2list('18776', '18775'))

or some sort of hierarchical query that could take your comma separated-string and split it into a set of varchars.

Union these to your initial query.

update: "I'm not dealing with just 2 static values, it is an arbitrary list."

Still can pass to a query as a collection (below is just one of many possible approaches)

23:15:36 LKU@sandbox> ed
Wrote file S:\spool\sandbox\BUFFER_LKU_39.sql

  1  declare
  2    cnt int := 10;
  3    coll sys.odcivarchar2list := sys.odcivarchar2list();
  4  begin
  5    coll.extend(cnt);
  6    for i in 1 .. cnt loop
  7      coll(i) := dbms_random.string('l', i);
  8    end loop;
  9    open :result for 'select * from table(:c)' using coll;
 10* end;
23:37:03  11  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.50
23:37:04 LKU@sandbox> print result

COLUMN_VALUE
-------------------------------------------------------------
g
kd
qdv
soth
rvwnq
uyfhbq
xxvxvtw
eprralmd
edbcajvfq
ewveyljsjn

10 rows selected.

Elapsed: 00:00:00.01