Review of Oracle APEX Best Practices (or the 200 day shower)

Over the past few days I have had two books on my bedside table: on my Google Nexus I have been reading David Eagleman’s novel Sum: FortyTales from the Afterlives, and whenever I put that down, I’ve been burying my nose in the dead-tree (i.e. non-ebook) version of Oracle APEX Best Practices by Learco Brizzi, Iloon Ellen-Wolff and Alex Nuijten. 

This article is about Oracle APEX Best Practices, of course, but let’s talk about the short story collection, Sum for a bit. One of the stories in the book hypothesises that in the afterlife we all relive our lives – with one difference: all our actions are grouped by an ‘order by’ statement. So instead of waking and sleeping, waking and sleeping, we spend 30 unbroken years asleep and then 40 continuous years awake. We spend 7 straight months making love, and then 6 full days clipping our toenails. We shower for 200 days. It’s a good book; read it.

Oracle APEX Best Practices is a good book too. I want to make that clear.  Two of the authors, Learco Brizzi and Iloon Ellen-Wolff are new names to me, but I have visited Alex Nuijten’s blog many times in the past and have never been let down. So I googled the other two authors, and what I read convinced me of their bona fides.

Not that the book doesn’t speak for itself. At 200-odd pages it is a good size; it is written in clear, precise English; and it covers Apex, from top to tail – from installation, through security and debugging, to deployment. It is mostly aimed at Apex beginners, but there is enough in-depth explanation in it to make it invaluable to intermediate Apex-ers too. It is a very good book.

But… 

In the afterlife in Sum, you spend 14 minutes experiencing pure joy, and then 27 long hours of intense pain. I have spent 2 full paragraphs praising Oracle APEX Best Practices; it is time to highlight my quibbles.

The first annoyance is this: to keep the page count down, the authors often ended sections with the url of some Oracle documentation or a blogpost containing more information. This is perfectly fine in an ebook, but what am I supposed to do with this in a paper book? I cannot type a 200-character url into my browser's address bar. Physical books cost more money than ebooks; they should not provide a worse experience.

My kingdom for a bit.ly!

I do also have some questions about the title. Best practices? This is a very good Apex book, no doubt, but I wouldn’t call it a best practices book. I expect best practices books to prescribe the way I develop my application and not just explain the options.

Finally, the authors occasionally struggle to stay true to their topic: Apex. In Chapter 2: Leveraging the Database, they spend 30 pages explaining how to use analytic and aggregate functions in Oracle databases. I was very grateful for the lesson – but what the hell is that doing an Apex book? What next - a fried rice recipe?

<\criticism >

Do not be put off by my quibbles; this is a good book. It was, unfortunately, released just on the eve of Apex 4.2 and therefore only covers up to 4.1; however, I do not think that this is a problem. Best practices are not version-dependent.

In Chapter 4, the authors discuss application security, advising on how to guard against SQL injections and URL tampering, and explaining Access Control Lists and dealing with password complexity rules using APEX_INSTANCE_ADMIN. And Chapter 5 is a 30 page essay on debugging and troubleshooting. Both these chapters are excellent and, along with the chapter on printing (BI Publisher, Apache FOP, Cocoon and JasperReports), are worth the cover price on their own (insofar as any software development book is worth its crazily-inflated price).

However, more than the long pieces on complex subjects, what I found useful in this book were the little reminders of things that I know and overlook: set up your User Interface Defaults before diving in and building your application, for example.

And even though I have said that the section on analytic functions, aggregate functions and index-organized tables should not be in an Apex book, I must concede that it is packed with useful (non-Apex) information.

According to Sum: Forty Tales from the Afterlives, we spend 1 whole year of our lives reading books. I do not regret the hours that I have spent on this one. 

(Full disclosure: I received a complimentary copy of this book from the publishers, Packt Publishing, in return for an unbiased review)

Authors & Awards: Usain Bolt with a pen

This post has absolutely nothing to do with Oracle, and even less to do with Apex. Sorry - you can stop reading here and I won't hold it against you.

However, I recently turned my attention to my other love, literature, and did a spot of datajournalism for another blog.  I'm reprinting my piece here.

I'll be back waltzing and wrestling with Oracle Apex very soon. Promise.

Africa's Leading Literary Powerhouses

Summer 2012 and the eyes of the world turn to the city of London, England. People across the globe sit in beer parlours, shebeens, pubs, washing unhealthy snacks down with tankards of beer and cheering as the healthiest specimens of our nations run, jump and swim faster, higher, stronger.

But why stop at sportsmen? Why not pitch our countries’ plumbers against the world’s, our street-corner hookers, our brain surgeons? Why don’t Liberians sneer at Sierra Leoneans: “The barefoot kids hawking peanuts in your Kroo Bay slums are nothing compared to the former child soldiers weaving through traffic selling groundnuts in the misery of our West Point”? And why not stand our writing ‘athletes’ up against each other in a sort of literary Olympic Games and see which nation ends up on the podium?

