CODEWITHSUNDEEP
oracle-databaseplsqlsql-server-2000linked-server
TonyP
TonyP

Reputation: 5873

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

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 ?

Upvotes: 1

Views: 2362

Answers (2)

user671612
user671612

Reputation: 11

In my case I needed to use trunc(sysdate).

I needed to invoke an oracle function from SQL Server 2005 Linked server. I had created the linked server and I can query it but in the moment to invoke the oracle funcion I got different errors like OLE DB provider "OraOLEDB.Oracle" for linked server "BAN23" returned message "ORA-01841: (full) year must be between -4713 and +9999, and not be 0".

OLE DB provider "OraOLEDB.Oracle" for linked server "BAN23" returned message "ORA-01861: literal does not match format string".

I tried different kinds of queries like elect * from openquery(BAN23,'SELECT contab.fu_icaro_tasas (''current_date'',''USD'',''COP'') FROM dual')

select * from openquery(BAN23,'SELECT contab.fu_icaro_tasas (''sysdate'',''USD'',''COP'') FROM dual')

select * from openquery(BAN23,'SELECT contab.fu_icaro_tasas (to_date(current_date, ''DD-MM-YYYY''),''USD'',''COP'') FROM dual') select * from openquery(BAN23,'SELECT contab.fu_icaro_tasas (to_date(sysdate, ''DD-MM-YYYY''),''USD'',''COP'') FROM dual')

My solution was to add trunc(sysdate) or trunc(current_date)

select * from openquery(BAN23,'SELECT contab.fu_icaro_tasas (trunc(current_date),''USD'',''COP'') FROM dual')

This last query works very well.

Upvotes: 1

A.B.Cade
A.B.Cade

Reputation: 16915

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;

Upvotes: 1

Related Questions