Oracle function and query return different results

Question

Hi, 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.

Answer 1

the function could be in a schema which also has the table in question. it could be working with respect to that table. when you are running the query independently, you could be using a table in a different schema. this is one possibility.

if this is the case, specifying the table name as a fully qualified one (schema.table) should solve the problem.

Answer 2

I'd run TKPROF to see what SQL you are actually processing in the database, specifically to see how the date variables are being recognised.

Answer 3

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.