How to get sysdate result via database link?

Question

I run a query on a database link to a Sybase server from Oracle.

In it, where clause is a date constraint, and I want it to be sysdate bound, so something like this:

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

The problem is that when I explain the plan, only the condition some_numeric_key = 1 appears in the actual sql, which is deleted to the sybase server. Oracle expects the date filter to execute on its side.

This leads to a performance nightmare - I need the date filter removed so that this query works quickly

Even if I try something like casting sysdate into a charcater string, like this: TO_CNAK (SYSDATE-2, 'YYYY-MM-DD')

He still does not delete it.

Is there anything I can do to get Oracle to remove this date filter from the db link on Sybase?

Answer 1

Performing integration between Oracle and other platforms, I often encounter this problem, not only SYSDATE , but also other non-standard functions.

There are two methods to solve this problem, the first of which is the most reliable in my experience.

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

Secondly, if you are not allowed to create objects on the remote side, try using bind variables (of the correct data type!) In the Oracle SELECT , for example:

 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; /