Just one method can get remote DB time
SQL> create or replace function test_remote_sysdate
2 return date
3 as
4 l_cursor int;
5 l_status int;
6 l_remote_sysdate date;
7 begin
8
9 l_cursor := dbms_sql.open_cursor@fcdw1cn;
10
11 dbms_sql.parse@fcdw1cn
12 ( l_cursor,
13 'begin :x := sysdate; end;',
14 dbms_sql.native
15 );
16
17 dbms_sql.bind_variable@fcdw1cn
18 ( l_cursor, ':x', l_remote_sysdate );
19
20 l_status := dbms_sql.execute@fcdw1cn
21 ( l_cursor );
22
23 dbms_sql.variable_value@fcdw1cn
24 (l_cursor, ':x', l_remote_sysdate );
25
26 dbms_sql.close_cursor@fcdw1cn
27 (l_cursor);
28
29 return l_remote_sysdate;
30 end;
31 /
Function created.
SQL> select TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS'), TO_CHAR(test_remote_sysdate,'YYYYMMDD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,' TO_CHAR(TEST_REMO
----------------- -----------------
20080419 11:51:40 20080419 12:07:52
2 return date
3 as
4 l_cursor int;
5 l_status int;
6 l_remote_sysdate date;
7 begin
8
9 l_cursor := dbms_sql.open_cursor@fcdw1cn;
10
11 dbms_sql.parse@fcdw1cn
12 ( l_cursor,
13 'begin :x := sysdate; end;',
14 dbms_sql.native
15 );
16
17 dbms_sql.bind_variable@fcdw1cn
18 ( l_cursor, ':x', l_remote_sysdate );
19
20 l_status := dbms_sql.execute@fcdw1cn
21 ( l_cursor );
22
23 dbms_sql.variable_value@fcdw1cn
24 (l_cursor, ':x', l_remote_sysdate );
25
26 dbms_sql.close_cursor@fcdw1cn
27 (l_cursor);
28
29 return l_remote_sysdate;
30 end;
31 /
Function created.
SQL> select TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS'), TO_CHAR(test_remote_sysdate,'YYYYMMDD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,' TO_CHAR(TEST_REMO
----------------- -----------------
20080419 11:51:40 20080419 12:07:52