Want a free Oracle textbook? It's yours (for a price)

I have neglected this blog. I apologise.

I have an excuse, though. Two.  Firstly, I have been keeping busy contributing over at the All Things Oracle site. It's nice over there; you should check it out - but not just now.  Secondly, the projects I have been working on, of recent, have not involved a lot of interesting Apex, and so I have suffered for topics to write on for this blog.

However, I recently produced a series of articles for ATO called Oracle for Absolute Beginners.  It was received enthusiastically - so much so that the ATO guys and I discussed making it available for purchase on Amazon. Which I have done.

But you? I like you. And so I am making the book available for free here. Completely free. And there isn't a catch.

Okay, you're right, there is a catch. I would appreciate it if you would go to Amazon after reading it and leave an honest review. Please bear in mind as you do so, that if you're already an Oracle expert, the book is not necessarily aimed at you - so don't hold the fact that it doesn't contain a chapter on Oracle exadata or 12c cloud computing against it.

Got that? Thanks. Now download the book and then leave an Amazon review.

Oracle for Absolute Beginners: Part 4 - Multi-table queries

Read: Part 1, Part 2, Part 3

A wise man* once said: To build the Great Wall of China, you must start with a brick. In our previous articles we acquainted ourselves with our bricks; now it’s time to build. If we consider what we’ve learned so far – Select, Update, Insert, Delete – as unicellular organisms, what we’re about to do next is multicellular, big and beautiful – it’s like going from an amoeba to a bee, a butterfly, to Beyoncé.

[*that wise man was me]

Consider a real-world requirement that we might have of our Addressbook database; since it contains a list of our friends and their phone numbers, we will naturally want to see a list of their names and their phone numbers. Ah, but that presents a problem. Our friends’ names are in the FRIEND_NAME table, while their phone numbers are in the PHONE_NUMBER table. And complicating things further, we can only tell which number belongs to which friend by looking in the FRIEND_PHONE table. Aaargh!

JOINS

We could, of course, get the information by running a series of queries: one select to find our friends’ names and their friend_id; a second to find the phone_id of the phone number linked to each friend in FRIEND_PHONE; and a third query to find the number from PHONE_NUMBER using the phone_id we identified in our second query. So yes, it can be done. But hey, you can probably ride a unicycle across Siberia – but that didn’t stop them from inventing the car.

What we need are joins. We need a select statement that can query multiple tables at the same time.

The syntax for a multi-table select statement is as follows:

Continue reading...

Oracle for Absolute Beginners: Part 3 - Update, Insert, Delete

Read: Part 1, Part 2

A wise man* once said: So no-one told you life was gonna be this way. Your job’s a joke, you’re broke, your love life’s DOA. It’s like you’re always stuck in second gear. And when it hasn’t been your day, you week, your month, or even your year.

[*That wise man wasn’t me; but stick with me, I promise to bring this back around to Oracle SQL imminently. ]

So – since it hasn’t been your day, your week, your month, or even your year – you decide to go to Central Perk with your friends Ross, Rachel, Monica, Phoebe, Chandler and Joey.  Oh, and that new friend you made that’s also named Ross Geller.  The music is loud, the atmosphere is great, and a good time is had by everyone.

You wake up the next morning with a bit of a sore head and try to recall everything that went on the night before: you’d discovered that Chandler’s middle name is Muriel, and that Ross’ is Eustace. Rachel gave you her new phone number. Phoebe gave you her new address. Oh, and you had a big fight with new Ross and decided you no longer want to be friends with him.

You roll out of bed and groggily wipe your eyes: all this new information needs recording; you’d better fire up your database.

UPDATE

Since we thoroughly looked into SELECT statements in the previous part, we can now turn our attention to UPDATE statements. In SQL we use update statements to change existing records – not to create new records or to delete them – just to change them.

The syntax for update statements is as follows:

Continue reading...

Oracle for Absolute Beginners: Part 2 - SQL

Read Part 1

A wise man* once said, no one’s ever learned how to cook just by reading recipes. And so, since we painted in the background in Part 1, we are now going to roll up our sleeves and dive in. By the end of this article you will be reading and writing SQL, the lingua franca of databases.

[* that wise man was me.]

SQL

SQL stands for Structured Query Language (pronounced ess-cue-ell or sequel) and is the programming language used in the management of relational databases. And not just Oracle RDBMS; the code we are about to learn will work just as well with Microsoft’s SQL Server, IBM’s Informix, MySQL and dozens of others. SQL is very much the English of the database world; it is spoken in many environments.  This is one reason why the skills you are about to learn are very valuable; they are eminently transferrable.

SQL consists of a data definition language (DDL) and data manipulation language (DML).  What this means is that we use SQL not only to define the tables into which we plan to put our data, but to manipulate (query, edit, delete, stuff like that) the data once it’s in place.

