RE: Function - return Deterministic

From: <rajendra.pande_at_ubs.com>
Date: Fri, 17 Aug 2012 13:28:51 -0400
Message-ID: <7E4D006EA3F0D445B62672082A16A5657A142D_at_NSTMC703PEX.ubsamericas.net>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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

Received on Fri Aug 17 2012 - 12:28:51 CDT