Re: (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