[intro music]
I'm Chris Saxon,
the SQL Wizard and today we're going to take a look at something
a little bit special. I'm gonna change the value of a row
without issuing any DML what so ever. No inserts,
no updates and no deletes. Just one SELECT statement.
And the value will change. So, let's get started.
I have here a little one row one column table,
with a little message in it - Hello World! What I'm gonna do
is, just by issuing a SELECT statement
change the value that's returned by this query.
Now, I'm sure you're pretty skeptical and think I've got something going on in the background.
So, to prove there isn't, I'm the only session in the database,
its just me. We can see that its
still got the same value. If we look at the table in the schema browser,
will see that there's no code that refers to it. So we just got the one column.
No dependencies on it. And no triggers on this table.
There is no code that refers to it. So, here we go.
Ready 1..2..3.. and
Yeah! We've changed the value of the query
just by issuing a SELECT statement. That's pretty neat.
Before we go on, let's just put the value back
and save that. When we take a look
we can see that..hmmm
Dint change it or at least not what I expected to.
There is still no triggers on this table.
And there's no other sessions in the database. So what's going on here?
I'm sure you've realized that I'm not using magic really.
What I'm doing is using a new feature of Oracle 12c.
That's Temporal validity. Let's take a look.
When I created the magic table,
I also defined a magic period on it.
I created these hidden columns - magic_start
and magic_end. Now, these don't appear
when you issue a SELECT * but you can refer to them explicitly
as I've done here. Lets take a look. So, we can see we've got magic_start
and magic_end. Now, I also used this
DBMS_ FLASHBACK_ARCHIVE package to set the VALID_TIME to CURRENT.
This means we only see the rows where
the current date is between the Start and End values.
If we set it ALL, will see a bit better what's going on.
And we query it now. We can see we have
actually got three rows in the table. My first row
which was valid for 30 seconds. My second, for
another 30 seconds. My final
row which is valid from a few moments ago and up until
some point tomorrow. So, we can use this Flashback
Archive package to show the currently active
rows. Or all the rows in the table. So we put it back to
CURRENT. We can see we get just the currently active row.
Because these dates are around
the current dates. And we can also use
Flashback Archive to see the data
as it existed at a point in time either in the past
or in the future using this as ASOF syntax.
Like this. So, we execute that
and will see that we get back my first row.
Because that was valid 3 minutes ago from the point in time
I run my ASOF statement. Now, we set it to 5 minutes ago.
Notice that the Start is less than 5 minutes ago.
So, we set that. Think so.
We find the query. Doesn't return anything.
This is because there's no rows where the magic starts
with 5 or more minutes ago. Now, let's just put it back to CURRENT.
And this is all very well and good.
But, what I haven't explained is, where the bananas have come from.
[music]
That was the Magically Changing Row trick.
We saw how I used Temporal validity to change the value
returned by a query without having to issue any DML
what so ever. For more information
about Temporal validity and to find out where all those bananas came from,
stay tuned for the Magically Changing Row - Behind the curtain episode.
See you next time.
[music]
Thanks for watching.
I'm Chris Saxon, the SQL Wizard. Subscribe to my YouTube channel
to get future updates on more SQL Magic.
I'll see you next time.
[exit music]