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

2002-10-04 Thread Roland Roberts
Mike == Mike Mascari [EMAIL PROTECTED] writes: Mike Tom Lane wrote: Yury Bokhoncovich [EMAIL PROTECTED] 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

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

2002-10-04 Thread Rob Fullerton
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:

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

2002-10-04 Thread Dan Langille
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

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

2002-10-03 Thread Mario Weilguni
Tom Lane wrote: Has anyone done the corresponding experiments on the other DBMSes to identify exactly when they allow CURRENT_TIMESTAMP to advance ? This applies up to Oracle 8.1.6, maybe it helps: According to a co-worker, Oracle advances the time in transactions: select to_char(sysdate,

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

2002-10-03 Thread Mark Kirkwood
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

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

2002-10-02 Thread Yury Bokhoncovich
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,

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

2002-10-02 Thread Tom Lane
Yury Bokhoncovich [EMAIL PROTECTED] 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

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

2002-10-02 Thread Mike Mascari
Tom Lane wrote: Yury Bokhoncovich [EMAIL PROTECTED] 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

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

2002-10-02 Thread Bruce Momjian
Mike Mascari wrote: Tom Lane wrote: Yury Bokhoncovich [EMAIL PROTECTED] 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

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

2002-10-02 Thread Mike Mascari
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

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

2002-10-02 Thread Bruce Momjian
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

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

2002-10-02 Thread Mike Mascari
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

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

2002-10-02 Thread Tom Lane
Mike Mascari [EMAIL PROTECTED] 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

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

2002-10-02 Thread Bruce Momjian
Message- From: Mike Mascari [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 02, 2002 11:20 AM To: Bruce Momjian Cc: Yury Bokhoncovich; Dan Langille; Roland Roberts; PostgreSQL-development Subject: Re: [HACKERS] (Fwd) Re: Any Oracle 9 users? A test please... Bruce Momjian wrote

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

2002-10-02 Thread Michael Paesold
Mike Mascari [EMAIL PROTECTED] 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 ---(end of

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

2002-10-02 Thread Mike Mascari
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:

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

2002-10-02 Thread Michael Paesold
Mike Mascari [EMAIL PROTECTED] 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:

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

2002-09-30 Thread Bruce Momjian
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?

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

2002-09-30 Thread Bruce Momjian
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

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

2002-09-30 Thread Mike Mascari
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 functions, then all such references are effectively

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

2002-09-30 Thread Bruce Momjian
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

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

2002-09-30 Thread Hannu Krosing
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

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

2002-09-30 Thread Bruce Momjian
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

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

2002-09-30 Thread Mike Mascari
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

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

2002-09-30 Thread Manfred Koizar
On Mon, 30 Sep 2002 15:29:07 -0400, Mike Mascari [EMAIL PROTECTED] 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

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

2002-09-30 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] 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