Oracle – for when it was like that when you got there

Main menu

Post navigation

Anchored Declarations and the Brownie Point Economy

This week’s hot conversational topic in the Nut and Squirrel was the Global Economic Crisis, with particular reference to a little-reported side-effect that has huge ramifications. I am, of course, referring to the devaluation of Brownie Points.

Unless you’re English, I guess some explanation may be called for at this point. So, at the risk of getting all anthropological…
Brownie Points are awarded by females to their mate for certain actions. When enough brownie points have been accrued, the male of the species can have these converted into a Pass.

The Pass can be used for a night out with the lads, at the footie, or whatever other pursuit is of interest.

This system is instinctively understood by females, although, due to the sudden and unexpected fluctuations in value, less so by the males.

You will often hear conversations such as :

“We’re going to Luton Saturday, their playing Mansfield, you reckon you can get a pass ?”
“Sorry, we’re at the Garden Centre on Saturday, need to earn some brownie points.”

This system goes under many different guises but is essentially the same the world over.
Lately however, it has become apparent that males are having to work harder for their brownie points than previously. The suspicion is that this is related to the retail price of women’s shoes.

What a mess. Where can you put your hard earned savings to ensure an index-linked return ?
Well, if you really want to know, you’ll have to read the Financial Times. Variables in PL/SQL, however, can be indexed-linked to database columns by the simple expedient of an anchored declaration.

Your Flexible Friend

Imagine we had to keep track of the brownie point values in Oracle. Let’s start with a table :

Notice anything amiss with the l_activity variable ? Well, it’s declared as a VARCHAR2(20) same as the column from which we’ll be selecting data into it. No problem so far.
But what happens if, to take a completely random example, your better-half decides to get involved. Yes, Deb decided that 20 characters just isn’t long-enough to describe the various activities for which points may be awarded. As a result we have to amend the table as follows :

ALTER TABLE brownie_points MODIFY activity VARCHAR2(30)
/

Oh, and our new Business Analyst has had some input on the data we need to hold in this table…

Not only do we have a procedure that will never be susceptible to changes to the columns on which it relies, we also have what is, essentially, self-documenting code.
The extra typing you have to do to define the parameters is offset by the reduction in the amount of typing you need to do for the comments.

A bit less typing

You can also use anchored declarations to save you typing. Let’s say you wanted to pull out all the stops…

Look at that. Instead of declaring two variables, we just need to declare one as %ROWTYPE for the table. Of course, we could go further. If we just want a cursor specifically to hold the values returned by the cursor, we can use the cursor itself for the basis of the variable declaration…

set serveroutput on
DECLARE
CURSOR c_activities IS
SELECT activity,
CASE
WHEN points < 0 THEN 'Not a good idea'
ELSE 'This could work'
END as advice
FROM brownie_points bp;
rec_activity c_activities%ROWTYPE;
BEGIN
OPEN c_activities;
FETCH c_activities INTO rec_activity;
WHILE c_activities%FOUND LOOP
DBMS_OUTPUT.PUT_LINE( rec_activity.activity||' - '||rec_activity.advice);
FETCH c_activities INTO rec_activity;
END LOOP;
CLOSE c_activities;
END;
/

Running this we get …

SQL> @advice.sql
Flowers ( from florist) - This could work
Flowers( delivered) - This could work
Flowers( delivered to work) - This could work
Dinner( take away) - This could work
Dinner( restaurant) - This could work
Leaving the loo seat up - Not a good idea
Late home from pub - Not a good idea
Flowers ( all-night garage) - This could work
PL/SQL procedure successfully completed.
SQL>

A word of warning here – if you do want to use this construct, then you need to make sure that you declare the variable after the cursor. Otherwise, you’ll get something like:

ERROR at line 2:
ORA-06550: line 2, column 18:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 2, column 18:
PL/SQL: Item ignored
ORA-06550: line 12, column 33:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 12, column 9:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 31:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 13, column 9:
PL/SQL: Statement ignored

Anchored declarations are incredibly useful. As well as future-proofing, they serve to make code more readable and can even save you typing in the long-run.

NOTE – Brownie points can go down as well as up. The author accepts no liability for the consequences of acting on any of the relationship advice contained in this blog.

2 thoughts on “Anchored Declarations and the Brownie Point Economy”

I can also confirm that the devaluation of brownie points has come about by the reduction in size of chocolate bars. This is linked to the increased demand for profit from these unscrupulous companies.
It’s hitting my pocket hard!