Hi Tom,

many thanks for the fast answer!

I created a test-function which worked locally.

But I have a problem, I can't get my code to work over the DB-link:

create or replace function test_remote_sysdate
return date
as
l_cursor int;
l_status int;
l_remote_sysdate date;
begin
l_cursor := dbms_sql.open_cursor;

dbms_sql.parse@REMOTE_DB1
( l_cursor,
'begin :x := sysdate; end;',
dbms_sql.native
);

dbms_sql.bind_variable( l_cursor, ':x', l_remote_sysdate );

l_status := dbms_sql.execute( l_cursor );
dbms_sql.variable_value(l_cursor, ':x', l_remote_sysdate );

dbms_sql.close_cursor(l_cursor);

return l_remote_sysdate;
end;
/

select sysdate, test_remote_sysdate from dual

ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "XXX.TEST_REMOTE_SYSDATE", line 11

(The db-link itself works, it is just a "loopback" to my db.)

Do you have an idea? Do I need any grants on the remote side?

Hi Tom,

sorry, I certainly must call DBMS_SQL at the remote site everywhere (use dbms_sql.xxx@REMOTE_DB everywhere).

Now it works:

create or replace function test_remote_sysdate
return date
as
l_cursor int;
l_status int;
l_remote_sysdate date;
begin

l_cursor := dbms_sql.open_cursor@REMOTE_DB;

dbms_sql.parse@REMOTE_DB
( l_cursor,
'begin :x := sysdate; end;',
dbms_sql.native
);

dbms_sql.bind_variable@REMOTE_DB
( l_cursor, ':x', l_remote_sysdate );

l_status := dbms_sql.execute@REMOTE_DB
( l_cursor );

dbms_sql.variable_value@REMOTE_DB
(l_cursor, ':x', l_remote_sysdate );

dbms_sql.close_cursor@REMOTE_DB
(l_cursor);

return l_remote_sysdate;
end;
/

It is very cool to be able to just

select sysdate - test_remote_sysdate as TIME_OFFSET from dual;

and get the time difference!


On more question:
I have several remote DB's/several DB-links,
so I want to pass the DB-link as a parameter into my function.
I guess I then have to use DBMS_SQL embedded in DBMS_SQL - dynamic dynamic sql?
I'm just trying this and get mad with all the '''' ...
Also debugging is tough!!!

Do you have a tip?

Thanks again!!!

Hi Tom,

many thanks!
Now with your help I ended up with the following working solution. I had some trouble to figure out that I have to use 'using out', not 'into'.

Perhaps it is interesting for other people as well, clocks not in sync between different dbs/servers seem fairly common.

create or replace
function remote_sysdate_at
( p_db_link_name in varchar2 )
return date
as
l_remote_sysdate date;
begin

execute immediate
replace
('declare ' ||
' l_cursor int; ' ||
' l_status int; ' ||
' l_date date; ' ||
'begin ' ||

' l_cursor := dbms_sql.open_cursor@<REMOTE_DB>; ' ||

' dbms_sql.parse@<REMOTE_DB> ' ||
' ( l_cursor, ' ||
' ''begin :x := sysdate; end;'', ' ||
' dbms_sql.native ' ||
' ); ' ||

' dbms_sql.bind_variable@<REMOTE_DB> ' ||
' ( l_cursor, '':x'', l_date ); ' ||

' l_status := dbms_sql.execute@<REMOTE_DB> ' ||
' ( l_cursor ); ' ||

' dbms_sql.variable_value@<REMOTE_DB> ' ||
' (l_cursor, '':x'', l_date ); ' ||

' dbms_sql.close_cursor@<REMOTE_DB> ' ||
' (l_cursor); ' ||

' :Y := l_date; ' ||
'end; '
,'<REMOTE_DB>', p_db_link_name
)
using out l_remote_sysdate
;

return l_remote_sysdate;

end;
/

select sysdate, remote_sysdate_at('DBLINK_NAME_TO_REMOTE_DB') from dual;

Then I saw: the first time I call it on a remote db, the difference between my sysdate and the remote is greater than all the follwing times - it must be the time it takes to connect to the remote db, the following times the connection is already there and is reused.

Ok, I could cope with that: just call it two times and use the second result.
Of course I cannot do anything about the parse/execute/network time, but do you know of other things which could come into play there?
Should I call it say 20 times, ignore the first result and do an average?

By the way, it this not something also the DB has to cope with concerning distributed transactions and such?
How does the DB do it?

Hello,

I just use this function on remote DBs to get sysdate from there:

create or replace function Now return date is
begin
return(sysdate);
end Now;

I can select the local and remote sysdate then as:

select sysdate, now@dblink from dual;