Re: Getting sysdate across a DB link

Paul Moore wrote:

> I'm trying to get the value of SYSDATE on a remote server. I have a
> database link to the server, but I'm not sure how to force SYSDATE to
> be evaluated on the remote machine. I know that ideally, all machines
> are in sync, but in practice, that's not the case.
>
> As a simplified version of my problem, suppose I want to calculate the
> clock difference between the local and remote machines. The "obvious"
> approach is
>
> select l.sysdate - r.sysdate from dual l, dual_at_remote_db r;
>
> This doesn't work - it gives me an error "ORA-01747: invalid
> user.table.column, table.column, or column specification" (presumably
> because SYSDATE is a date function, not a column).
>
> I'd really like to do this without needing to install anything on the
> remote database (I don't have a lot of control over the remote DB) and
> in SQL rather than PL/SQL. (Essentially so that I can do "INSERT INTO
> local_table SELECT ..., remote_date, ... FROM ..._at_remote_db"). If it's
> not possible, I can relax the restrictions, but I may just stick with
> a client-side answer (fetch from the remote DB, insert into the local
> one, using 2 database connections and no DB link). I'd like to do it
> all on the server, though...
>
> Thanks for any suggestions,
>
> Paul.
>
> PS I just tried using inline views:
>
> select (local - remote)*24*3600 diff
> from
> (select sysdate local from dual) l,
> (select sysdate remote from dual_at_t) r;
>
> That didn't work either :-(

I'd write a function on that machine to return it.

Daniel Morgan Received on Fri Feb 28 2003 - 11:05:39 CST