Manipulating data using SQL is easy, as the syntax isn’t a million miles from the way we speak.  For instance, to select all the data from a table you would use the SELECT … FROM table_name command.  If, on the other hand, you wanted to update data, you’d use the UPDATE command; and the DELETE and INSERT commands pretty much do what you’d expect them to, too.

It’s easy.  Let me show you.

Continue reading...

Oracle for Absolute Beginners: Part 1 - Databases

A wise man* once said, an expert is someone who uses big words and acronyms where simple phrases would do just as nicely. So stand back and listen to this: Database, Relational Database, DBMS, RDBMS, SQL, Sub-queries, normalisation.

[* that wise man was me.]

So now that I’ve established my credentials by bamboozling you with arcane words and capital letters, let me tell you what the purpose of this series of articles is. By the end of it, you will be able to re-read that first paragraph and understand every word; or, if you would prefer that in more practical terms, you will be able to read – and write –  SQL, which is the programming language of databases.

Definitions

Let’s meet the main characters of our story: I’ll give you a couple of definitions; one building on the other.

A Database is an organised collection of data. Not yet sure what that means? Well, do you own an address book, either on your phone or in a physical book? That’s a database. After all, the addresses and phone numbers areorganised – with all friends whose names start with A being grouped separately from people whose names start with B or C or D.

A Relational Database is a database in which the data is organised according to type with the relationships being maintained between the differing types. Okay, that sounds a bit like Greek (or Dutch, if you’re Greek; or German, if you’re Dutch; or Xhosa if you’re German…), but it makes sense if you let me explain.

Dig out your address book again. Imagine all the names grouped together; and all the phone numbers grouped together in another list; and all the addresses in a third. On their own these individual lists might be interesting but not useful; but if we establish the relationship between the lists – this address is where that person lives and that’s their phone number – then our database takes shape.

Make sense? Don’t worry about it too much if it doesn’t; we’ll come back to it a little later. Let’s talk about Oracle now.

Continue reading...

Introduction to Instrumentation

The forest is thick with menace. The sunlight barely breaks through the leaves and is sludgy like curdled milk; the shadows of the branches look like gnarly fingers reaching to grasp at the pair of frightened small children.

As they wind down the path, Hansel leaves a trail of pebbles. “It will help us find our way home,” he says to Gretel, his voice quavering with fear.

There is a parallel somewhere in there with the world of Oracle, believe me. I know that you’re unlikely to be eaten by an ugly, wicked witch while writing PL/SQL (I can’t give you the same guarantees with Sql Server), but think of Hansel’s pebbles as debug messages, think of it as instrumentation.

Instrumentation is the practice of measuring the progress of your application’s performance as it runs, and is usually pre-baked into the software at the time the code is being written.  It is, of course, not a uniquely Oracle concept, but Oracle is all I know and all I will talk about in this article. So, if you got here by googling “Hansel and Gretel”, you may wish to leave now. 

Do You Need Instrumentation?

If you’ve ever asked:
• Why is my application suddenly performing so poorly?
• Which of my procedures needs tuning?
• Where exactly is my application crashing?
• What exactly is my code doing?

Or if you’ve ever said:

• Aaaaargh!

Then you probably do need instrumentation.

What Is Instrumentation?

Instrumentation, very simply put, is the practice of including your debugging into your code as the code is being written, and not after the fact, after the crash/issue/problem has happened. This way, when you need to understand what your code is doing, you can simply run your application and examine your logs.

What To Instrument?

No one understands your code as well as you do (I hope!) and so no one can supply you a full list of what information you want to capture in your instrumentation.  However, the following are some practices that are generally accepted as wise.

• Include instrumentation at the entry point of every procedure and function that you write.  The entry point, obviously, would be the first line after the BEGIN.

• Include instrumentation at the exit point of every procedure and every function that you write. The exit point of your procedures is right before your exception handling . Secondly,  I’m not your mother and so I don’t want to nag you, but I do hope that your functions generally have only one exit point – one RETURN – and are not littered with RETURNs within conditional statements and other digressions.  However, you should include instrumentation right before every exit point.

• If your routine accepts any parameters, you should include instrumentation outputting the values passed to each of these parameters.

• You will want instrumentation within your exception handling. You will definitely want to know if your code has raised an exception, and you will want to know what it was. Some Oracle experts argue that you should never have WHEN OTHERS THEN exception handling in your code. However, it may be a good idea to include it, instrument the exception, and then re-raise it.

• Instrument any significant points within the body of your code.  Got a conditional statement? Then instrument the various paths within it. Writing a cursor? Then instrument any parameters it takes and any output it spits out.

