Introduction to Flashback Technology (Part 1: Application Developers)

Imagine, if you will, that someone invented time travel. What would you do with it? Nip back into the past and fix that error you made? And maybe you’d want to do something about that silly, ill-advised haircut you had when you were 17. Well, the good news is that, in a manner of speaking, time travel has been invented. At least as far as Oracle databases go.

And the bad news?  There’s nothing it can do about your teenage haircut.

Oracle Flashback Technology is a suite of features that enables Oracle application developers and database administrators to wind the clock back and examine their database objects and data in a previous state without having to resort to backup media. Simply put, Oracle Flashback Technology is time travel for databases.

In this series of articles I intend to expound on the benefits of Oracle Flashback to application developers and to database administrators. This first installment will concentrate on the tools that will be of interest to application developers.

Oracle Flashback Technology is actually a collection of features that enables the developer to examine the database’s recent past in a number of ways:


Oracle Flashback Query:
Flashback query is a neat trick. It allows you run a query against your data as it was at past time. Imagine you accidentally delete a number of rows and commit your changes. With flashback query you can easily reach into the past and retrieve those lost rows.  Using the SELECT... AS OF clause you can examine the ghost of data past by referencing its timestamp.

SELECT *
FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2012-04-21 17:00:00','YYYY-MM-DD HH24:MI:SS');

And if you wanted to view those rows that you accidentally deleted 5 minutes ago?

SELECT *
FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '6' MINUTE)
MINUS
SELECT *
FROM emp;

Continue reading...

Introduction to Materialized Views: Query Rewrite

In the fairy tale, The Shoemaker and The Elves, a poor cobbler works to the best of his ability; and somehow, when he isn’t looking, magical beings would sneak in and polish and perfect his handiwork.

It seems the people at Oracle are up on their fairy tales. Perfectly-tuned queries are the Holy Grail to developers and DBAs; we are constantly fiddling, forever optimising, shaving milliseconds from the total run time. Well, since 8i, functionality has been in place that can take our DML statements and magically improve them. 

Oracle do not admit that this involves any elves. But I have my suspicions.

How It Works:

Here’s how it works: part of the reason queries involving joins between data-heavy tables can take such a long time is that the engine needs to compute aggregates and disentangle the joins between the tables before it can run the query. However, materialized views, by their very nature, have all that information precomputed. Therefore, if you can find the right materialized view and match it to the right query, you can cut running times dramatically.

This trick, called Query Rewrite, only works with SELECT statements. However, those statements may be hidden away in a CREATE TABLE … AS SELECT statement, or an INSERT INTO … SELECT statement, or they may be squirrelled away in any type of subquery or sub-clause. 

Additionally, Query Rewrite must be enabled at the initialization parameter level:

Alter session set query_rewrite_enabled = TRUE;

Continue reading...

Alter session set triggers disabled (or The personal anti-ninja assassin insurance policy)

If your database was a city, your tables would be buildings, your constraints would be the streets and motorways that connect them, your records would be the citizens that populate them, your views the nightclubs and restaurants where like-minded records go to hang out.

And triggers? Well, they'll definitely be ninjas. Think about it: easily overlooked, moving with stealth, 'firing' on command, shrouded in mystery.

Triggers, of course, are immeasurably useful. You don't need me to tell you that. However, there are times when you wish you could flip a switch and turn them off: imagine you are inserting or updating a truckload of data and cannot afford the overhead, or you are tracking a simple statement and do not want it to take any unexpected detours. 

Of course, you can knock up a quick anonymous pl/sql block to loop through all your triggers (or at least all the relevant ones) and dynamically disable them.

/*
** This block will disable all database triggers. Do NOT do this.
*/
begin
  for i in (select trigger_name
            from user_triggers) loop

            execute immediate 'alter trigger '||i.trigger_name||' disable';
  end loop;
end;

"Ladies and gentlemen, this is your pilot speaking. We will soon be flying across the Himalayas mountains. I will fly this section with my eyes closed. Because I can. Please, sit back and enjoy the complimentary peanuts and your in-flight movie, Alive."

Just because you can do a thing, does not mean that you should. You wrote those triggers for a reason; so unless you can guarantee that no other users will be using your database, switching them all off for any length of time might be a tad unwise. What you need in those circumstances is the ability to disable triggers for only your session. It'll be like your personal anti-ninja assassin insurance policy.

Unfortunately, ALTER SESSION SET TRIGGERS DISABLED; is not a valid Oracle command. So until it is, we are going to need to create a substitute for it.

