Re: Getting sysdate across a DB link

On 28 Feb 2003, paul.moore_at_atosorigin.com wrote:
> I'm trying to get the value of SYSDATE on a remote server.

[...]

> select l.sysdate - r.sysdate from dual l, dual_at_remote_db r;

I think I misread your post when I answered previously, the confusing thing about your post was that I thought you were having trouble retrieving sysdate across a dblink but then I tried just a local select.

    SQL> select t.sysdate from dual t;
    select t.sysdate from dual t

             *
    ERROR at line 1:
    ORA-01747: invalid user.table.column, table.column, or column specification

So, your error you referenced isn't dblink based. Therefore, it looks like you are struggling with finding the time differences of your local sysdate and the remote sysdates, not struggling with sysdates across a dblink?

So, then you need to be able to execute them both at the same time and compare the differences? But, you need to alias because you have to delinieate which server it came from? I played around with this a bit and came up with the following (I don't have a remote database to play with, but I think you could use it)

    SQL> DECLARE

         local_dt varchar2(100);
         remote_dt varchar2(100);
         sqlstr varchar2(1000);
         BEGIN
            sqlstr := 'select (select (to_char(sysdate,''MM/DD/YYYY HH:MM:SS'')) from dual),
                      (select (to_char(sysdate,''MM/DD/YYYY HH:MM:SS'')) from dual) from dual';
            execute immediate sqlstr into local_dt, remote_dt;
            dbms_output.put_line(local_dt || ' ' || remote_dt) ;
         END;
    /     

    03/02/2003 08:03:05 03/02/2003 08:03:05

    PL/SQL procedure successfully completed.

The only question I would have is, there is a network wait. When is the local sysdate calculation fired in relation to the remote sysdate calculation?

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.