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.