The easiest way to do this would be to create a session variable, a boolean that you can switch on or off, and then edit your triggers to only fire dependent on the state of this variable.

The first bit is easy. A variable in the specification section of a database package makes a perfect session-specific boolean.

create or replace package trg_pkg as
   skip_trigger  boolean := FALSE;
end trg_pkg;

Now all we need do is add a line as the first bit of executable code in our triggers checking the state of our trg_pkg.skip_trigger variable. If it is TRUE, we exit the trigger without executing any code; if not, we fire the trigger as normal. Locate the triggers you may want to switch off and add the following to them:

 
...
BEGIN
  if trg_pkg.skip_trigger then return; end if; -- do not execute the trigger.
...

And that's it. Whenever you do not want triggers to fire in your session, all you need do is flip your trg_pkg.skip_trigger switch. The triggers won't fire for whatever commands you carry out, but will work as normal for everyone else. (For security reasons, you will probably want to control which users are granted execute permissions on your trg_pkg package.)

One last thing, if you want to add the clause to all the triggers in your schema - as I have needed to do recently - you might find the following code handy.

/*
** This script will rebuild all triggers adding a clause which
** prevents the trigger from firing if trg_pkg.skip_trigger is TRUE.
** This code presumes that no trigger is longer than 32767 characters long. 

** 04/02/2012  David Njoku * Created this script.
*/

declare 
 vSql  varchar2(32767);
 vBackup  varchar2(32767);
 vString  user_source.text%type;
 
 vCLAUSE  CONSTANT varchar2(500)  := ' if trg_pkg.skip_trigger then return; end if; -- Do not execute this trigger if trg_pkg.skip_trigger is set to true. ';
 
 
begin 
 -- Find all the triggers that relate to this schema's tables.
 for i in (select at.trigger_name, at.owner
    from all_triggers at, all_objects ao
    where at.table_owner = USER
    and at.status = 'ENABLED'
    and at.trigger_name = ao.object_name
    and at.owner = ao.owner
    and ao.object_type = 'TRIGGER'
    and ao.status = 'VALID'
    and not exists (select 1
      from recyclebin
      where object_name = at.trigger_name)) loop 
    
    -- Now that we've got the trigger name and owner, we need to grab a hold of the existing code, in case we need to revert.
    vBackup := ' CREATE OR REPLACE ';
    for j in (select replace(text,chr(10),' ') text 
       from all_source
       where owner = i.owner
       and name = i.trigger_name
       and type = 'TRIGGER'
       order by line) loop 
       
       vBackup := vBackup||chr(10)||j.text;
    end loop;
    
    /*
    ** We now need to start building our new version of the trigger.  The plan is to add our if clause immediately after the BEGIN 
    ** Some triggers will contain more than 1 BEGIN. Since we cannot tell which is the correct one, we'll need to add our clause after
    ** all of them.
    */

    vSql := ' CREATE OR REPLACE ';
    for j in (select replace(text,chr(10),' ') text 
       from all_source
       where owner = i.owner
       and name = i.trigger_name
       and type = 'TRIGGER'
       order by line) loop 
       
       -- do we need to add our clause here?
       if replace(upper(j.text),' ','') like '%BEGIN' then 
        vSql := vSql||chr(10)||j.text||chr(10)||vCLAUSE;
       else 
        vSql := vSql||chr(10)||j.text;
       end if;
    end loop; 
    
    -- Now we've got our trigger we should try building it.  
    begin 
     execute immediate vSql;
    exception 
     when others then 
     dbms_output.put_line(i.trigger_name||' '||sqlerrm);
    end; 
    
    -- If the trigger was not built successfully for whatever reason we should revert to the orginal.
    for j in (select 1
      from all_triggers
      where trigger_name = i.trigger_name
      and owner = i.owner
      and status = 'DISABLED') loop 
      
      begin   
       execute immediate vBackup;
      exception 
       when others then 
       dbms_output.put_line('BACKUP '||i.trigger_name||' '||sqlerrm);
      end;
    end loop; 
 end loop;
end;
/

Introduction to Regular Expressions in Oracle

Regular expressions – RegEx or RegExp to their friends – were fashionably late in coming to the Oracle party. The seeds of what we know today as regular expressions were formulated in the period immediately following the Second World War in fields as diverse as formal language theory and neurophysiology; but it wasn’t until the PL/SQL Web toolkit arrived for 8i and 9i that they popped their heads over the Oracle parapet. By this time they were already rock stars in the worlds of Unix and Perl.