And the last was just what we did. The rules: We sourced our data by analysing the winners of major international literary prizes, filleting out all African winners and noting their country of origin. We limited our scope – and therefore our resultset – to awards for English language literature, with a deliberate bias towards prose fiction. Where a writer has dual nationality, as in the case of Zimbabwean-British author Doris Lessing, we favoured the African nation; with Mauritian-South African novelist, Lindsay Collen, we plumped for Mauritius, as this is the country she identifies with.

Continue reading...

Introduction to Oracle Flashback Technology (Part 3: Flashback Database)

In this series of articles, we are discussing the collection of tools that make up the Oracle Flashback Technology suite. They are varied in their purpose and in their implementation, but together they act very much like a morning-after pill for your database, a means by which developers and administrators can protect their database from immediate past errors and indiscretions. In the first part of this series, I talked about the flashback tools that every developer should have in his arsenal, and in the second we rummaged through the pile looking for things that might be of interest to administrators.  However, I have saved the biggie for last: Flashback Database.

What is Flashback Database?

 For those times when minor surgery is not enough, Flashback Database can apply a defibrillator to the heart of your database, shout “Clear!” and proceed to shock it back to a happier time. This is because Flashback Database is a mechanism that allows administrators rewind an entire database to a past time or SCN.  Flashback Database has most of the advantages of a point in time recovery, with  only a fraction of the hassle and longwindedness. Flashback Database, unlike some of the lesser members of the Flashback family, is a physical-level recovery mechanism; it uses its flashback logs to access past versions of data blocks, finessing the final result with information from the archived redo log where necessary.

Setting Up Flashback Database

Flashback Database must be enabled before you can use it.  This simple query will tell you whether it is or not:

SELECT FLASHBACK_ON FROM V$DATABASE;

The result will either be YES or NO.  If Flashback Database is not enabled and you wish to enable it, you will need to ensure that the database is in ARCHIVELOG mode first.

There are two other prerequisites for enabling flashback database.  You will need to instruct the database on where to store the flashback logs that it will generate and you will need to tell it how much space it can use up doing so. These are expressed in two instance parameters – DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE.  Remember to set the size before the location.

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 9G SCOPE=BOTH;

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘/oracle/flash_recovery_area’;

When Flashback is up and running, your control over the Flashback Recovery Area, which is what the location you specify in the DB_RECOVERY_FILE_DEST parameter is called, will be limited; the database will create and age out the flashback logs as necessary. One way in which you can attempt to manage the Flashback Recovery Area is to specify a retention target, telling the database how long you would like it to retain its logs before they are overwritten by newer ones. Whatever retention target you specify, however, is not set in stone; in practice, the length of time that logs are retained will depend on the intersection of the retention target and the space made available by the DB_RECOVERY_FILE_DEST_SIZE parameter. If the database runs out of space, it will kill off old logs, irrespective of the retention target.

The retention target has a default of 1 day and is set in minutes, thusly:

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 2880; /* 2 Days */

This done, you are now ready to flick the switch on Flashback Database. To do so you will need to shut down cleanly and mount the database, and while in this mode run the following:

ALTER DATABASE FLASHBACK ON;

Continue reading...

Introduction to Oracle Flashback Technology (Part 2: Database Administrators)

In the first in this series of articles on the tools in the Oracle Flashback Technology armoury, I discussed what is available to application developers, the various means by which they can rewind the clock and correct recently-past data errors.  However, if this was a world in which the worst thing that could happen was a small mix-up with data, what a happy world it would be. However, this is not Disneyland, and data errors are not the big, bad wolf at the door; it is errors that raze whole database objects that database administrators fear. But fortunately, Oracle Flashback can come to our rescue in those situations too.

First a quick recap: Oracle Flashback Technology is actually a clutch of different Oracle tools that enable developers and administrators reach into the immediate past of their database to recover from errors, without having to resort to point-in-time recovery options. For this reason, they are relatively fast and incur a relatively low logistical overhead (since the database does not need to suffer any downtime to implement them).

FLASHBACK TABLE:

Flashback table enables you revert a table to its state at a specified timestamp or SCN. Any rows of data created after the parameterized timestamp disappear and any deleted rows zombie back to existence; in addition, associated attributes such as triggers, indexes and constraints remain unviolated. And all this magic occurs without even the slightest blip of interference affecting the rest of the database; it stays up and available.

Flashback table is able to sidestep the unpleasantness of restoring from backups by using information in the undo tablespace to restore the table.

Imagine this scenario: some catastrophe has befallen the EMP table and the developers are unable to unpick the data. All eyes turn to you: Save us, they beseech, wave your wand and erase the past hour. This is where you would find Flashback Table useful:

FLASHBACK TABLE EMP TO SYSTIMESTAMP – INTERVAL '60' MINUTE;

And it’s done. Day saved. Simple.

To use flashback table, however, there are a few prerequisites that need to be in place:

• You must have the FLASHBACK privilege on the table. Or you must have the FLASHBACK ANY TABLE system privilege.
• You must have the SELECT, INSERT, DELETE and ALTER privileges on the table.
• Row movement must be enabled on the table.
• Finally - and crucially - the information in the undo tablespace must extend back far enough to cover your timestamp or SCN.

Continue reading...

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;
/