Code-analysis: Detect unused cursors

Toad for Oracle - Beta
1

Not sure if the eixsing rule for unused variables should be extended or if this is a new rule, but it would be great if unused cursors could be detected too. Is this possible?

CREATE PACKAGE BODY test_package
AS
__ CURSOR cursor_unused_global IS SELECT * FROM dual;__
variable_unused_global PLS_INTEGER;

PROCEDURE test_proc
AS
__ CURSOR cursor_unused_local IS SELECT * FROM dual;__
variable_unused_local PLS_INTEGER;
BEGIN
dbms_output.put_line( ‘TEST’ );
END test_proc;
END test_package;

2

Peter,

This rule 6405 is queued for rewrite as an XPath expression. (QP-158) It will process unused cursors as well.

Related to this, how about this snippet:

  DECLARE
e_ins_priv	EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ins_priv, -20999);
BEGIN
NULL;
END;

Is e_ins_priv used (by the PRAGMA), or is it unused?

What’s your vote (and those of other readers?)

Thanks,
Andre

3

From a technical standpoint, it is used because it is a parameter in the EXCEPTION_INIT call and because of that, it get sent down into the Oracle washing machine to get populated. From a practical standpoint, although it’s initialized, it’s never “used”.

All that being said, I say it’s “unused” in this scenario although there is a definite possibility of a firm maybe that I could be wrong one way or the other depending on the time of the day and the phase of the moon.

4

I agree with Gregory, this would be unused for me. It’s more or less the same as initializing a constant and then never using it.

I like the way Eclipse handles this with Java:

int xxx = 1;
xxx = 2;
xxx = Integer.valueOf(3);

This would still be marked with: The value of the local variable xxx is not used.

The problem with PL/SQL is, that you cannot ignore the result of a function or an out-parameter, so the third line would actually make the variable used in PL/SQL.

In the following code, the value of xxx is still unused though:

DECLARE
xxx PLS_INTEGER;
BEGIN
xxx := 1;
END;

5

Hi all,

Could anyone tell me what is the circumstance of declared and unused cursor except it is like i variable case, lack of code writing.

Thx

6

I’d say the most likely reason for an unused cursor (and variable) is removing the code that used it without removing the declaration.

7

There’s also the idea of “carpet bombing” the code with a bunch of variables you think you might need at the start instead of creating variables you need as you need them. It’s an anti-pattern of development but I’m surely not the only person who has seen this done. It’s one of the advantages of having the “blue squiggles” in Toad…you can easily see these kinds of issues when you get the privilege of inheriting someone else’s code (I KNOW that nobody posting here would ever write code that is anything but stellar).

Also, a big shout out to everyone who is helping us try to make these rules better. We tried to build a mechanism that would help developers write the best code possible. With your help, we can continue to make the rules better and more accurate so thank you!

8

The next (or next next) beta will flag unused cursors.

Thanks,
Andre