Подсказка Oracle WITH и MATERIALIZE действует как автономная транзакция для функций
В Oracle 12c, если я вызываю функцию в запросе, который использует подсказку MATERIALIZE в разделе WITH..AS, вызов функции действует как автономная транзакция:
DROP TABLE my_table;
CREATE TABLE my_table (
my_column NUMBER
);
-- Returns number of records in table
CREATE OR REPLACE FUNCTION my_function
RETURN INTEGER
IS
i INTEGER;
BEGIN
SELECT COUNT(1) INTO i FROM my_table;
RETURN i;
END;
/
-- Inserts one record to table
INSERT INTO my_table (my_column) VALUES (9);
-- Returns number of records in table. This works correctly, returns 1
SELECT COUNT(1) AS "use simple select" FROM my_table;
-- Returns number of records in table. This works correctly, returns 1
WITH x AS (
SELECT /*+ MATERIALIZE */ COUNT(1) AS "use WITH, MATERIALIZE" FROM my_table
)
SELECT * FROM x;
-- Returns number of records in table. This works correctly, returns 1
SELECT my_function AS "use FUNCTION" FROM dual;
-- Returns number of records in table. This works INCORRECTLY, returns 0.
-- Function is called in autonomous transaction?
WITH x AS (
SELECT /*+ MATERIALIZE */ my_function "use WITH,MATERIALIZE,FUNCTION" FROM dual
)
SELECT * FROM x;
ROLLBACK;
Кто-нибудь знает в чем причина этого? Это ошибка Oracle или она предназначена для такой работы? (Почему?) Почему это работает так, только когда WITH сочетается с MATERIALIZED hint и вызовом FUNCTION?