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 :

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.