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 :
CREATE TABLE brownie_points( activity VARCHAR2(20), points NUMBER(3)) /
You may also have a bit of code knocking around somewhere to display all of the activities for which you can gain points :
set serveroutput on DECLARE l_activity VARCHAR2(20); CURSOR c_activities IS SELECT activity FROM brownie_points WHERE points > 0; BEGIN FOR r_activities IN c_activities LOOP -- -- Slightly contrived for the purposes of this example -- l_activity := r_activities.activity; DBMS_OUTPUT.PUT_LINE( l_activity); END LOOP; END; /
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…
INSERT INTO brownie_points( activity, points) VALUES( 'Flowers ( all-night garage)', 2); INSERT INTO brownie_points( activity, points) VALUES( 'Flowers ( from florist)', 15); INSERT INTO brownie_points( activity, points) VALUES( 'Flowers( delivered)', 50); INSERT INTO brownie_points( activity, points) VALUES( 'Flowers( delivered to work)', 250); INSERT INTO brownie_points( activity, points) VALUES( 'Dinner( take away)', 5); INSERT INTO brownie_points( activity, points) VALUES( 'Dinner( restaurant)', 25); INSERT INTO brownie_points( activity, points) VALUES( 'Leaving the loo seat up', -10); INSERT INTO brownie_points( activity, points) VALUES( 'Late home from pub', -15);
The upshot of all of this is that when we next run our code, we get a nasty surprise…
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 13
If only I hadn’t acted like a rogue trader and tried to shave a bit off the typing, this problem would never have occurred. If only I’d used an anchored declaration…
set serveroutput on DECLARE l_activity brownie_points.activity%TYPE; CURSOR c_activities IS SELECT activity FROM brownie_points WHERE points > 0; BEGIN FOR r_activities IN c_activities LOOP -- -- Slightly contrived for the purposes of this example -- l_activity := r_activities.activity; DBMS_OUTPUT.PUT_LINE( l_activity); END LOOP; END; /
Running this, we’d get :
Flowers ( all-night garage) Flowers ( from florist) Flowers( delivered) Flowers( delivered to work) Dinner( take away) Dinner( restaurant)
Stored Program Parameters
You can also use anchored declarations for parameters in Stored Program Units.
Say we had a procedure to add records to the table…
CREATE OR REPLACE PROCEDURE add_activity_pr ( i_activity brownie_points.activity%TYPE, i_points brownie_points.points%TYPE) IS BEGIN INSERT INTO brownie_points( activity, points) VALUES( i_activity, i_points); END; /
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…
set serveroutput on DECLARE rec_activity brownie_points%ROWTYPE; CURSOR c_activities IS SELECT activity, points FROM brownie_points bp WHERE points = (SELECT MAX(bp1.points) FROM brownie_points bp1); BEGIN OPEN c_activities; FETCH c_activities INTO rec_activity; CLOSE c_activities; DBMS_OUTPUT.PUT_LINE('If you want a Pass then '||rec_activity.activity||' is worth '||rec_activity.points||' points.'); END; /
Save this as all_stops.sql and …
SQL> @all_stops.sql If you want a pass then Flowers( delivered to work) is worth 250 points. PL/SQL procedure successfully completed. SQL>
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.
Excellent post on the subject.
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!
Well said !
I’m thinking of starting a petition. Who knows, if we get enough momentum, they might even do a Dispatches investigation into the whole murky business.