Oracle function and query return different results

Question

I am using oacle 10g database.

Function:

create or replace FUNCTION FUNC_FAAL(myCode number,firstDate date
  , secondDate date) 
  RETURN INTEGER as
  rtr integer;
BEGIN
  select count(*) into rtr 
  from my_table tbl where tbl.myDateColumn between firstDate and 
          secondDate and tbl.kkct is null and tbl.myNumberColumn  = myCode ;
  return (rtr);
END FUNC_FAAL;

This function returns the result of 117177.

But if I execute the same request in a function separately,

select count(*)
from my_table tbl 
where tbl.myDateColumn between firstDate and secondDate 
and tbl.kkct is null and tbl.myNumberColumn  = myCode ;

I get another result 11344 (which is correct).

What could be the problem?

Thank.

Answer 1

You obfuscated your code, and I suspect that the problem is hidden in the process. I suspect your code is more like

 create or replace FUNCTION FUNC_FAAL(myNumberColumn number,firstDate date
  , secondDate date) 
  RETURN INTEGER as
  rtr integer;
BEGIN
  select count(*) into rtr 
  from my_table tbl where tbl.myDateColumn between firstDate and 
          secondDate and tbl.kkct is null and tbl.myNumberColumn  = myNumberColumn ;
  return (rtr);
END FUNC_FAAL;

where the parameter or local variable has the same name as the column in the table. In SQL, a table column takes precedence, so the variable is not used, and the column is compared with itself, giving more matches.

(, v_ p_), .

Answer 2

, . . , . .

, (schema.table), .

Answer 3

I would run TKPROFto see what kind of SQL you are actually processing in the database, in particular, to find out how date variables are recognized.