PLS-00364 – And you never buy me flowers !

Nestled deep in the heart of the Oracle RDBMS lies DIANA – the ADA pre-compiler which gives all your PL/SQL the once-over before sending it out into the world.
As I’ve mentioned before, DIANA can be a capricious girl, and if you upset her, she’s likely to complain about all sorts of things, some of them entirely spurious.
One such error that she tends to throw out is “PLS-00364 : loop index variable [ some cursor record variable ] use is invalid”.

There always seems to be an error preceding this one on the stack, unless you’ve done something particularly wacky with your cursor record variable. The way the variable is being used is perfectly legal. It just seems to be DIANA complaining “…and you never buy me flowers!”
I hasten to add (just in case she’s reading this), I’m not thinking of my girlfriend in this context. She never says this…or anything else in the least bit unreasonable. Ahem.
Let’s look at an example

set serveroutput on
DECLARE
    CURSOR c_silly IS
        SELECT first_name, last_name, wibble
        FROM hr.employees
        WHERE last_name LIKE 'T%';
BEGIN
    FOR r_silly IN c_silly LOOP
        DBMS_OUTPUT.PUT_LINE(r_silly.first_name||' '||r_silly.last_name);
    END LOOP;
END;
/

When we run this we get :

ERROR at line 3: 
ORA-06550: line 3, column 39: 
PL/SQL: ORA-00904: "WIBBLE": invalid identifier 
ORA-06550: line 3, column 9: 
PL/SQL: SQL Statement ignored 
ORA-06550: line 7, column 30: 
PLS-00364: loop index variable 'R_SILLY' use is invalid 
ORA-06550: line 7, column 9: 
PL/SQL: Statement ignored 

As you can see, the lady has a valid point – there is no column called wibble in the HR.EMPLOYEES table.
If we just address this particular issue and re-run the code…

set serveroutput on
DECLARE
    CURSOR c_silly IS
        SELECT first_name, last_name
        FROM hr.employees
        WHERE last_name LIKE 'T%';
BEGIN
    FOR r_silly IN c_silly LOOP
        DBMS_OUTPUT.PUT_LINE(r_silly.first_name||' '||r_silly.last_name);
    END LOOP;
END;
/

It seems that DIANA has forgotten all about her objections to the unsuitable cursor record :

Jonathon Taylor 
Winston Taylor 
Sigal Tobias 
Peter Tucker 
Oliver Tuvault 

PL/SQL procedure successfully completed. 

Conclusive proof, I think, that PLS-00364 is in fact an “Out of Flowers” error.
The point of all of this – always fix the first error first and re-run. You never know what else will be magically fixed. Oh, and you don’t have to go to the florists to let her know she’s special.

About these ads