• You may also want to include a debug message logging the SQL%ROWCOUNT after significant DML.

Continue reading...

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

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

All Things Oracle (or LeBron James's Explain Plan)

I don't know if you follow basketball, but if you do - and, perhaps, even if you don't - you will know that when, in 2010, the best basketball player in the world, LeBron James, decided to leave the Cleveland Cavaliers he organised a one-hour television show to announce which team he had chosen to join. The Decision, as the 'extravaganza' was called was broadcast on ESPN and got 10 million viewers.

I was not one of them. But I'm going to assume that if 10 million people - more than the populations of Finland and Norway put together - watched it, it must have featured gorgeous cheerleaders, dressed in a blur of motion and very little else, dancing around, joyfully pumping the air with their pompoms as LeBron explained his plan to join a new team.

No? No cheerleaders? Not even one? Well, in that case it's really lucky you're here, cos I've got a dozen cheerleaders whooping and dancing all around my tiny flat as I prepare to make this announcement.

Hey, take your eyes off them! Focus on me.

I have decided to join the All Things Oracle team. [Insert fanfare here.]

This does not mean that I will be abandoning this blog. Quite the contrary, I am hoping that contributing to All Things Oracle will exercise my blog muscles and get me into the habit of blogging here more regularly. My plan is that I will continue to blog about my trials and triumphs with Apex here, while writing about the other facets of Oracle that I am interested in - Oracle Forms and the database in general - over at ATO. (I have already contributed pieces on regular expressions and autonomous transactions.)

ATO is brought to you by the guys at Red Gate software, and their ambition is to turn it into a hub for Oracle developers and DBAs by publishing original content and by pointing readers in the direction of useful information elsewhere. To achieve this they have assembled a formidable team of experts: I've studied that list and I cannot see one weak link ... oh, there I am! The fields covered include database tuning, Apex, system architecture, Forms, database administration, warehousing and much more.

So make sure you add the ATO rss feed to your reader and that you click on their links whenever you google stuff on bing or bing stuff on google.

Now please go back to watching the cheerleaders. And enjoy the champagne - it, er, comes from France.

Apex & Version Control (Or Dance like Google's watching)

Sit back, young one, and listen. You may learn something.

Just the other weekend I went to a party, one of those dance parties with loud, fast music, free-flowing alcohol and the type of beautiful women who wear short skirts, irrespective of the temperature outside. Y'know, just the average weekend for your typical Oracle Application Express blogger.

The thing to do, when you find yourself in this situation, is sit back, have a sip of alcohol to soothe your nerves, and survey the scene. Step back into the shadows of evolution: it is 2000BC again and you are a hunter on the plains of the Serengeti overlooking your prey. Choose your quarry and then step onto the dancefloor, making gentle rhythmic moves. Circle her (or him) gradually and then move in, bringing your arms and your head into your dance. Whisper to them, "May I have this dance?"

Guaranteed success, young one. Or your money back.

Real life is nothing like Hollywood. When you're dancing with(/against) a female in real life you don't have a troupe of other dancers dancing behind you in formation, matching your every move perfectly, like the dancers in Thriller, Step Up 3D or any of these movies. Real life does not naturally come with back up.

Which is why, of course, we need to back up our Apex applications ourselves. The question you will have to answer is this: should you back up your full application in one file or back it up page by page?

If the application you are building is a website that you publish to the internet then in many ways your challenge is an easy one. You will need a development environment, perhaps a test environment and a production environment. There's no reason for one environment to pollute any of the others.

Export your full application after each major code change and back it up wholly. Backing up apex applications page by page is complicated; there's no need to take unnecessary risks.

However if, like us, you are creating a huge system which spans multiple apex applications and hundreds of pages which you intend to sell to multiple customers who have divergent upgrade schedules and obliquely differing requirements, then the picture is slightly more complicated. Your application may never be in a state where you can export it fully. You will need to export each page and each shared component as each bit of development is completed.

Application Express's versioning capabilities are simplistic at best; you can version the application, but not the page. To keep a record of the various versions of your pages you will need to use the page comments.

If you choose to export your pages individually you will need to bear in mind that exporting the page does not export any shared components that you may use on that page. You'll need to do that separately. And, unfortunately, there's no built-in way to export application items. As usual, Apex forces you to dance a merry dance to achieve what you need.

Talking about dancing, you'll want to forget that "dance like no one's watching" nonsense. You don't code like no one's going to use your software, do you? This is 2011, my friend; and Youtube has a very long memory.

Oh, and if you've already built your application and need help exporting your individual pages the following procedure should help. You'll need to run it in SQL*Plus and have set up a directory.

/*
** This procedure will write out an apex page.
*/
create or replace procedure export_apex_pages( p_dir  in varchar2, app_id number )
is
l_thePage       htp.htbuf_arr;
l_output        utl_file.file_type;
l_lines         number default 999999999;
vWorkspace  number;
vLength    number := 0;
vFileName  varchar2(100);
begin

-- Find out what the workspace is.
for i in (select workspace_id
  from apex_applications
  where application_id = app_id) loop
 
  vWorkspace := i.workspace_id;
end loop;

-- Loop through all the pages for the application.
for j in (select  page_id
   from apex_application_pages
   where application_id = app_id ) loop

  -- Determine the file name.
  vFileName  :=  'f'||app_id||'_page_'||j.page_id||'.sql';
  vLength    := 0;

  OWA.num_cgi_vars    := 0;
  apex_util.export_application_page(p_application_id=>app_id,p_workspace_id=>vWorkspace, p_page_id=>j.page_id);

  l_output         := utl_file.fopen( p_dir, vFileName, 'w' );
  l_lines         := 99999999;
  owa.get_page( l_thePage, l_lines );

  for i in 1 .. l_lines loop

   utl_file.put( l_output, l_thePage(i) );
 
   /*
   ** We need to calculate the length cos we can only write up to 32k.
   ** If it starts getting close to that we need to close the file, and then reopen it.
   */
   vLength := vLength + length(l_thePage(i));
   if vLength > 30000 then -- it's getting close to our limit.  Look for a point where we can close it.
    if l_thePage(i) like '%'||chr(10) then -- the line ends with a page return. Perfect place to close and reopen the file.
     vLength := 0;
     utl_file.fclose(l_output);
     l_output := utl_file.fopen(p_dir,vFileName,'A');
    end if;
   end if;
  end loop;

  utl_file.fclose( l_output ); 
end loop;
end export_apex_pages;
/

Learning Oracle Application Express (or teaching Angelina Jolie the MC Hammer dance)

I'm a Londoner so, unsurprisingly, I think London is the greatest city in the world (feel free to disagree if you enjoy being wrong). However I do accept that London isn't always that friendly to foreigners. Other major European cities have street signs and directions in the local language and English, but London seems to say to you, "Speak English - or get lost. Literally."

The Apex blogosphere can be a little like that; we often blog about advanced minutiae, perhaps to the exclusion of absolute beginners. This week 2 of my colleagues started learning Apex and so I've decided to put together a little Apex 101 study guide for their benefit and for that of other Apex padawans. (And if you say that this is only tangentially different from a post I wrote 2 years ago my answer is that all blog posts are kinda related; it's like that 6 degrees of separation theory that says, as far as I understand it, that I and Angelina Jolie are practically best friends.)

Where to start: There is only one place to start, and that's at the Oracle Apex website. Sign up for a workspace - it's free and easy. Have a look round, press buttons to see what they do: it's like developing with a condom on - safe, without commitment, and, hey, you can even do it drunk. However, the very best way to really dip your toes into the water is to work through Oracle's 2 Day Developers Guide [PDF link here]. When you're done with that you may wish to return here to flirt with the other tutorials.

Internet resources: Did you know that you're only ever 6 clicks away from an Apex blogger? Fact. We're like LOLCATS, adverts for blue pills for men, and YouTube videos of Rick Astley. Fortunately, the Apex Blog Aggregator has found the best blogs about Apex and, erm, aggregated them. Alternatively, you can go here or even here.

Another internet resource that you'll probably want to bookmark is OTN (which either stands for a. Oracle Technology Network or b. Orangutans Tangoing in the Netherlands. You decide.) The forum's search functionality is more Altavista than Google, but chances are that any question you've got has probably been asked and answered before.

Tutorials: The same way that with each regeneration since the first series in 1963, Dr Who has come back as someone slightly younger and better looking, each regeneration of Apex has resulted in a slicker, more intuitive IDE. However, I attended the Oracle University's 5 day Apex course and found it very useful. You may also want to have a look at SkillBuilders. I've attended some of their webinars and they've been excellent. Hey, why don't you put on a pair of geeky glasses, download some of their past webinars and pretend you're me? Whatever pedals your bike, pal.

Books: If you are the type who prefers to learn from books there is a growing catalogue of Apex books out there. Here's a list. And here are two reviews by a blogger who I respect immensely - and who Angelina Jolie is practically best friends with.

And that's it, my friend, all you need to get started. I'm not going to claim that this blog post has changed your life or that you came to me as a boy/girl and are leaving as a man/woman (unless it's taken you 20 years to read these 600 words), but hopefully you now got all the resources you need to hack your way through the Apex jungle. There's a lot more I can show you (have you seen my MC Hammer dance? No?) but my doorbell is ringing. It's probably Angelina.