Getting sysdate across a DB link

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 :-( Received on Fri Feb 28 2003 - 10:53:10 CST