Question about Program Window Hint

[ScottMattes]

In a program unit in one of my packages I have four lines that say:

row_count := sql%rowcount;

after the third such line I have this:

if row_count = 0
then

after compiling this package I have one line in the message area that says:

2434 Hint: Value assigned to 'row_count' never used in 'proc_name'

Line number 2434 is the first occurance of

row_count := sql%rowcount;

Shouldn't there be three hints about the value of 'row_count' not being used? And should the message be more like "the value of THIS 'row_count' is never used"?

This is probably more of nitpicking, but it did make me stop and look through my code because I was sure that I had referenced this variable.

 

[Marco Kalter]

I tested this with the following procedure:

create or replace procedure HintTest is
  row_count integer;
begin
  row_count := sql%rowcount;
  row_count := sql%rowcount;
  row_count := sql%rowcount;
  if row_count = 0
  then
    null;
  end if;
end HintTest;

I get a "Hint: Value assigned to 'row_count' never used in 'HintTest'" for the first 2 assignments, which are indeed overruled by the 3rd assignment.

 

[ScottMattes]

Marco,
With your test procedure I also get the correct number of hints.

So, I went back to my code and copied the procedure from the package into a stored procedure, kept the structure but removed identifying names and I get only one hint. Please see below

CREATE OR REPLACE PROCEDURE my_proc(v_one IN VARCHAR2,
v_two IN VARCHAR2)
IS
row_count integer;

cursor c_one is
select 1
from dual;

one_rcd c_one%rowtype;

cursor c_two( l_one CHAR, l_two char ) is
select 2
from dual
WHERE l_one = l_one
AND l_two = l_two;

two_rcd c_two%rowtype;

cursor c_three is
select *
from dual;

three_rcd c_three%rowtype;

BEGIN

open c_one;

-- process each
loop
FETCH c_one
INTO one_rcd;
EXIT WHEN(c_one%NOTFOUND);

-- a comment
insert into t_one
fields(f1)
select 'asdf'
from dual;
row_count := sql%rowcount;

-- another comment
update t_one
set f1 = 0;
row_count := sql%rowcount;

-- yet again a comment
open c_two( v_one, v_two);

loop
FETCH c_two
INTO two_rcd;
EXIT WHEN(c_two%NOTFOUND);

update t_one
set f1 = 4;
row_count := sql%rowcount;

if row_count = 0 -- comment here too
then
insert into t_one
fields(f1
)
values(6);
end if;
end loop; -- comment

close c_two;

end loop; -- getting each

close c_one;

-- not the last comment
open c_three;

loop
FETCH c_three
INTO three_rcd;
EXIT WHEN(c_three%NOTFOUND);

-- update tbl
update t_one
set f1 = 9;
row_count := sql%rowcount;
end loop; -- another loop ended

close c_three;

END my_proc;
[/code]

 

[Marco Kalter]

This does indeed produce only one hint. We'll check it out.