Re: driving_site and local function call

From: steph <stephan0h_at_yahoo.de>
Date: Mon, 23 Jun 2008 11:17:39 -0700 (PDT)
Message-ID: <00dca6c6-3eea-4470-8373-41aab5f0a4be@m3g2000hsc.googlegroups.com>


On Jun 22, 11:50 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On Jun 19, 8:57 pm, steph <stepha..._at_yahoo.de> wrote:
>
>
>
> > oracle 10G R2:
>
> > I've got this query accessing a lot of remote data via database link.
> > Therefore hint DRIVING_SITE is used to make the query happen on the
> > remote server. Now I've done a change, I call a local package
> > function, which makes the hint being ignored.
>
> > The query looks something like this
>
> > select /*+DRIVING_SITE(table1) */
> > <col1>, ...
> > , local_package.myfunc()
> > from table1_at_remotedb
> > inner join table2_at_remotedb on <join condition>
> > ...
>
> > When I comment the function call everything works as excpected, but
> > when the function call is uncommented the query is happening locally.
> > Is there any reason why the DRIVING_SITE-hint get's ignored the moment
> > i call the local function? Bug or feature?
>
> > Thanks,
> > Stephan
>
> Obviously, since the function is local, it can't be executed and
> evaluated at the remote db, so Oracle has to transfer the working set
> to the local db first and then call the function locally. For a
> distributed query to be executed remotely, all its components must be
> remote (tables, functions, etc.) You can copy the package to the
> remote db and call the function like this:
>
> select /*+DRIVING_SITE(table1) */
> <col1>, ...
> , PACKAGE.MYFUNC_at_remotedb()
> from table1_at_remotedb
> inner join table2_at_remotedb on <join condition>
>
> and see if the hint is obeyed again (I think it will be.)
>
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com

ok, i see. So this is a kind of a one-way street: I can query remote tables/packages ... but a local one cannot be called by the remote server -- I was hoping that this would happen "somehow" ...

ok, but then I could create a function on the remote site that would just do this:

create function return sometype as
begin
  return call_to_local_function_at_local_db(); end;

this should make it work, shouldn't it?

thanks,
stephan Received on Mon Jun 23 2008 - 13:17:39 CDT

Original text of this message