Month: August 2008

I’m talking about Apex…

at the AUSOUG conference this year. First time for me, so please be gentle :)
The draft programme is out now.

Looks like a great line up again this year, with a mix of local and international speakers. Some highlights that jumped out at me include:
Timothy Hall – 11g New Features for PL/SQL developers
Chris Muir – Simple database web services without an application server
Penny Cookson – How Ugly is that Apex Report?
… and that’s just the first day.

There’s also a mysterious session on the second day entitled “What do you want from your local User Group?” on the second day. Well, it looks mysterious because of the green tinge.

“This Procedure Never Raises Exceptions”

It’s a really bad thing to do, but that’s ok because “we put comments in that say it’s bad”.

PROCEDURE insert_stats IS
  PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
  INSERT INTO stats_table ...
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    NULL; --yeah this is a bad thing to do, bla bla bla...
END insert_stats;

The idea is that we want to gather some stats about user behaviour, but we are not allowed to interrupt the user’s important work with any unexpected error that might be raised due to the gathering of those stats.

This post is not about why that’s a bad thing – others have made very good points about this practice elsewhere.

What I want to write about is the exception handler here – does it really protect the caller from exceptions raised by this procedure? The answer is, no. Why?

SQL> CREATE PROCEDURE test_handler AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  n NUMBER;
BEGIN
  INSERT INTO stats_table (id) VALUES (0);
  dbms_output.put_line('inserted=' || SQL%ROWCOUNT);
  n := 1 / 0; -- fail...
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('handled: ' || SUBSTR(SQLERRM,1,4000));
    --"silly thing to do but at least we're safe, right?"
END test_handler;
/

Procedure created.

SQL> BEGIN test_handler; END;
/
inserted=1
handled: ORA-01476: divisor is equal to zero
BEGIN test_handler; END;
*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back

The divide-by-zero is just there to simulate an exception that is raised after the insert succeeds, but before (or during) the COMMIT. As soon as the transaction is started, the procedure must raise ORA-06519 unless a COMMIT or ROLLBACK succeeds. Lesson to learn? An autonomous transaction will raise ORA-06519 to the caller, and it will not be caught by “WHEN OTHERS”. To get around this we could put a ROLLBACK in the exception handler.