Thread: Re: (Fwd) Re: Any Oracle 9 users? A test please...
It is not clear to me; is this its own transaction or a function call? --------------------------------------------------------------------------- Dan Langille wrote: > 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. > > From: "Shawn O'Connor" <soconnor@mail.e-perception.com> > To: Dan Langille <dan@langille.org> > Subject: Re: Any Oracle 9 users? A test please... > In-Reply-To: <3D985663.24174.80554E83@localhost> > Message-ID: <20020930114241.E45374-100000@mail.e-perception.com> > 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 > > > On Mon, 30 Sep 2002, Dan Langille wrote: > > > 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. > > > > On 30 Sep 2002 at 10:31, Shawn O'Connor wrote: > > > > > 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 > > > > > > On Mon, 30 Sep 2002, Dan Langille wrote: > > > > > > > Followups to freebsd-database@freebsd.org 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 > > > > -- > > > > Dan Langille > > > > I'm looking for a computer job: > > > > http://www.freebsddiary.org/dan_langille.php > > > > > > > > > > > > To Unsubscribe: send mail to majordomo@FreeBSD.org > > > > 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 pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > It is not clear to me; is this its own transaction or a function call? > That looks like an anonymous PL/SQL procedure to me. Another question might be, given: "more than one reference to one or more <datetime value function>s, then all such references are effectively evaluated simultaneously" under what conditions does Oracle report *the same* value for CURRENT_TIMESTAMP? So far, in this discussion, we have the following scenarios: 1. RDBMS start: No one 2. Session start: No one 3. Transaction start: PostgreSQL 4. Statement start: ??? 5. CURRENT_TIMESTAMP evaluation: Oracle 9, ??? Given what Tom has posted regarding the standard, I think Oracle is wrong. I'm wondering how the others handle multiple references in CURRENT_TIMESTAMP in a single stored procedure/function invocation. It seems to me that the lower bound is #4, not #5, and the upper bound is implementation dependent. Therefore PostgreSQL is in compliance, but its compliance is not very popular. Mike Mascari mascarm@mascari.com > Dan Langille wrote: >> >> >>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.
I am starting to see Tom's issue here. If you have a PL/pgSQL function that does: > >>DECLARE > >>BEGIN > >> SELECT CURRENT_TIMESTAMP INTO time1 FROM DUAL; > >> SELECT CURRENT_TIMESTAMP INTO time2 FROM DUAL; > >>END; You would want those two to be the same because they are in the same function, but by looking at it, they look the same as interactive queries. In a sense if we change CURRENT_TIMESTAMP, we are scoping the variable to match the users/client's perspective. However, we have added statement_timeout, so it does seem we have had to move to a more user-centered perspective on some of these things. The big question is whether a variable that would be inserted into the database should have such scoping. I can see cases where people would want that, and others where they wouldn't. > 1. RDBMS start: No one > 2. Session start: No one > 3. Transaction start: PostgreSQL > 4. Statement start: ??? > 5. CURRENT_TIMESTAMP evaluation: Oracle 9, ??? This is a nice chart. Oracle already has transaction start reported by sysdate: > SQL> begin > 2 insert into rbr_foo select sysdate from dual; > [...wait about 10 seconds...] > 3 insert into rbr_foo select sysdate from dual; > 4 end; > 5 / > > PL/SQL procedure successfully completed. > > SQL> select * from rbr_foo; > > A > --------------------- > SEP 27, 2002 12:57:27 > SEP 27, 2002 12:57:27 so for CURRENT_TIMESTAMP it seems they have evaluation-time, while MSSQL/Interbase have statement time. > Given what Tom has posted regarding the standard, I think Oracle > is wrong. I'm wondering how the others handle multiple > references in CURRENT_TIMESTAMP in a single stored > procedure/function invocation. It seems to me that the lower > bound is #4, not #5, and the upper bound is implementation > dependent. Therefore PostgreSQL is in compliance, but its > compliance is not very popular. I don't see how we can be compliant if SQL92 says: The time of evaluation of the <datetime value function> during theexecution of the SQL-statement is implementation-dependent. It says it has to be "during the SQL statement", or is SQL statement also ambiguous? Is that why Oracle did what they did? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Tue, 2002-10-01 at 01:10, Bruce Momjian wrote: > > > Given what Tom has posted regarding the standard, I think Oracle > > is wrong. I'm wondering how the others handle multiple > > references in CURRENT_TIMESTAMP in a single stored > > procedure/function invocation. It seems to me that the lower > > bound is #4, not #5, and the upper bound is implementation > > dependent. Therefore PostgreSQL is in compliance, but its > > compliance is not very popular. > > I don't see how we can be compliant if SQL92 says: > > The time of evaluation of the <datetime value function> during the > execution of the SQL-statement is implementation-dependent. > > It says it has to be "during the SQL statement", or is SQL statement > also ambiguous? It can be, as "during the SQL statement" can mean either the single statement inside the PL/SQL function (SELECT CURRENT_TIMESTAMP INTO time1 FROM DUAL;) or the whole invocation of the Pl/SQL funtion (the / command in Mikes sample, i believe) -------------- Hannu
Hannu Krosing wrote: > On Tue, 2002-10-01 at 01:10, Bruce Momjian wrote: > > > > > Given what Tom has posted regarding the standard, I think Oracle > > > is wrong. I'm wondering how the others handle multiple > > > references in CURRENT_TIMESTAMP in a single stored > > > procedure/function invocation. It seems to me that the lower > > > bound is #4, not #5, and the upper bound is implementation > > > dependent. Therefore PostgreSQL is in compliance, but its > > > compliance is not very popular. > > > > I don't see how we can be compliant if SQL92 says: > > > > The time of evaluation of the <datetime value function> during the > > execution of the SQL-statement is implementation-dependent. > > > > It says it has to be "during the SQL statement", or is SQL statement > > also ambiguous? > > It can be, as "during the SQL statement" can mean either the single > statement inside the PL/SQL function (SELECT CURRENT_TIMESTAMP INTO > time1 FROM DUAL;) or the whole invocation of the Pl/SQL funtion (the / > command in Mikes sample, i believe) Which is what Oracle may have done. SQL99 talks about triggers seeing the same date/time, but then again if your trigger is a function, it has to see the same values for all of its calls. This doesn't match Oracle, unless they have some switch that returns consistent values when the function is called as a trigger (yuck). -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > Hannu Krosing wrote: > >>It can be, as "during the SQL statement" can mean either the single >>statement inside the PL/SQL function (SELECT CURRENT_TIMESTAMP INTO >>time1 FROM DUAL;) or the whole invocation of the Pl/SQL funtion (the / >>command in Mikes sample, i believe) > > > Which is what Oracle may have done. SQL99 talks about triggers seeing > the same date/time, but then again if your trigger is a function, it has > to see the same values for all of its calls. This doesn't match Oracle, > unless they have some switch that returns consistent values when the > function is called as a trigger (yuck). > I think there is a #6 level in that chart. For example: INSERT INTO foo(field1, field2, field3) SELECT CURRENT_TIMESTAMP, (some time-intensive subquery), CURRENT_TIMESTAMP FROM bar; I'd bet Oracle inserts the same value for CURRENT_TIMESTAMP for both fields for every row. And that is what they view as a "SQL Statement". I've only got 8, so I can't test. Also, as you point out, Oracle may distinguish between PL/SQL created anonymously or with CREATE PROCEDURE vs. PL/SQL code created with CREATE FUNCTION. It may be that UDFs return a single CURRENT_TIMESTAMP for the life of the invocation, while stored procedures don't. It is PostgreSQL, after all, that has merged the two concepts into one. Maybe someone could test version 9 with a FUNCTION that executes the same PL/SQL code and returns the difference between the two times. Mike Mascari mascarm@mascari.com
On Mon, 30 Sep 2002 15:29:07 -0400, Mike Mascari <mascarm@mascari.com>
wrote:
> I'm wondering how the others handle multiple
>references in CURRENT_TIMESTAMP in a single stored
>procedure/function invocation.
MSSQL 7 seems to evaluate CURRENT_TIMESTAMP for each statement,
Interbase 6 once per procedure call. Here are my test procedures:
MSSQL 7
create table tst (i integer, d datetime not null)
go
create procedure tstInsert
as begin delete from tst insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst
b,tst c, tst d, tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b,
tstc, tst d, tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst
c,tst d, tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c,
tstd, tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst
d,tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d,
tste insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst
einsert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst e
insertinto tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst e
insertinto tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst e
end
go
begin transaction
exec tstInsert
commit transaction
select * from tst
i d
----------- ---------------------------
0 2002-09-30 22:26:06.540
1 2002-09-30 22:26:06.540
32 2002-09-30 22:26:06.540
243 2002-09-30 22:26:06.540
1024 2002-09-30 22:26:06.550
3125 2002-09-30 22:26:06.550
7776 2002-09-30 22:26:06.550
16807 2002-09-30 22:26:06.560
32768 2002-09-30 22:26:06.570
59049 2002-09-30 22:26:06.590
(10 row(s) affected)
Interbase 6
SQL> create table tst(i integer, d timestamp);
SQL> commit;
SQL> set term !!;
SQL> create procedure tstInsert as begin
CON> delete from tst;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> end;
CON> !!
SQL> set term ; !!
SQL> commit;
SQL> execute procedure tstInsert; -- takes approx. 5 seconds.
SQL> select * from tst;
I D
============ =========================
0 1858-11-17 00:00:00.0000 1 2002-09-30 22:37:54.0000 32 2002-09-30 22:37:54.0000 243
2002-09-3022:37:54.0000 1024 2002-09-30 22:37:54.0000 3125 2002-09-30 22:37:54.0000 7776 2002-09-30
22:37:54.0000 16807 2002-09-30 22:37:54.0000 32768 2002-09-30 22:37:54.0000 59049 2002-09-30
22:37:54.0000
SQL> commit;
BTW, it's interesting (but OT) how they handle
select count(*), current_timestamp, 1 from tst where 0=1;
differently.
MSSQL: 0 2002-09-30 22:53:55.920 1
Interbase: 0 1858-11-17 00:00:00.0000 0 <--- bug here?
Postgres: 0 2002-09-30 21:10:35.660781+02 1
ServusManfred
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I don't see how we can be compliant if SQL92 says:
> The time of evaluation of the <datetime value function> during the
> execution of the SQL-statement is implementation-dependent.
> It says it has to be "during the SQL statement", or is SQL statement
> also ambiguous? Is that why Oracle did what they did?
Yes, you're finally seeing my issue: "SQL statement" isn't all that
well-defined a concept.
ISTM that the reported behavior of Oracle's pl/sql is *clearly* in
violation of SQL92: the body of a pl/sql function is a single <SQL
procedure statement> per SQL92 4.17, so how can they allow
current_timestamp to change within it?
It would be even more interesting to try the same function called
from another pl/sql function --- in that scenario, hardly anyone
could deny that the whole execution of the inner function is contained
within one statement of the outer function, and therefore
current_timestamp should not be changing within it.
regards, tom lane
Hello! On Mon, 30 Sep 2002, Bruce Momjian wrote: > It is not clear to me; is this its own transaction or a function call? BTW. As reported by my friend: Oracle 8.1.7 (ver.9 behaves the same way): --- cut --- SQL> SET TRANSACTION READ WRITE; Transaction set. SQL> SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') FROM DUAL; TO_CHAR(SYSDATE,'MM ------------------- 02-10-2002 10:04:19 SQL> -- wait a lot SQL> SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') FROM DUAL; TO_CHAR(SYSDATE,'MM ------------------- 02-10-2002 10:04:27 SQL> COMMIT; Commit complete. --- cut --- > > > > > 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? -- WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group. Phone: +7 (3832) 106228, ext.140, E-mail: byg@center-f1.ru. Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.
Yury Bokhoncovich <byg@center-f1.ru> writes:
> 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?
regards, tom lane
Tom Lane wrote: > Yury Bokhoncovich <byg@center-f1.ru> writes: > >>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? Roland used an anonymous PL/SQL procedure: SQL> begin 2 insert into rbr_foo select sysdate from dual; [...wait about 10 seconds...] 3 insert into rbr_foo select sysdate from dual; 4 end; 5 / PL/SQL procedure successfully completed. SQL> select * from rbr_foo; Oracle isn't processing those statements interactively. SQL*Plus is waiting on the "/" to send the PL/SQL block to the database. I suspect its not going to take Oracle more than a second to insert a row... Mike Mascari mascarm@mascari.com
Mike Mascari wrote: > Tom Lane wrote: > > Yury Bokhoncovich <byg@center-f1.ru> writes: > > > >>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? > > Roland used an anonymous PL/SQL procedure: > > SQL> begin > 2 insert into rbr_foo select sysdate from dual; > [...wait about 10 seconds...] > 3 insert into rbr_foo select sysdate from dual; > 4 end; > 5 / > > PL/SQL procedure successfully completed. > > SQL> select * from rbr_foo; > > Oracle isn't processing those statements interactively. SQL*Plus > is waiting on the "/" to send the PL/SQL block to the database. > I suspect its not going to take Oracle more than a second to > insert a row... Oh, I understand now. He delayed when entering the function body, but that has no effect when he sends it. Can someone add an explicit sleep in the function body and try that? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > Mike Mascari wrote:>> >>Oracle isn't processing those statements interactively. SQL*Plus >>is waiting on the "/" to send the PL/SQL block to the database. >>I suspect its not going to take Oracle more than a second to >>insert a row... > > > Oh, I understand now. He delayed when entering the function body, but > that has no effect when he sends it. Can someone add an explicit sleep > in the function body and try that? > SQL> create table foo (a date); Table created. SQL> begin 2 insert into foo select sysdate from dual; 3 dbms_lock.sleep(5); 4 insert into foo select sysdate fromdual; 5 end; 6 / PL/SQL procedure successfully completed. SQL> select to_char(a, 'HH24:MI:SS') from foo; TO_CHAR( -------- 11:31:02 11:31:07 Mike Mascari mascarm@mascari.com
Mike Mascari wrote: > Bruce Momjian wrote: > > Mike Mascari wrote: > >> > >>Oracle isn't processing those statements interactively. SQL*Plus > >>is waiting on the "/" to send the PL/SQL block to the database. > >>I suspect its not going to take Oracle more than a second to > >>insert a row... > > > > > > Oh, I understand now. He delayed when entering the function body, but > > that has no effect when he sends it. Can someone add an explicit sleep > > in the function body and try that? > > > > SQL> create table foo (a date); > > Table created. > > SQL> begin > 2 insert into foo select sysdate from dual; > 3 dbms_lock.sleep(5); > 4 insert into foo select sysdate from dual; > 5 end; > 6 / > > PL/SQL procedure successfully completed. > > SQL> select to_char(a, 'HH24:MI:SS') from foo; > > TO_CHAR( > -------- > 11:31:02 > 11:31:07 OK, two requests. First, would you create a _named_ PL/SQL function with those contents and try it again. Also, would you test CURRENT_TIMESTAMP too? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote:
>
> OK, two requests. First, would you create a _named_ PL/SQL function
> with those contents and try it again. Also, would you test
> CURRENT_TIMESTAMP too?
>
SQL> CREATE TABLE foo(a date);
Table created.
As a PROCEDURE:
SQL> CREATE PROCEDURE test 2 AS 3 BEGIN 4 INSERT INTO foo SELECT SYSDATE FROM dual; 5 dbms_lock.sleep(5); 6
INSERT INTO foo SELECT SYSDATE FROM dual; 7 END; 8 /
Procedure created.
SQL> execute test;
PL/SQL procedure successfully completed.
SQL> select to_char(a, 'HH24:MI:SS') from foo;
TO_CHAR(
--------
12:01:07
12:01:12
As a FUNCTION:
SQL> CREATE FUNCTION mydiff 2 RETURN NUMBER 3 IS 4 time1 DATE; 5 time2 DATE; 6 c NUMBER; 7 BEGIN 8
SELECTSYSDATE 9 INTO time1 10 FROM DUAL; 11 SELECT COUNT(*) 12 INTO c 13 FROM bar, bar, bar, bar, bar, bar,
bar,bar; 14 SELECT SYSDATE 15 INTO time2 16 FROM DUAL; 17 RETURN (time2 - time1); 18 END; 19 /
Function created.
SQL> select mydiff FROM dual;
MYDIFF
----------
.000034722
I can't test the use of CURRENT_TIMESTAMP because I have Oracle
8, not 9.
Mike Mascari
mascarm@mascari.com
Mike Mascari <mascarm@mascari.com> writes:
> SQL> CREATE PROCEDURE test
> 2 AS
> 3 BEGIN
> 4 INSERT INTO foo SELECT SYSDATE FROM dual;
> 5 dbms_lock.sleep(5);
> 6 INSERT INTO foo SELECT SYSDATE FROM dual;
> 7 END;
> 8 /
> Procedure created.
> SQL> execute test;
> PL/SQL procedure successfully completed.
> SQL> select to_char(a, 'HH24:MI:SS') from foo;
> TO_CHAR(
> --------
> 12:01:07
> 12:01:12
What fun. So in reality, SYSDATE on Oracle behaves like timeofday():
true current time. That's certainly not a spec-compliant interpretation
for CURRENT_TIMESTAMP :-(
Has anyone done the corresponding experiments on the other DBMSes to
identify exactly when they allow CURRENT_TIMESTAMP to advance?
regards, tom lane
Mike Mascari <mascarm@mascari.com> wrote: > I can't test the use of CURRENT_TIMESTAMP because I have Oracle > 8, not 9. What about NOW()? It should be available in Oracle 8? Is it the same as SYSDATE? Regards, Michael Paesold
Michael Paesold wrote: > What about NOW()? It should be available in Oracle 8? Is it the same as > SYSDATE? > Unless I'm missing something, NOW() neither works in Oracle 8 nor appears in the Oracle 9i online documentation: http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/functions2.htm#80856 Mike Mascari mascarm@mascari.com
Mike Mascari <mascarm@mascari.com> wrote: > Michael Paesold wrote: > > > What about NOW()? It should be available in Oracle 8? Is it the same as > > SYSDATE? > > > > Unless I'm missing something, NOW() neither works in Oracle 8 > nor appears in the Oracle 9i online documentation: > > http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/fu nctions2.htm#80856 > > Mike Mascari I am sorry, if that is so. I thought it was available, but obviously, I was wrong. Regards, Michael
Tom Lane wrote: > > >Has anyone done the corresponding experiments on the other DBMSes to >identify exactly when they allow CURRENT_TIMESTAMP to advance ? > I have Db2 on hand and examined CURRENT TIMESTAMP in an sql procedure. (IBM have implemented it without the "_" ....) The short of it is that CURRENT TIMESTAMP is the not frozen to the transaction start, but reflects time movement within the transaction. Note that "db2 +c" is equivalent to issueing BEGIN in Pg, and the command line tool (db2) keeps (the same) connection open until the TERMINATE is issued : $ cat stamp.sql create procedure stamp() language sql begin insert into test values(1,current timestamp); insert into test values(2,current timestamp); insert into test values(3,currenttimestamp); insert into test values(4,current timestamp); insert into test values(5,current timestamp); insertinto test values(6,current timestamp); insert into test values(7,current timestamp); insert into test values(8,currenttimestamp); insert into test values(9,current timestamp); end @ $ db2 connect to dss Database Connection Information Database server = DB2/LINUX 7.2.3 SQL authorization ID = DB2 Local database alias = DSS $ db2 -td@ -f stamp.sql DB20000I The SQL command completed successfully. $ db2 +c db2 => call stamp(); "STAMP" RETURN_STATUS: "0" db2 => commit; DB20000I The SQL command completed successfully. db2 => select * from test; ID VAL ----------- -------------------------- 1 2002-10-03-19.35.16.286019 2 2002-10-03-19.35.16.286903 3 2002-10-03-19.35.16.287549 4 2002-10-03-19.35.16.288235 5 2002-10-03-19.35.16.288925 6 2002-10-03-19.35.16.289571 7 2002-10-03-19.35.16.290209 8 2002-10-03-19.35.16.290884 9 2002-10-03-19.35.16.291522 9 record(s) selected. db2 => terminate; regards Mark
The original tester says "this is an anonymous procedure". On 30 Sep 2002 at 15:07, Bruce Momjian wrote: > > It is not clear to me; is this its own transaction or a function > call? > > ---------------------------------------------------------------------- > ----- > > Dan Langille wrote: > > 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. > > > > From: "Shawn O'Connor" <soconnor@mail.e-perception.com> > > To: Dan Langille <dan@langille.org> > > Subject: Re: Any Oracle 9 users? A test please... > > In-Reply-To: <3D985663.24174.80554E83@localhost> > > Message-ID: <20020930114241.E45374-100000@mail.e-perception.com> > > 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 > > > > > > On Mon, 30 Sep 2002, Dan Langille wrote: > > > > > 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. > > > > > > On 30 Sep 2002 at 10:31, Shawn O'Connor wrote: > > > > > > > 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 > > > > > > > > On Mon, 30 Sep 2002, Dan Langille wrote: > > > > > > > > > Followups to freebsd-database@freebsd.org 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 > > > > > -- > > > > > Dan Langille > > > > > I'm looking for a computer job: > > > > > http://www.freebsddiary.org/dan_langille.php > > > > > > > > > > > > > > > To Unsubscribe: send mail to majordomo@FreeBSD.org > > > > > 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 > pgman@candle.pha.pa.us | (610) 359-1001 + If your > 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
>>>>> "Mike" == Mike Mascari <mascarm@mascari.com> writes: Mike> Tom Lane wrote: >> Yury Bokhoncovich <byg@center-f1.ru> writes: >>> 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 fromRoland 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 roland@rlenter.com 76-15 113th Street, Apt 3B roland@astrofoto.org Forest Hills, NY 11375