Oracle function and query return different results

function, oracle10g

Solution

You've obfuscated your code, and I suspect hidden the problem 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 the SQL, the table column takes precedence and so the variable isn't used and the column is compared to itself, giving a greater number of matches.

It is best to prefix variables and parameters (eg v_ and p_) to avoid such problems.

Problem

I am using oracle 10g database. Function is : ``` 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 117177 as result. But if I run same query in the function seperately ; ``` select count(*) from my_table tbl where tbl.myDateColumn between firstDate and secondDate and tbl.kkct is null and tbl.myNumberColumn = myCode ; ``` I get different result 11344 (which is the right one). What can be the problem ? Thanks.

Original source