Hi there,
I've here an rather complex problem with whom google couldn't help me.
<h3>The Situation</h3>
I've got two databases (
local db : oracle 10XE,
remote db: non-oracle) which are connected via dblink. The connection works perfectly. On local db I want to execute this query:
select sum(revenue), year, brand1
from revenues@remote
where cus_encryptData('MFGR#12', 'mykey') = category -- <--
group by year, brand1
order by year;
whereas
cus_encryptData(<str>, <str>) is user defined function which turns a plaintext into a ciphertext. This function can for security and technical reasons only reside on the local db. Here is the definition:
create or replace function cus_encryptData2(plain varchar2, pw varchar2)
return varchar2 deterministic PARALLEL_ENABLE is [...]
<h3>The Problem</h3>
This query works, although very slowly (because a lot of data has to exchanged between remote and local db). The execution plan suggests that the where condition is not propagated to the remote db. The only thing that happens on the remote db is the column select. But when I change the query to:
select sum(revenue), year, brand1
from revenues@remote
where 'dLWEfksdaAWE321asDcASd2' = category --'dLWEfksdaAWE321asDcASd2' == cus_encryptData2('MFGR#12', 'mykey')
group by year, brand1
order by year;
the where condition is propagated and everything works as it is supposed to (only the aggregation is done locally, but thats ok).
<h3>The Question</h3>
Does anybody know how to get oracle to just send the result of cus_encryptData to the remote db or rather to resolve the function first before sending it to the remote db? Moreover, the solution should be as simple as possible, i.e. if possible only standard SQL.
Thanks a lot
Chris