How to have sysdate result remoted across database link?

Question

I'm running a query across a database link to a Sybase server from Oracle.

In it's where clause is a restriction on date, and I want it tied to sysdate, so something like this:

select * from some_remote_view where some_numeric_key = 1 and some_date > sysdate+2

The problem is, when I do explain plan, only the condition some_numeric_key = 1 shows up in the actual sql that is getting remoted to the sybase server. Oracle is expecting to perform the date filter on its side.

This is causing a performance nightmare - I need that date filter remoted across to have this query working quickly

Even if I try something like casting the sysdate to a charcater string like this: to_char(sysdate-2,'YYYY-MM-DD')

It still does not remote it.

Is there anything I can do to get Oracle to remote this date filter across the db link to Sybase?

Answer 1

Doing integration between Oracle and other platforms I often run into this problem, not just with SYSDATE but with other non-standard functions as well.

There are two methods to work around the issue, the first being the most reliable in my experience.

First, you can create a view on the remote db with the filters you need, then on the Oracle side you just select from the new view without additional filters.

Second, if you are not allowed to create objects on the remote side, try using bind variables (of the correct data type!) in your Oracle SELECT statement, e.g.:

declare
   v_some_date constant date := sysdate + 2;
begin
    insert into oracle_table (...)
    select ...
      from remote_table@db_link t
     where t.some_numeric_key = 1
       and t.some_date > v_some_date;

    commit;
end;
/