RE: Function - return Deterministic

Does this help?

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6978
972926020


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Eriovaldo Andrietta
Sent: Friday, August 17, 2012 8:34 AM
To: ORACLE-L
Subject: Function - return Deterministic

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


Please visit our website at 
http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.html 
for important disclosures and information about our e-mail 
policies. For your protection, please do not transmit orders 
or instructions by e-mail or include account numbers, Social 
Security numbers, credit card numbers, passwords, or other 
personal information.
--
http://www.freelists.org/webpage/oracle-l

Other related posts: