Function - return Deterministic

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Fri, 17 Aug 2012 09:34:29 -0300
Message-ID: <CAJdDhaMfTuQaBH3Erw-fBCKtoyhwnEV8b-7NNfM6hkQJBP44cw_at_mail.gmail.com>

Hi Friends,
I am in trouble with this resource.

1.) The function F_TESTE_DETERMINISTIC works fine as the concept of DETERMINISTIC.
     It executes once.

CREATE OR REPLACE FUNCTION F_TESTE_DETERMINISTIC (PARAMETRO IN NUMBER) RETURN NUMBER
DETERMINISTIC
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('RETORNO : ' || PARAMETRO);   RETURN PARAMETRO;
END; set serveroutput on

SELECT ROWNUM N,

       teste (1),
       DBMS_RANDOM.STRING ('X', 16)
       FROM dual

CONNECT BY LEVEL <= 10;

         N TESTE(1) DBMS_RANDOM.STRING('X',16) ---------- ----------

---

         1          1 24CRBOWA1RG390FZ
         2          1 PNJZD51W00Z5Q8HF
         3          1 SAAHZS9KDBP9HAP6
         4          1 QOH0LBM34BW1WDQT
         5          1 1QK8SOJ3Z89UFQ3R
         6          1 PG5CJ4JXL6IV6ZEF
         7          1 29I9Z89PXQU11QUT
         8          1 S1DFH9RNX34ZOYJ1
         9          1 IR4FZCHM70DKUDQD
        10          1 8WBO9530DN8O6TRG

foo : 1

10 rows selected

2.) But , using the return SDO_GEOMETRY as below, it does not happen.

    It calls 10 times the function.

CREATE OR REPLACE FUNCTION point
  (x in NUMBER, y in NUMBER, srid in NUMBER DEFAULT 8370) RETURN SDO_GEOMETRY
DETERMINISTIC
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('x = : ' || x || ' y = ' || y);   RETURN SDO_GEOMETRY (2001, srid,

                       SDO_POINT_TYPE (x,y,NULL),
                       NULL, NULL);

END;
/

SQL> select point (1,2) from dual
  2 CONNECT BY LEVEL <= 10;

POINT(1,2)

---

<Object>
<Object>
<Object>
<Object>
<Object>
<Object>
<Object>
<Object>
<Object>
<Object>

x = : 1 y = 2
x = : 1 y = 2
x = : 1 y = 2
x = : 1 y = 2
x = : 1 y = 2
x = : 1 y = 2
x = : 1 y = 2
x = : 1 y = 2
x = : 1 y = 2
x = : 1 y = 2

10 rows selected

Why does it happen ? The DETERMINISTIC does not works with SDO_GEOMETRY ?

Best Regards
Eriovaldo

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Aug 17 2012 - 07:34:29 CDT