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