hint: Value assigned to ... never used

[Joe]

After compiling the package in PL/SQL Dev the returned hint is as follows:

Compilation errors for PACKAGE BODY TEST.PKG

Error: Hint: Value assigned to 'TS' never used in 'PROCESS_MODE'
Line: 212
Text: TS := 'COMPLETED';

The depth of the above assignment statement is 5, i.e. it resides with in 5 nested if statements. The variable is used throughout the procedure before and after line 212.

Any ideas as to why oracle would think it is not used?

cheers.
-Joe

 

[Marco Kalter]

It is not Oracle that reports this hint, this comes from PL/SQL Developer. Can you send me the source of this program unit?

 

[Joe]

Thanks Marco;
I have pasted a the code in question; the lines marked with ---HINT are the places where the hint warnings are raised. Do not compile this as it will not work as some external methods are called. For brevity I have removed all the excess irrelevant code and supplied an outline of the procedure.

PROCEDURE PROCESS_MODE is
  sbCount NUMBER;
  v_arRec A_R%ROWTYPE;
  v_authID NUMBER;
  TS VARCHAR2(100);
  LR VARCHAR2(100);

  tab1Cur GENERIC_CURSOR;
  cursorRes table1%ROWTYPE;
  BEGIN
    OPEN tab1Cur FOR 'SELECT * FROM table1';
      LOOP
        FETCH tab1Cur INTO cursorRes;
        EXIT WHEN tab1Cur%NOTFOUND;
        sbCount := IsComp(TS, LR);
        IF (sbCount > 0) THEN
          IF (AInDB (cursorRes.old_nr, cursorRes.old_sr)) THEN
            SELECT COUNT(*) INTO sbCount FROM A_R ar WHERE
              ar.old_nr = cursorRes.old_nr;
            IF (sbCount>0) THEN
              SELECT * INTO v_arRec FROM A_R ar WHERE
                ar.old_nr = cursorRes.old_nr;
              IF (v_arRec.Re_Date = cursorRes.date_tr) THEN
                TS := 'COMPLETED';	----HINT occurs here
                LR := 'Duplicate';  --HINT
              ELSE
                TS := 'COMPLETED';   --HINT
                LR := 'Date D';  --HINT
              END IF;
            ELSE
              TS := 'COMPLETED';
              LR := 'ReCompleted';
            END IF;
          ELSE
            TS := 'UNIDENTIFIEDASSET';
            LR := 'Asset Unidentified';
          END IF;
        END IF;
        UPDATE table1 SET trans_status = TS WHERE r_id = cursorRes.r_id;
        WriteLogBody (output_file, 'table1', cursorRes.r_id, TS, LR);
      END LOOP;
    CLOSE tab1Cur;
  END;

Cheers,
-Joe

 

[Marco Kalter]

This works okay for me. Perhaps you are using an older PL/SQL Developer version? I tested this on the current 6.0.4 release.

 

[Joe]

I am using PL/SQL Developer
Version 6.0.4.906 (MBCS)
Home: OraHome92
DLL: C:\oracle\ora92\bin\oci.dll
OCI: version 9.2
Oracle9i Enterprise Edition Release 9.2.0.1.0

I have used the TS and LR variables in other functions and they compile without any warnings, but they are not as deep as the one's in question here.

I have found something interesting. It appears that if I rename the variables where I have indicated PROBLEM in the code below as a comment, the code compiles without any warnings.

I hope this helps in the problem finding.

BEGIN
OPEN tab1Cur FOR 'SELECT * FROM table1';
LOOP
FETCH tab1Cur INTO cursorRes;
EXIT WHEN tab1Cur%NOTFOUND;
sbCount := IsComp(TS, LR);
IF (sbCount > 0) THEN
IF (AInDB (cursorRes.old_nr, cursorRes.old_sr)) THEN
SELECT COUNT(*) INTO sbCount FROM A_R ar WHERE
ar.old_nr = cursorRes.old_nr;
IF (sbCount>0) THEN
SELECT * INTO v_arRec FROM A_R ar WHERE
ar.old_nr = cursorRes.old_nr;
IF (v_arRec.Re_Date = cursorRes.date_tr) THEN
TS := 'COMPLETED'; ----HINT occurs here
LR := 'Duplicate'; --HINT
ELSE
TS := 'COMPLETED'; --HINT
LR := 'Date D'; --HINT
END IF;
ELSE
TS := 'COMPLETED';	--PROBLEM
LR := 'ReCompleted';  --PROBLEM
END IF;
ELSE
TS := 'UNIDENTIFIEDASSET';
LR := 'Asset Unidentified';
END IF;
END IF;
UPDATE table1 SET trans_status = TS WHERE r_id = cursorRes.r_id;
WriteLogBody (output_file, 'table1', cursorRes.r_id, TS, LR);
END LOOP;

-Joe

 

[Marco Kalter]

Can you send me the complete source file of the program unit that causes this problemby e-mail. I don't understand yet why I cannnot reproduce this.

 

[gulhaugen]

Here's some code that might clear things up:

procedure wrong_hint is
    v_check_value number;
    v_value_to_assign number;
  begin
    v_check_value := to_char(sysdate, 'iw');
    if v_check_value > 9 then
      v_value_to_assign := 1;
      if v_check_value = 20 then
        v_value_to_assign := 2;
      end if;
    else
      if true then
        null;
      end if;
      v_value_to_assign := 3;
    end if;
    dbms_output.put_line('test_' v_value_to_assign);
   end;

This should not produce a hint, but it does.

If you comment out

if true then
        null;
      end if;

No hint is given
PLSQLDev 6.0.4.906

 

[Marco Kalter]

This does indeed produce an incorrect hint. We'll fix it.