Home » SQL & PL/SQL » SQL & PL/SQL » SYSDATE from another database, how to get?
SYSDATE from another database, how to get? [message #240040] Thu, 24 May 2007 00:31 Go to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Hi all,

I have 2 DB servers and I want to run a PL/SQL procedure on 1 where I can use the SYSDATE of the 2nd database and not the one where the script is running?

Is there a way to retrieve the Sysdate from the other DB?

To clarify:
IN DB1, i need my code to use DB2.sysdate???

Thanks for the help in advance.

Baz
Re: SYSDATE from another database, how to get? [message #240042 is a reply to message #240040] Thu, 24 May 2007 00:34 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
you want to take sysdate from other database during runtime??

for that i guess you should use service to connect and fetch date from that.

[Updated on: Thu, 24 May 2007 00:38]

Report message to a moderator

Re: SYSDATE from another database, how to get? [message #240048 is a reply to message #240042] Thu, 24 May 2007 00:39 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Thanks DreamzZ,

Can you elaborate on this? Is there an easier way to do it while running my code?

regards
Re: SYSDATE from another database, how to get? [message #240052 is a reply to message #240048] Thu, 24 May 2007 00:47 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
eg


select sysdate from dual@db2

for dblinks see this

http://www.psoug.org/reference/db_link.html
Re: SYSDATE from another database, how to get? [message #240057 is a reply to message #240052] Thu, 24 May 2007 00:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I doubt if that would work. I am afraid the database/optimizer is smarter than that.
I could not find anything in the docs to prove me right, but it is a gut feeling
Don't have the facilities to test it.

Another way would be to write a wrapper-function on the other database.
create or replace function my_sysdate
return date
is
begin
  return sysdate;
end my_sysdate;
Re: SYSDATE from another database, how to get? [message #240080 is a reply to message #240057] Thu, 24 May 2007 01:40 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Thanks guys,

I will try both scenarios and will let you know which will work. If not I will post a response.

Cheers
Re: SYSDATE from another database, how to get? [message #240091 is a reply to message #240080] Thu, 24 May 2007 02:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If Frank is right, it might be worth trying
select /*+DRIVING_SITE(dual)*/ sysdate 
from dual@db2


Ross Leishman
Re: SYSDATE from another database, how to get? [message #240110 is a reply to message #240091] Thu, 24 May 2007 02:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I was more under the impression that sysdate would not actually be fetched.
But still, worth the tries.
Please test Ross' solution too, as I am very curious which solution(s) work!
Re: SYSDATE from another database, how to get? [message #240132 is a reply to message #240110] Thu, 24 May 2007 04:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can get sysdate from the remote site with the driving site hint, but it looks like Oracle is being clever again, and only fetching sysdate from one site at a time:
SQL> select 'Here' loc,to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') from dual
  2  union all
  3  select 'Remote',to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') from dual@Remote;

LOC       TO_CHAR(SYSDATE,'DD
--------- -------------------
Here      24/05/2007 10:02:56
Remote 24/05/2007 10:02:56

SQL> alter system set fixed_date = '2007/05/23'
  2  ;

System altered.

SQL> select 'Here' loc,to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') from dual
  2  union all
  3  select 'Remote',to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') from dual@Remote;

LOC       TO_CHAR(SYSDATE,'DD
--------- -------------------
Here      23/05/2007 00:00:00
Remote 23/05/2007 00:00:00

SQL> select 'Here' loc,to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') from dual
  2  union all
  3  select 'Remote',to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') from dual@Remote
  4  union all
  5  select /*+ driving_site(dual) */ 'Remote Hint',to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') from dual@Remote;

LOC                              TO_CHAR(SYSDATE,'DD/MM/YYYYHH24:MI:SS')
-------------------------------- ---------------------------------------------------------------------------
Here                             24/05/2007 10:03:47
Remote                        24/05/2007 10:03:47
Remote Hint                   24/05/2007 10:03:47

[Updated on: Thu, 24 May 2007 04:05]

Report message to a moderator

Re: SYSDATE from another database, how to get? [message #240155 is a reply to message #240132] Thu, 24 May 2007 04:39 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Thanks guys,

I agree with JRowBottom. I tried it and got the same result. Now the question is how can we force Oracle to fetch the SYSDATE from the Remote DB??

Any help is appreciated.

Baz
Re: SYSDATE from another database, how to get? [message #240186 is a reply to message #240155] Thu, 24 May 2007 05:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think using a wrapper could work. Again, I cannot test it here (I only have 1 db at home)
Re: SYSDATE from another database, how to get? [message #240216 is a reply to message #240186] Thu, 24 May 2007 06:59 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, indeed; wrapper function does work.
SQL> connect scott/tiger@ora10
Connected.
SQL> create or replace function my_sysdate
  2  return date
  3  is
  4  begin
  5    return sysdate;
  6  end my_sysdate;
  7  /

Function created.

SQL> grant execute on my_sysdate to public;

Grant succeeded.

SQL> connect mike/lion@ora8
Connected.

SQL> l
  1  select to_char(sysdate, 'hh24:mi:ss') time_here,
  2         to_char(my_sysdate@dbl, 'hh24:mi:ss') time_remote
  3* from dual
SQL> /

TIME_HER TIME_REM
-------- --------
13:52:50 13:52:52

SQL>
Previous Topic: Global temporary tables
Next Topic: How to create a simple join query?
Goto Forum:
  


Current Time: Fri Dec 09 01:46:02 CST 2016

Total time taken to generate the page: 0.14496 seconds