Oracle packaged function does not work through SQL Server 2008 R2 linked server

Question

I have the following overloaded function in a oracle (10g) package.

function fnDaysFromNowToDate(dd_mon_yyyy date) return number is days number;
    --d2 varchar2(11):=to_char(sysdate,ddf);
Begin
   dbms_output.put_line( 'd='|| to_date(dd_mon_yyyy,'dd-mon-yyyy'));
   dbms_output.put_line( 's='|| to_date(sysdate,'dd-mon-yyyy'));
    return    trunc(dd_mon_yyyy-trunc(sysdate));

  --return 1;
  end;
  --- overload for varchar
  function fnDaysFromNowToDate(dd_mon_yyyy varchar2) return number is days number;
  Begin
   dbms_output.put_line( 'd='|| to_date(dd_mon_yyyy,'dd-mon-yyyy'));
   dbms_output.put_line( 's='|| to_date(sysdate,'dd-mon-yyyy'));
    return    trunc(to_date(dd_mon_yyyy,'dd-mon-yyyy')-trunc(sysdate));
  end;

And both of them work fine when executed within Oracle Sql Developer, like so

select t$stdt,to_char(t$tdat,'dd-Mon-YYYY') t$tdat, t$cuno,T$CPGS,T$QANP,T$DISC 
   from baan.ttdsls031020 
       where 
       trim(t$cuno)        =   '000811' 
      and pkgUtils.fnDaysFromNowToDate(to_char(t$tdat,'dd-Mon-YYYY')) >  1
        and t$qanp           =   pkgPriceWorx.fndefaultQanp 
      and trim(t$cpgs)  =   '1AM00';

but when I execute same query through SQL linked server using OraOLEDB.Oracle provider

select * from openquery(hades,"
select t$stdt,to_char(t$tdat,'dd-Mon-YYYY') t$tdat, t$cuno,T$CPGS,T$QANP,T$DISC 
   from baan.ttdsls031020 
       where 
       trim(t$cuno)        =   '000811' 
      and pkgUtils.fnDaysFromNowToDate(to_char(t$tdat,'dd-Mon-YYYY')) >  1
        and t$qanp           =   pkgPriceWorx.fndefaultQanp 
      and trim(t$cpgs)  =   '1AM00'
       ");

the following errors are thrown

OLE DB provider "OraOLEDB.Oracle" for linked server "hades" returned message
ORA-01861: literal does not match format string
ORA-06512: at "SAAP.PKGUTILS", line 29". OLE DB provider "OraOLEDB.Oracle" for linked server "hades" returned message "ORA-01861: literal does not match format string ORA-06512: at "SAAP.PKGUTILS", line 29". .Net SqlClient Data Provider: Msg 7320, Level 16, State 2, Line 3 Cannot execute the query " select t$stdt,to_char(t$tdat,'dd-Mon-YYYY') t$tdat, t$cuno,T$CPGS,T$QANP,T$DISC from baan.ttdsls031020 where trim(t$cuno) = '000811' and pkgUtils.fnDaysFromNowToDate(to_char(t$tdat,'dd-Mon-YYYY')) > 1 and t$qanp = pkgPriceWorx.fndefaultQanp and trim(t$cpgs) = '1AM00' " against OLE DB provider "OraOLEDB.Oracle" for linked server "hades".*

Any idea why this behaviour ?

Answer 1

I don't think that it is good practice to overload a plsql function which receives a Date, with one that recieves a varchar2, since oracle, many times, automatically cast a varchar2 to Date according to NLS_DATE_FORMAT which may vary between environments.

Anyway, you souldn't run "to_char(t$tdat,'dd-Mon-YYYY')" if t$tdat is varchar2 because then oracle will cast your varchar2 to a date first (because to_char gets a date as parameter) according to NLS_DATE_FORMAT.
Nor should you use to_date on dates (from the same reson)

If you want to use overloading for a case that you don't know if you get a varchar2 or a date you can do it like this:

function do_things(d date) return number is

begin

dbms_output.put_line('d=' || to_char(d, 'dd-mon-yyyy'));
dbms_output.put_line('s=' || to_char(sysdate, 'dd-mon-yyyy'));

return trunc(d - trunc(sysdate));

end do_things;


function fnDaysFromNowToDate(dd_mon_yyyy date) return number is
days number;
begin
--d2 varchar2(11):=to_char(sysdate,ddf);Begin dbms_output.put_line( 'd='|| to_date(dd_mon_yyyy,'dd-mon-yyyy'));

dbms_output.put_line('function gets date');
days := do_things(dd_mon_yyyy);
return days;

end fnDaysFromNowToDate;
--return 1; end; --- overload for varchar


function fnDaysFromNowToDate(dd_mon_yyyy varchar2) return number is
days number;
Begin
dbms_output.put_line('function gets varchar2');
days := do_things(to_date(dd_mon_yyyy, 'dd-mon-yyyy'));
return days;

end fnDaysFromNowToDate;