Re: (Fwd) Re: Any Oracle 9 users? A test please... - Mailing list pgsql-hackers

I received this via personal email.  I assume the author wants it
shared.  It shows CURRENT_TIMESTAMP changing within a function!

---------------------------------------------------------------------------

Steve Hulcher wrote:
> Oracle 9i.
> 
> Hope this is helpful
> 
> 
> --SQL RUN----------------------------------------------------
> /*
> CREATE TABLE foo (a DATE);
> CREATE OR REPLACE PROCEDURE test
> AS
> BEGIN
>   INSERT INTO foo SELECT CURRENT_TIMESTAMP FROM dual;
>   dbms_lock.sleep(5);
>   INSERT INTO foo SELECT CURRENT_TIMESTAMP FROM dual;
> END;
> /
> show errors;
> */
> 
> DELETE FROM foo;
> EXECUTE test;
> 
> SELECT TO_CHAR(a, 'YYYY-MM-DD HH24:MI:SS') FROM foo;
> 
> --RESULTS----------------------------------------------------
> 0 rows deleted.
> 
> 
> PL/SQL procedure successfully completed.
> 
> 
> TO_CHAR(A,'YYYY-MM-
> -------------------
> 2002-10-02 11:33:12
> 2002-10-02 11:33:17
> 
> 
> 
> -----Original Message-----
> From: Mike Mascari [mailto:mascarm@mascari.com]
> 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:
> > 
> > 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   SELECT SYSDATE
>    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
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 

--  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