But I’m being rude here; I should introduce you. Regular expressions are a codified means to accomplish flexible pattern-matching in strings of text.

And, if you’re rereading that definition and thinking ‘huh?!’, that’s a feeling you might want to get used to, because, while regular expressions are powerful and definitely useful, they can seem very much like hieroglyphics to the uninitiated. But don’t worry, I’ll hold your hand.

String pattern matching has always been possible in Oracle sql and pl/sql, of course. With the LIKE condition and the simple metacharacters of “%” and “_”, character patterns could be described and integrated into queries. However, the moment you required a little complexity these basic wildcards cowered in fear. An example: my name is David, but friends tend to call me Dave when they have a beer or two in them. To match either spelling of my name, not using regular expressions, you would have to query the table for ‘Dav%’. But then your results might be flooded with Davidsons and Davises and Davinas. And I’m definitely not pretty enough to be called Davina!

Continue reading ...

Introduction to Autonomous Transactions (Or If Obama And Merkel Had A Lovechild...)

I don't know if you've been paying attention, but you may have noticed that there's a bit of a recession going on at the moment. Stock prices are crashing around the globe, and I'm pretty sure that the Euro is now worth less than Monopoly money. It's very much like the Great Depression of the 1930s all over again - except that, this time, the bankers don't have the decency to throw themselves out of windows.

The solution, David Cameron, Angela Merkel and Barack Obama assure us, is for everyone to go out and spend more money. That'll kickstart our economies, they assure us. More transactions. A lot more.

So they'll probably thank me for this article that I wrote over at All Things Oracle about autonomous transactions. Have a read; you may thank me too.

AUTONOMOUS TRANSACTIONS IN ORACLE

Autonomous transactions are a bit like that dark part of the forest, beyond the rickety bridge, where the shadows are shaped like witches and bats swarm soundlessly from the trees. We all know it’s there, but the wise are in no hurry to visit.

Here, however, is a definition: an autonomous transaction is a completely independent transaction that is started by temporarily suspending the parent/calling transaction, which is then resumed after the independent transaction is completed. Or, if you prefer a real-world analogy: an autonomous transaction is a little like getting married, but having an affair on the side.

Morality isn’t the reason autonomous transactions are frowned upon; they possess the potential to get out of hand. Autonomous transactions are completely self-contained transactions that commit independently, and so to start one is to juggle two transactions...

Continue reading...

