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 ?

It can be either type varchar2 or Date, the overloading should kick in and determine the which one of the two to be called, in the case presented it is of char (varchar2) type — TonyP

A.B.Cade A.B.Cade · Accepted Answer · 2012-02-11T23:17:33

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;

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

2 Answers