Re: Getting sysdate across a DB link

 it works with full views - but my clocks are synchronized so your mileage may vary.
SQL> CREATE VIEW MOO AS SELECT 1 AS SILLY_HEADER,SYSDATE AS MOOTIME FROM DUAL; View created.

SQL> SELECT * FROM MOO; SILLY_HEADER MOOTIME
------------ ---------

           1 28-FEB-03 select (local - MOOTIME)*24*3600 diff

from

(select sysdate local from dual) l,

(select MOOTIME FROM MOO@<mylink>) r;

returns 0 as answer ...

moo !

"Paul Moore" <paul.moore_at_atosorigin.com> wrote in message news:182bcf76.0302280853.71aed8f0_at_posting.google.com...
> 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 - 11:08:20 CST