DBMS_DEBUG_VC2COLL

Today I was asked to re-process 700+ orders. A list of Order_IDs was provided and I was expected to update the order status and process date. It seems to be an easy task,

1 2 3

update orders    set status = 'PAID',last_modified_dt = sysdate  where order_id in (1234, 1235, 1239)

But the problem is there are some orders missing. There are no such orders exist in the database for some order_IDs. I was asked to provide a list of those missing order_IDs. It is easy to find order_IDs exist in the database,

1 2 3

select order_ID   from orders  where order_id in (1234, 1235, 1239)

For example, only order_ID 1234 and 1235 exist. Order_ID 1239 does not exist in the database. How could we find the non-existing orders? My first thought is spool the result of the select statement to a file, then compare it with the file including all order_IDs using diff command. It definitely works. Is there a neat way in SQL to do it? I found a very handy collection data type SYS.DBMS_DEBUG_VC2COLL, which can convert a list to an array. Here are some examples.

1 2	select *   from table(sys.dbms_debug_vc2coll('a','b','c'));

COLUMN_VALUE ------------ a b c

1 2	select *   from table(sys.dbms_debug_vc2coll(1,2,3));

COLUMN_VALUE ------------ 1 2 3

Let us go back to my case. How can I achieve my goal?

1 2 3 4 5 6

select *   from table(sys.dbms_debug_var2coll(1234, 1235, 1239)  minus select order_ID   from orders  where order_id in (1234, 1235, 1239)

That is it. It solves the problem without using diff command. Is there any other better way?