(Fwd) Re: Any Oracle 9 users? A test please...

(Fwd) Re: Any Oracle 9 users? A test please...

Post by Roland Rober » Sat, 05 Oct 2002 22:00:18





    >>> As reported by my friend: Oracle 8.1.7 (ver.9 behaves the same way):

    >>> [ to_char(sysdate) advances in a transaction ]

    >> Now I'm really confused; this directly contradicts the report
    >> of Oracle 8's behavior that we had earlier from Roland Roberts.
    >> Can someone explain why the different results?

    Mike> Roland used an anonymous PL/SQL procedure:

You're right and I didn't think enough about what was happening.  This
also explains why I so often see the same timestamp throughout a
transaction---the transaction is all taking place inside a PL/SQL
procedure.

roland
--
                       PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

(Fwd) Re: Any Oracle 9 users? A test please...

Post by Dan Langill » Sat, 05 Oct 2002 21:58:44


The original tester says "this is an anonymous procedure".


> It is not clear to me;  is this its own transaction or a function
> call?

> ----------------------------------------------------------------------
> -----


> > And just for another opinion, which supports the first.

> > >From now, unless you indicate otherwise, I'll only report tests
> > >which
> > have both values the same.



> > Subject: Re: Any Oracle 9 users?  A test please...


> > MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII
> > X-PMFLAGS: 35127424 0 1 P2A7A0.CNM

> > Okay, here you are:
> > ----------------------------------

> > DECLARE
> >  time1 TIMESTAMP;
> >  time2 TIMESTAMP;
> >  sleeptime NUMBER;
> > BEGIN
> >  sleeptime := 5;
> >  SELECT CURRENT_TIMESTAMP INTO time1 FROM DUAL;
> >  DBMS_LOCK.SLEEP(sleeptime);
> >  SELECT CURRENT_TIMESTAMP INTO time2 FROM DUAL;
> >  DBMS_OUTPUT.PUT_LINE(TO_CHAR(time1));
> >  DBMS_OUTPUT.PUT_LINE(TO_CHAR(time2));
> > END;
> > /
> > 30-SEP-02 11.54.09.583576 AM
> > 30-SEP-02 11.54.14.708333 AM

> > PL/SQL procedure successfully completed.

> > ----------------------------------

> > Hope this helps!

> >  -Shawn


> > > We're testing this just to see what Oracle does.  What you are
> > > saying is what we expect to happen.  But could you do that test
> > > for us from the command line?  Thanks.


> > > > I'm assuming your doing this as some sort of anonymous
> > > > PL/SQL function:

> > > > Don't you need to do something like:

> > > > SELECT CURRENT_TIMESTAMP FROM DUAL INTO somevariable?

> > > > and to wait five seconds probably:

> > > > EXECUTE DBMS_LOCK.SLEEP(5);

> > > > But to answer your question-- When this PL/SQL function
> > > > is run the values of current_timestamp are not the same, they
> > > > will be sepearated by five seconds or so.

> > > > Hope this helps!

> > > >  -Shawn



> > > > > Any Oracle 9 users out there?

> > > > > I need this run:

> > > > >         BEGIN;
> > > > >         SELECT CURRENT_TIMESTAMP;
> > > > >         -- wait 5 seconds
> > > > >         SELECT CURRENT_TIMESTAMP;

> > > > > Are those two timestamps the same?

> > > > > Thanks
> > > > > --
> > > > > Dan Langille
> > > > > I'm looking for a computer job:
> > > > > http://www.freebsddiary.org/dan_langille.php


> > > > > with "unsubscribe freebsd-database" in the body of the message

> > > --
> > > Dan Langille
> > > I'm looking for a computer job:
> > > http://www.freebsddiary.org/dan_langille.php

> > ------- End of forwarded message -------
> > --
> > Dan Langille
> > I'm looking for a computer job:
> > http://www.freebsddiary.org/dan_langille.php

> --
>   Bruce Momjian                        |  http://candle.pha.pa.us

>   life is a hard drive,     |  13 Roberts Road +  Christ can be your
>   backup.        |  Newtown Square, Pennsylvania 19073

--
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


 
 
 

(Fwd) Re: Any Oracle 9 users? A test please...

Post by Rob Fullert » Sat, 05 Oct 2002 21:58:22


Howdy All,

You have to explicitly commit transactions in oracle using SQL*Plus.
However, DUAL (eg. SELECT current_timestamp FROM DUAL;) is special in this
case.  It is a table in the sys schema, used for selecting constants,
pseudo-columns, etc.

I'm not sure if this helps but see:

http://download-east.oracle.com/otndoc/oracle9i/901_doc/server.901/a9...

rob
'Oracle 9 tester' :P


> OK, I just received this answer from an Oracle 9 tester.  It shows
> CURRENT_TIMESTAMP changing during the transaction.  Thanks, Dan.

> Dan, it wasn't clear if this was in a transaction or not.  Does Oracle
> have autocommit off by default so you are always in a transaction?

> ---------------------------------------------------------------------------


> > A very quick answer:

> > ------- Forwarded message follows -------
> > Date: Mon, 30 Sep 2002 13:03:51 -0400 (EDT)



> > Subject: Re: Any Oracle 9 users?  A test please...

> > Message-ID: <Pine.BSF.4.44.0209301303030.50384-

> > MIME-Version: 1.0
> > Content-Type: TEXT/PLAIN; charset=US-ASCII


> > > Date: Mon, 30 Sep 2002 12:49:59 -0400





> > > please...


> > > Any Oracle 9 users out there?

> > > I need this run:

> > >         BEGIN;
> > >         SELECT CURRENT_TIMESTAMP;
> > >         -- wait 5 seconds
> > >         SELECT CURRENT_TIMESTAMP;

> > > Are those two timestamps the same?

> > > Thanks

> > Our DBA says:

> > <snip from irc>

> > <data> SQL> SELECT current_timestamp FROM DUAL;
> > <data> CURRENT_TIMESTAMP
> > <data>
> > ----------------------------------------------------------------------

> > ----- <data> 30-SEP-02 01.06.42.660969 PM -04:00 <data> SQL> SELECT
> > current_timestamp FROM DUAL; <data> CURRENT_TIMESTAMP <data>
> > ----------------------------------------------------------------------

> > ----- <data> 30-SEP-02 01.06.48.837372 PM -04:00 <data> (you have to
> > include 'from dual' for 'non-table' selects)

> > --
> >    Derek Marshall

> > Smash and Pow Inc > 'digital plumber'
> > http://www.smashpow.net


> > with "unsubscribe freebsd-database" in the body of the message

> > ------- End of forwarded message -------
> > --
> > Dan Langille
> > I'm looking for a computer job:
> > http://www.freebsddiary.org/dan_langille.php

> --
>   Bruce Momjian                        |  http://candle.pha.pa.us

>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html