Writing a SQL query over a given set of values

I am trying to write a plsql query that allows me to query a set of known values that are not stored in a table. Say those known values are the following strings:

• abc
• def
• ghi
• jkl

I would like to achieve something like the following:

select * from [fill-in-the-blank] myvalues 
where not myvalue开发者_如何学编程s in 
(
    select id from dbtable
)

..where I am trying to ascertain which of those know values are not in a database table.

Constraints

• This is pl/sql (oracle)
• This solution must run from within Oracle PL/SQL Developer
• I only have read access to the schema so I cannot create temporary tables.

Any ideas?

You could use a Common Table Expression (CTE) to accomplish this:

with cte as (
    select 'abc' as id from dual
    union all
    select 'def' from dual
    union all
    select 'ghi' from dual
    union all
    select 'jkl' from dual
)
select * 
from cte
where not id in 
(
    select id from dbtable
)

In fact, you may not even really need the CTE at all (though I find it aids readability):

select * 
from (
    select 'abc' as id from dual
    union all
    select 'def' from dual
    union all
    select 'ghi' from dual
    union all
    select 'jkl' from dual
)
where not id in 
(
    select id from dbtable
)

Old thread I know, but nobody mentioned

select * from table(sys.dbms_debug_vc2coll('abc','def','ghi','jkl'));

You don't have to use sys.dbms_debug_vc2coll, of course. Available collection types can be listed using:

select c.owner, c.type_name, c.elem_type_name, c.length
from   all_coll_types c
where  c.coll_type = 'TABLE'
and    c.elem_type_name = 'VARCHAR2'
order by 1,2;