Last night I couldn’t sleep and what else you’re going to do? I was thinking about Oracle stuff.
In Oracle version 12, Oracle has enhanced the WITH clause – traditionally used for sub-query factoring – to allow the declaration of functions and procedures. This can be (ab)used to create a very interesting scenario, that is not very common in Oracle: Reading data within the same SELECT statement, but from two different points in time. And the points in time are in the future, and not in the past.
Let’s say I want to take a snapshot of the current SCN, and then another one 5 or 10 seconds after that. Traditionally we’d have to store that somewhere. What if I could take two snapshots – at different SCNs – using a single SELECT statement ? Without creating any objects ?
col value for a50 set lines 200 pages 99 with procedure t (secs in number, scn out varchar2) is pragma autonomous_transaction; begin dbms_lock.sleep(secs); select 'at ' || to_char(sysdate,'HH24:MI:SS') || ' SCN: ' || dbms_flashback.get_system_change_number into scn from dual; end; function wait_for_it (secs in number) return varchar2 is l_ret varchar2(32767); begin t(secs, l_ret); return l_ret; end; select 1 as time, 'at ' || to_char(sysdate,'HH24:MI:SS') || ' SCN: ' || dbms_flashback.get_system_change_number as value from dual union all select 5, wait_for_it(5) from dual union all select 10, wait_for_it(5) from dual /And the result is:
TIME VALUE ---------- -------------------------------------------------- 1 at 09:55:49 SCN: 3366336 5 at 09:55:54 SCN: 3366338 10 at 09:55:59 SCN: 3366339
We can clearly see there, that the SCN is different, and the time shown matches the intervals we’ve chosen, 5 seconds apart. I think there could be some very interesting uses for this. What ideas can you folks come up with ?
Interested in working with Stefan? Schedule a tech call.