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