Function - return Deterministic
- From: Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 17 Aug 2012 09:34:29 -0300
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
