Wrong Compiler Hints

[MartinSchm]

Hi,

don't know if it is a Oracle or PL/SQL Developer error. If I compile a package I get these compiler hints. But some of them are completely wrong.

They all are from this kind.
- Hint: Parameter 'p_zeitraum' is declared but
never used in 'AbgrenzungAufloesen'

or

- Hint: Value assigned to 'l_prozent' never used
in 'rechneStaffelsatz'

Looking into this method the varibles are used.
If I delete the declaration of the variables I get compiler error "identifier xyz must be declared".

It's not a big problem but maybe you could have a look.

bye
Martin

 

[mike]

I've noticed this happens as well, so I always double check the hints.

If the variables/parameters are used in the exception section only this will happen.

If a variable is declared in a procedure and the only place it is used is within an anonymous or named block in the procedure, I think it happens as well if I'm remembering correctly - it's been awhile since I've seen it.

 

[ScottMattes]

Don't take this as gospel, but I think those hints are entirely from Oracle.

 

[Marco Kalter]

The hints are not from Oracle, they are generated by PL/SQL Developer.

Can I get an example from both MartinSchm and mike for the erroneous hints?

 

[mike]

I compile code in pl/sql developer to get the hints as part of my peer review of other people's code and I've seen it a few times before, but I don't remember which code it was so I guess I'm not being much help at the moment.

I can't reproduce it the way that I remember it happening so maybe it was in a prior version or I'm remembering how it happened wrong.

Anyway, next time I see it, I'll e-mail you the code so you can see what's happening.

 

[mike]

The problem showed up so I have a small example. The hint on line 13 says that variable v_variable is not used in the procedure MAIN.

Basically, if a variable is assigned a value in an exception of an anonymous block and it is used in another exception of a different anonymous block, the hint says it is not used and there is a possibility that it will be.

PROCEDURE MAIN IS

v_variable VARCHAR2(100);
v_counter NUMBER;
BEGIN
BEGIN
SELECT full_name
INTO v_variable
FROM per_people_f
WHERE rownum = 1;
EXCEPTION
WHEN OTHERS THEN
v_variable := 'Nobody';
END;

BEGIN
SELECT COUNT(*)
INTO v_counter
FROM per_people_f;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('v_variable: ' || v_variable);
END;

EXCEPTION

WHEN OTHERS THEN
ZFND_DEBUG.PUT_LINE('+---------------------------------------------------------------------------+');
ZFND_DEBUG.PUT_LINE('ERROR IN zper_test.MAIN');
ZFND_DEBUG.PUT_LINE('ERROR: ' || SQLCODE || ' ' || SQLERRM);

ZFND_DEBUG.PUT_LINE('+---------------------------------------------------------------------------+');
RAISE_APPLICATION_ERROR(-20001,'Error in zper_test.MAIN');

END MAIN;

 

[Marco Kalter]

Thanks, that is indeed not correct. We'll fix it.

 

[kromkar]

I have encountered a similar problem where a procedure contains a sub-procedure and the value is assigned in the outer procedure but only used in the sub-procedure. I get the error message, "value assigned to varName is never used in procName."

 

[Marco Kalter]

Can you send me a demo procedure to reproduce this?

 

[Ivan C.]

The same erroneous hint appears when you add a minus/plus sign in front of the variable name. Here's an example:

CREATE OR REPLACE
PROCEDURE a_test
          IS
   ln_n PLS_INTEGER := 3;
BEGIN
   IF ADD_MONTHS (SYSDATE, - ln_n) >= SYSDATE THEN
      dbms_output.put_line ('1');
   END IF;
END a_test;

I know you'll fix this too, so thank you in advance.

 

[Marco Kalter]

We'll fix it. One work around is to specify 0 - ln_n instead.