Views Don’t Care Unless They Have To

Oracle’s Edition Based Redefinition (EBR) feature allows modifying database objects such as PLSQL code or database tables without interfering with a functioning application. In this series of articles I’ll introduce some of the underlying concepts of EBR and show some examples of its use. This first post describes a feature of Oracle views that helps enable EBR – the fact that views do not automatically change when their underlying tables do. Here is an example:

-- create demo table
SQL> CREATE TABLE demo
 2 ( col1 NUMBER );
Table created.

-- create a view on top of the table
SQL> CREATE VIEW demo_view AS
 2 SELECT *
 3 FROM demo;
View created.

-- insert a record into the view and only provdide value for col1
SQL> INSERT INTO demo_view
 2 VALUES(1);
1 row created.

-- add a second column to the table
SQL> ALTER TABLE demo
 2 ADD ( col2 NUMBER );
Table altered.

-- Does the view see the new column right away? No!
SQL> INSERT INTO demo_view
 2 VALUES(2);
1 row created.

-- The view remains blissfully unaware
SQL> SELECT *
 2 FROM demo_view;
COL1
----------
 1
 2

-- the table sure knows about the column though
SQL> INSERT INTO demo
 2 VALUES(3);
INSERT INTO demo
 *
ERROR at line 1:
ORA-00947: not enough values

Inserts directly into the table fail because of the new column. But not the view! The view does not see the columns until it is recreated.

SQL> CREATE OR REPLACE VIEW demo_view AS
 2   SELECT *
 3     FROM demo;
View created.

SQL> INSERT INTO demo_view
 2   VALUES(3);
INSERT INTO demo_view
 *
ERROR at line 1:
ORA-00947: not enough values

In future articles I’ll explain how this relates to EBR.

Thanks for reading!

 

Transaction Backout Is Not Picky

All of the examples I’ve shown so far on this blog rely on transaction names to specify what to backout. This is simpler than specifying transaction ID’s. But it does have potential drawback. It backs out ALL discovered. Here is an example that executes single INSERTs in 3 separate transactions – notice the transaction names used.

SQL> SET TRANSACTION NAME 'INSERT';
Transaction set.

SQL> BEGIN
 2     INSERT INTO demo
 3     VALUES(1,'Demo 1');
 4     COMMIT;
 5   END;
 6 /
PL/SQL procedure successfully completed.

SQL> COMMIT;
Commit complete.

SQL> SET TRANSACTION NAME 'BOB';
Transaction set.

SQL> BEGIN
 2     INSERT INTO demo
 3     VALUES(2,'Demo 2');
 4     COMMIT;
 5   END;
 6 /
PL/SQL procedure successfully completed.

SQL> COMMIT;
Commit complete.

SQL> SET TRANSACTION NAME 'INSERT';
Transaction set.

SQL> BEGIN
 2     INSERT INTO demo
 3     VALUES(3,'Demo 3');
 4     COMMIT;
 5   END;
 6 /
PL/SQL procedure successfully completed.

SQL> COMMIT;

What will happen when I backout the transaction (or transactionS) named INSERT?

SQL> DECLARE
 2     v_names SYS.TXNAME_ARRAY := SYS.TXNAME_ARRAY('INSERT');
 3   BEGIN
 4     DBMS_FLASHBACK.TRANSACTION_BACKOUT( numtxns => 1,
 5                                         names => v_names,
 6                                         scnhint => hold_stuff.v_scn,
 7                                         options => DBMS_FLASHBACK.CASCADE );
 8   END;

What records are left in the DEMO table?

SQL> SELECT *
 2     FROM demo;

DEMO_ID    DEMO_NAME
---------- ---------
         2 Demo 2

Both of the INSERT transactions were backed out. Here is the SQL used for the backout.

delete from DRH.DEMO where DEMO_ID = '3' and DEMO_NAME = 'Demo 3'
delete from DRH.DEMO where DEMO_ID = '1' and DEMO_NAME = 'Demo 1'

Beware of this behavior! Or. conversely; embrace it in your applications. I’ll discuss more about that in future entries.

Thanks for reading!

 

Best Dad In The World

I have many things to be thankful for but two of the most important are the town I grew up in and the parents that raised me there. The town is the idyllic Nelson, BC – a great place to grow up. And my parents allowed me to take full advantage of it including the golf course and its much fabled 7th hole.

It was easily reachable for a potential birdie but the green was cruelly sloped outwards towards a deep sand trap. An approach shot with a high loft was required. My Dad favored more of a low line drive so this hole frustrated him. Off the tee we frequently watched his straight but low trajectory approach land in front of the green and then roll. And roll. And roll…into the sand trap. As a youngster tagging along I would ask “Why are you in the sand trap again?” “It just feels right” he’d reply sarcastically. After a swing or two in the trap I’d ask “Why are you still in the sand trap?” “Just enjoying the view” he’d say with a chuckle. One way or another the ball would find it’s way onto the green and a few putts later we’d be on our way to the next tee.

Once I started golfing I was very focused on learning to hit the ball really, really high so it would stop quickly when it landed near the green. Any green. The 7th green.

Over the years the scene would play out again and again – my ball would soar high, land near the putting green and stay away from the trap. My Dad’s would find the trap more often than not and the familiar conversation would follow. Never in anger. Always in fun.

My Dad passed away in September 2014. My Mom spent the last year and a half working slowly through the grieving process. She decided the holiday weekend this May was the time to say a final good-bye and spread his ashes in remembrance. We discussed many places around town that we associated with my Dad – the lake he enjoyed boating on, the church hall he was always fixing something at, the golf course, the park he enjoyed walking at and the power plant where he worked for 20 years. We settled on the dual theme of water and work and chose the river leading up to the power plant.

So on an overcast afternoon we gathered to say a few words, release his ashes into the river and take a shot of his favorite Rum. Afterwards my older brother mentioned his main memory of Dad was his focus on family and this focus had been passed onto us. This made me think of my daughter who has expressed some interest in trying golf. I can hear it now…

“Why are you in the sand trap again Dada?”

“Just enjoying the view…”

I miss you Dad.