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

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(at)mascari(dot)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(at)mascari(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073