Should Your Next Project Be Written In Forms or Apex? (Or The Polygamist's Dilemma)

The polygamist pauses, his hand hovered above the doorknob. He looks back down the hallway, blinking in the harsh light of the naked bulb he has just flipped on to do war with the shapeless darkness of the night. A few paces away - no further away than a firm decision - stands another door: silent, inviting. His gaze returns to the door in front of him; also as silent as the entrance to Aladdin's cave. Which door should he open? Which of his beloved wives should he visit?

Okay, I admit it, I know nothing about polygamy. I once caught an episode of Big Love on telly, and I enjoyed reading Lola Shoneyin's The Secret Lives Of Baba Segi's Wives, but that's about it.

I do, however, know a bit about Oracle Forms and Oracle Application Express. Which is kinda fortunate since I'm guessing you're not here for marital advice. A question I do get asked from time to time - most recently on LinkedIn - is this: What technology should I choose for my new project - Oracle Forms or Oracle Application Express?

At the risk of sounding like a guru sat atop a Himalayan rock, there is only one real answer to this question: look within your heart,  the answer floats like an asteroid in the galaxy of your inner space. Or something. Personally, if I was making the decision a year ago I'd have said Apex cos it was new and exciting; today, I'd probably say Forms, cos it's old and I better understand its foibles: the polygamist's dilemma - the new, nubile wife, or the old wife who he fully understands?

You will probably want to base your decision on factors more concrete and quantifiable than imaginary asteroids, so here are some things to consider.

Skills: What skills do you have? What skills are you willing to invest in? What skills are you capable of acquiring? I would imagine that you have a greater wealth of Forms experience. This need not be the deciding factor, but it should definitely count as a tick in that box. I was able to retrain my team of Forms developers in Apex, but it took some time and some expense. And, crucially, it was a challenge they were happy to face: I still have nightmares about The Big Oracle ADF Disaster of 2008. My therapist says I'll get over it one day, but I doubt it.

Platform: A huge part of your choice must depend on the demands of the project itself. For instance, what platform are you hoping to run it on? If you are planning to take advantage of the current boom in mobile computing, then Apex should be your choice. I must admit that I have not yet tried to run a Forms application on a phone or a tablet, but I do not suspect that that is an experiment that would end happily.

If, on the other hand, you expect your application to be run only from PCs and laptops using a myriad of browsers, with or without javascript enabled, then possibly Forms with its browser-agnostic java applet might be worth a look-in.

Cost: If you are a millionaire Saudi Arabian prince who has only got to this page cos you googled "I am looking for the oracle on polygamy", you can skip this bit. The rest of you, pay attention. With its middleware costs,  Forms is undeniably the more expensive option. I am not allowed anywhere near the money at my company, but I am assured that the difference in cost can be considerable.

Audience: Who is your application aimed at? If it is to be published on the open Internet, then you only really have one choice - Apex. I am not denying the work Oracle's Forms team has done to modernise the old girl, but I doubt that it will ever be truly suitable for the world-wide web.

However, if you are building an enterprise application, then there is definitely something to be said for the solidity and gravitas that Forms still exudes. And if your application will be used mostly for data-entry, then Forms provides speed that Apex, even with its clever javascript and ajax hooks, cannot match.

Extendability: The answer to the question of which is the more extendable technology may not be as obvious as it may first seem. Being a web technology, Apex can more easily sup at the banquet of ajax and javascript, can more easily flirt with jQuery and whatever the next big thing will be. But with each iteration of Forms, its door is opened wider, with Java pluggable components and javascript and CSS support. However, I believe that Apex will always outpace Forms in this respect; the story of the tortoise and the hare was only a fairy tale.

Speed: I cannot get my hands on the exact figures now, but I have read that for every tenth of a second that Amazon manages to shave off their website's response time, sales increase by many millions of dollars. Google experienced a similar increase in the stickiness of their site when they introduced Google Instant. If response times are as crucial to your project, then perhaps you should be leaning towards lightweight Apex. But do not dismiss Forms out of hand; from 11g, it is possible to slash load times by pre-starting runtime engines. The tortoise may have bought itself some roller skates. 

Deployability: Our IT team are forever engaged in a battle with Oracle Application Server. One of them - OAS or the IT guys - isn't very good at their job. Since no one at Oracle has ever bought me a drink at the pub, I'll assume it's their fault. Apex is easier to deploy than Forms; indeed there is a growing number of companies that will host your Apex application for you.

The Big Picture:  You have to consider the wider context. In my case, the option of building Forms applications is a receding one, since my company has taken the decision to move towards Apex. We now have a cohort of new, young developers to who Forms is Betamax.

Conclusion: The polygamist makes his decision and quietly opens a door. He enters the room and closes the door behind him. Are you awake? he whispers into the darkness. There is a crack in the curtains, and a shaft of milky moonlight is lying across the bed like an abandoned sword. Yes, a voice whispers from beneath the covers. He walks towards the sound, his pace quickens with excitement.

There is no universally right or wrong answer; it depends on your project and the factors I have named above (and the many I have not thought of - which is why there is a comments section below).

PS: I'd like to thank Steve Cairns for his help with this post. He is one of the country's leading experts on polygamy. Or Oracle technologies. I forget which. 

Of Comments And Panties...

Code comments are like underwear: people can't see them, but they really should be there, and it's best if you keep them clean. And brief.

Oh, and it's never as funny as you think it is, if they contain references to Homer Simpson.

Here's an article I wrote for All Things Oracle about commenting in programming. (Un)fortunately, it contains no further references to underwear. I apologise.

How to make comments the most important ‘code’ you write

80% of all communication, it is often said, is nonverbal. The actual percentage is disputed – with experts quoting figures that range from 50% all the way up to 93% – but what is not in dispute is the fact that what is left unspoken is often as important as what is said.

An almost direct parallel can be drawn with comments in code. Annotations aimed at the programmer but ignored by the compiler can be as important as actual functional code and should be treated with a comparable level of seriousness.

Should be treated with seriousness; however, unfortunately, they almost never are. Most programmers will confess that their commenting is, at best, patchy and often non-existent. The excuses run from the threadbare to the clichéd; from “I do not have enough time to comment” to “Good code is self-explanatory”.

Fewer programmers will admit to this, but a lack of knowledge of the many uses – and the power  – of commenting is widespread.  That is what this article intends to address.
Continue reading at All Things Oracle