Monday, January 28, 2013

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,
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,
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.
select * 
  from table(sys.dbms_debug_vc2coll('a','b','c'));
COLUMN_VALUE
------------
a
b
c
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?
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?

No comments:

Post a Comment