Procedure query returns incorrect result

I have a stored procedure running a batch of sql queries. However, one of the queries always returns the wrong result for a single table.

The procedure is:

CREATE OR REPLACE PROCEDURE countUser ( name IN USERS.username%TYPE )

AS

rows_counted NUMBER := 0;

BEGIN

dbms_output.enable;

dbms_output.put_line('Counting values for user '||name);

SELECT count(*) AS count INTO rows_counted FROM files fi WHERE fi.username = name;
dbms_output.put_line(rows_counted||' files counted');

SELECT count(*) AS count INTO rows_counted FROM files fi WHERE fi.username = '771';
dbms_output.put_line(rows_counted||' files(2) counted');

SELECT count(*) AS count INTO rows_counted FROM userdevices ud where ud.username = name;
dbms_output.put_line(rows_counted||' user devices counted');

SELECT count(*) AS count INTO rows_counted FROM users u where u.username = name;
dbms_output.put_line(rows_counted||' users counted');

commit;

EXCEPTION

WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

END;


When I call this procedure, it prints out this result:

SQL> exec countUser('771');
Counting values for user 771
0 files counted
1 files(2) counted
1 user devices counted
1 users counted

PL/SQL procedure successfully completed.

The parameter 'name' works fine in the where clause of queries on the UserDevices table and Users table, but fails for the Files table.

Any ideas whats happening here?

1. What happens if you change the order?
2. I do not believe you need the "AS count" and, if you do, choose a name that is not a reserved word COUNT.

Using reserved words in the wrong context can be a source of hard to trace problems.
Clive

1. Order doesn't affect the output.
2. I changed the alias assigned to count and the behavior doesn't change: the output is still wrong.

Thanks for the reply. Any other thoughts?

what is the datatype and precision of the users.username column and files.username column?...you might wanna try

SELECT count(*) AS count INTO rows_counted FROM files fi WHERE trunc(fi.username) = name

The data type is varchar2. I tried

SELECT count(*) AS count INTO rows_counted FROM files fi WHERE trim(fi.username) = name

but with the same result.

Thanks for your reply. This has been a pretty annoying bug.