Useful books

An enormous spider appears. It's clearly coming for you. Or not. What do you do?

Staying calm, place a glass or similar containment device over the creature.

Now gently slide a card under the glass, taking care not to trap any legs. There should be eight after you have completed this manoeuvre.

Transfer to a suitable platform such as a book, and carry to a window or other suitable release location.

No Oracle experts were harmed in the making of this blog.

Reintroduce the creature into the wild, where it can begin a new life free to roam among its own kind. Or just wander back in of course, but spiders are not known for their homing instinct. Or are they? To be honest we haven't checked. This one said something about "Scotland" and "revenge".

Finally, always sterilise your equipment.

Meanwhile in Edinburgh, police were called to a flat in the Stockbridge area following reports of a violent assault...

Just, Why?

I'm currently debugging a procedure of the form illustrated below, lovingly recreated using the ubiquitous scott.emp table. I'm also taking suggestions on what might've been going through the head of the person who wrote it.

RENAME emp TO emp_table
/
CREATE OR REPLACE VIEW emp
AS
   SELECT *
     FROM emp_table
/
CREATE OR REPLACE TRIGGER mod_emp
   INSTEAD OF UPDATE
   ON emp
   FOR EACH ROW
DECLARE
   new_emp                       emp_table%ROWTYPE;
   old_emp                       emp_table%ROWTYPE;
BEGIN
   new_emp.empno       := :NEW.empno;
   new_emp.ename       := :NEW.ename;
   new_emp.job         := :NEW.job;
   new_emp.mgr         := :NEW.mgr;
   new_emp.hiredate    := :NEW.hiredate;
   new_emp.sal         := :NEW.sal;
   new_emp.comm        := :NEW.comm;
   new_emp.deptno      := :NEW.deptno;
   old_emp.empno       := :OLD.empno;
   old_emp.ename       := :OLD.ename;
   old_emp.job         := :OLD.job;
   old_emp.mgr         := :OLD.mgr;
   old_emp.hiredate    := :OLD.hiredate;
   old_emp.sal         := :OLD.sal;
   old_emp.comm        := :OLD.comm;
   old_emp.deptno      := :OLD.deptno;
   update_emp (old_emp, new_emp);
END;
/

CREATE OR REPLACE PROCEDURE update_emp (
   old_emp                             emp_table%ROWTYPE
  ,new_emp                             emp_table%ROWTYPE
)
IS
BEGIN
   IF old_emp.empno != new_emp.empno
   THEN
      RETURN;   --can't update primary key attribute
   END IF;

   IF     old_emp.empno IS NOT NULL
      AND new_emp.empno IS NULL
   THEN
      DELETE FROM emp_table
            WHERE empno = old_emp.empno;

      RETURN;
   END IF;

   IF    (    old_emp.ename IS NULL
          AND new_emp.ename IS NOT NULL)
      OR (    old_emp.ename IS NOT NULL
          AND new_emp.ename != old_emp.ename)
   THEN
      UPDATE emp_table
         SET ename = new_emp.ename
       WHERE empno = old_emp.empno;
   END IF;
   -- ..
   -- ..
   -- ..
   IF    (    old_emp.deptno IS NULL
          AND new_emp.deptno IS NOT NULL)
      OR (    old_emp.deptno IS NOT NULL
          AND new_emp.deptno != old_emp.deptno)
   THEN
      UPDATE emp_table
         SET deptno = new_emp.deptno
       WHERE empno = old_emp.empno;
   END IF;
END;
/

A Year of WTFs

Each year, the Oracle WTF organisation celebrates its anniversary by inviting a fellow Oracle professional to share an evening of fine Eastern European lager and disappointing bar snacks in a pub where you can't hear each other properly, and this year it was the turn of former newbie (but now of course senior expert) DBA Lisa Dobson.

Actually that's not quite true - I realised today it was just over a year ago that we started the blog, and the other evening some of us met up with Lisa who happened to be in town, and if I'd thought of this a bit earlier I could probably have pretended it was all planned. Anyway it was nice meeting Lisa, and we enjoyed the story about the colleague who accidentally deleted the entire production server, as luck would have it a week after the server room air conditioning took out the only other server by dripping a surprisingly large amount of water into it. That rather outdid my story about the day we turned up for work at a client's site and found nobody could log in, because as it happened someone had stolen the servers during the night. Or the other one, if I'd remembered to tell it, about the high street retail chain whose backup system involved a PC and a timer plug. (I shouldn't laugh - it actually worked quite well.) It was also reassuring to hear we are not the only ones who don't understand a single bloody word of those "Oracle" blogs about installing a Java framework in JDeveloper to implement service-oriented BPEL with a right-click and some XML. Umm, neat.

It seems we're also not the only ones to have been shocked and saddened by Doug Burns' recent senseless killing spree. This monster must be stopped. (...is probably what Doug said as he reached for his copy of "Expert One-On-One Oracle").

Anyway, one year, 63 posts, and some lessons learned:

1. Post one of Tom Kyte's pet hates like WHEN OTHERS THEN NULL (or WHEN OTHERS THEN DBMS_OUTPUT, which isn't much better quite frankly) for a mention on his blog and a surefire thousand hits. We need one about IM Speak now b/c im sure u will C loadsa hits ;-)
2. Post something involving security for a mention on Pete Finnigan's site. We also get a steady stream of visitors googling for "forgot system password +oracle", "how to get password of a user in oracle" and so on, and being directed to Umm, I Forgot My Password, part 2. (In future just put it on a Post-It under your keyboard like everyone else.)
3. Titles involving common Oracle error codes seem to do well. A lot of visitors come here from a Google search for SP2-0552 (guys, you have a variable with a colon in front of it somewhere). We need some posts about ORA-0600 or TNSNAMES.
4. A lot of people seem to be searching for an explanation of joins and end up at our Joins Explained, heaven help them, where a Mr Sanders Kaufman explains things like "Left Joins are joined on the left columns of two tables. Right Joins are joined on the right columns of two tables." I'm glad we got that cleared up.
5. Google Analytics Rock. So (while we're at it) do Statcounter, Feedburner and CoComment.

We were recently offered some sponsorship by price comparison website Shopzilla (or rather, they emailed me a week ago and I remembered it in the pub). The consensus among those present was that we should not accept it, although personally I think all that would change if we could get The Daily WTF's Beanbag Girl.

We were also kindly offered a spot on Pythian Group's Logfile Of The Vanities or whatever it's called (or rather, they emailed me a month ago and I seem to have deleted the message, sorry guys, meant to get back to you) but I didn't take them up on it, partly because there didn't seem much point in writing a piece telling people about Oracle blogs they already read (surely?) via blogs.oracle.com, and also because it's explicitly for DBAs, and with the possible exception of Padders we are all developers (even though these days "developer" is often assumed to mean someone who is into aspect-oriented JDeveloper BPEL plug-ins). Our main thing is SQL, PL/SQL and data modelling, and AFAIK none of those have much to do with the DBA role. In fact, thinking about it I'm not sure why they asked. And is that Pie-thian as in Pythagoras and Python, or Pith-ian as in Gryffindor and Slytherin, anyway?