Implicit Cursors are from Venus, Explicit Cursors are from Mars

Domestic bliss. There’s nothing like it. There’s certainly nothing like it in our house, particularly when I indulge in one of my endearing little foibles that is guaranteed to get Deb pouting like Angelina Jolie suffering a nasty reaction to a wasp-sting.
Whether it’s leaving the toilet seat up, or hanging my clothes up on the nearest floor, there are some days where I just can’t seem to do anything right.
Having said that, I must confess that I myself, am not a model of toleration. My own personal pout trigger is a query that looks something like this :

SELECT NVL(COUNT(*), 0)
FROM some_table;

I’ve seen this quite a bit recently, usually in the form of an explicit cursor.
Once I’ve got the rant about this out of my system, I’ll then look at how you might make single-row sub-queries a bit more efficient without ending up knee-deep in implicit cursors.
I’ll also ponder what it actually is that we really know about cursors. Continue reading

Advertisements

ORA-1810 and Other Dating Disasters

During a recent difference of opinion, my girlfriend said to me “Ooohhh, you’re so logical!” The atmosphere was not improved by the fact that she had to then explain to me that this wasn’t a compliment.
All of which has nothing to do with the purpose of this post (although it may help to solve the mystery as to why I was single for so long).

The other day, someone asked me over to have a look at why they were getting an error when running a particular query. Now, as quizzes seem to be en vogue in the Oracle world at the moment ( have a look at that nice Mr Feuerstein’s site ), here’s a quick one for you. See if you can spot what’s wrong with this query Continue reading