Comparing list of values against table

Question

I tried to find solution for this problem for some time but without success so any help would be much appreciated. List of IDs needs to be compared against a table and find out which records exist (and one of their values) and which are non existent.
There is a list of IDs, in text format:

100,
200,
300 

a DB table:

ID(PK)   value01 value02 value03 .....
--------------------------------------
100       Ann
102       Bob
300       John
304       Marry
400       Jane

and output I need is:

100 Ann
200 missing or empty or whatever indication
300 John

Obvious solution is to create table and join but I have only read access (DB is closed vendor product, I'm just a user). Writing a PL/SQL function also seems complicated because table has 200+ columns and 100k+ records and I had no luck with creating dynamic array of records. Also, list of IDs to be checked contains hundreds of IDs and I need to do this periodically so any solution where each ID has to be changed in separate line of code wouldn't be very useful. Database is Oracle 10g.

Solution

there are many built in public collection types. you can leverage one of them like this:

with ids as (select /*+ cardinality(a, 1) */ column_value id
               from table(UTL_NLA_ARRAY_INT(100, 200, 300)) a
            )
select ids.id, case when m.id is null then '**NO MATCH**' else m.value end value
  from ids
         left outer join my_table m
                     on m.id = ids.id;

to see a list of public types on your DB, run :

select owner, type_name, coll_type, elem_type_name, upper_bound, precision, scale from all_coll_types
 where elem_type_name in ('FLOAT', 'INTEGER', 'NUMBER', 'DOUBLE PRECISION')

the hint

/*+ cardinality(a, 1) */

is just used to tell oracle how many elements are in our array (if not specified, the default will be an assumption of 8k elements). just set to a reasonably accurate number.