ops$tkyte%ORA11GR2> WITH t_old AS
2 (SELECT id,
3 c_lob
4 FROM test_table
5 WHERE 1=2),
6 t_new AS
7 (SELECT id,
8 c_lob
9 FROM test_table
10 WHERE id = 1)
11 SELECT t_old.id,
12 nvl(t_old.c_lob, t_new.c_lob)
13 FROM t_old,
14 t_new
15 WHERE t_old.id = t_new.id(+)
16 UNION ALL
17 SELECT t_new.id,
18 t_new.c_lob
19 FROM t_old,
20 t_new
21 WHERE t_old.id(+) = t_new.id
22 AND t_old.id IS NULL;
ID NVL(T_OLD.C_LOB,T_NEW.C_LOB)
---------- --------------------------------------------------------------------------------
1 AAAAA
I cannot reproduce.
can you give a real example that shows what you are trying to do for real. T_new and T_old are distracting from the problem you are trying to solve here (they confuse me)
what are you really trying to do in real life?