12c: Fun with WITH!

Posted in: Technical Track

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

procedure t (secs in number, scn out varchar2)
    pragma autonomous_transaction;
    select 'at ' || to_char(sysdate,'HH24:MI:SS') || ' SCN: ' 
                 || dbms_flashback.get_system_change_number 
      into scn 
      from dual;
function wait_for_it (secs in number) 
 return varchar2 is
    l_ret varchar2(32767);
    t(secs, l_ret);
    return l_ret;
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:

---------- --------------------------------------------------
         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.

About the Author

Stefan is a passionate Oracle database researcher and has been focusing on understanding the Oracle database at its core for more than 10 years. His primary focus lies in the performance of the database, its security aspects, and using its various features to solve problems and architect efficient database designs. Stefan is also an avid technical writer and has written large documentation sets from scratch for massive Oracle projects.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *