Re: Odd sysdate construction

  • From: Vladimir Begun <Vladimir.Begun@xxxxxxxxxx>
  • To: "Oracle-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 12 Jan 2005 13:44:16 -0800

Vladimir Begun wrote:
> Freeman, Donald wrote:
> 
>>select SYSDATE@! from dual;
>>This appeared in a client query (actually it was trunc(sysdate@!) )
>>What is the @!?   Its valid in the Oracle takes it but I can't find any =
>>documentation as to what it is. It seems to act the same as plain old =
>>sysdate. But it's the only place where @! Is allowed.
> 
> Please review:
> http://www.orafaq.net/maillist/oracle-l/2003/10/17/1450.htm

I got a message from one of the list's participants, and I
believe it's necessary to provide a clarification:

-- DROP USER t1 CASCADE
-- DROP USER t2 CASCADE

CREATE USER t1 IDENTIFIED BY t1;
CREATE USER t2 IDENTIFIED BY t2;
GRANT CREATE SESSION, CREATE DATABASE LINK TO t1;
GRANT CREATE SESSION TO t2;

CREATE OR REPLACE TRIGGER sys.trg$t1
   AFTER LOGON
   ON T1.SCHEMA
BEGIN
   EXECUTE IMMEDIATE 'alter session set events = ''10046 trace name context 
forever, level 12'' tracefile_identifier=''t1''';
END;
/
CREATE OR REPLACE TRIGGER sys.trg$t2
   AFTER LOGON
   ON T2.SCHEMA
BEGIN
   EXECUTE IMMEDIATE 'alter session set events = ''10046 trace name context 
forever, level 12'' tracefile_identifier=''t2''';
END;
/
CONNECT t1/t1
CREATE DATABASE LINK t1_t2 CONNECT TO t2 IDENTIFIED BY t2 USING 'fake';
-- fake is a fake connect string
SELECT sysdate FROM dual@t1_t2;
SELECT user FROM dual@t1_t2;

-- after this point you'd get the trace files:
-- 1) original trace file with produced by session t1
-- SELECT sysdate FROM dual@t1_t2;

-- 2) the trace file produced by session t2 that does
-- callback from the remote server to local one, something
-- similar to:

-- PARSING IN CURSOR #2 len=33 dep=0 uid=85 oct=3 lid=85 tim=1903060877517 
hv=2617363369 ad='10a2ce18'
-- SELECT SYSDATE@! FROM "DUAL" "A1"
-- END OF STMT

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

--
//www.freelists.org/webpage/oracle-l

Other related posts: