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(at)mascari(dot)com