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

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