SQL & PL/SQL

Hello all,

I have a really specific issue when using a pipelined function used in a complex subquery where the function ignores the changes made on the current transaction. The problem is the hidden hint materialize sometimes used by the Oracle optimizer. I say sometimes because it depends mostly on the execution plan and the complexity of the query.

I can repeat the problem with a dummy scenario.

Let's say we have a dummy table with a simple record :
CREATE TABLE DUMMY ("NAME" VARCHAR2(50 BYTE));
INSERT INTO DUMMY VALUES('Original name');

We then create a package which will contain our pipelined function and its record object and collection:
CREATE OR REPLACE PACKAGE PKG_DUMMY AS
TYPE DUMMY_RECORD IS RECORD (NAME VARCHAR2(50 BYTE));
TYPE DUMMY_RECORDS IS TABLE OF DUMMY_RECORD;
FUNCTION FUNC_GET_DUMMY_NAME RETURN DUMMY_RECORDS PIPELINED;
END PKG_DUMMY;

CREATE OR REPLACE
PACKAGE BODY PKG_DUMMY AS
FUNCTION FUNC_GET_DUMMY_NAME RETURN DUMMY_RECORDS PIPELINED AS
BEGIN

FOR CUR IN ( SELECT * FROM DUMMY )
LOOP
PIPE ROW (CUR);
END LOOP;

END FUNC_GET_DUMMY_NAME;
END PKG_DUMMY;

With this SQL query, we can return the value of the table by the pipelined function :
WITH DUMMY_NAME AS
(
SELECT "NAME"
FROM TABLE(PKG_DUMMY.FUNC_GET_DUMMY_NAME())
)
SELECT "NAME"
FROM DUMMY_NAME

Result
Original name

If we modify the DUMMY table with a new name without a commit, and re-execute the query, we got the same result :
UPDATE DUMMY SET "NAME" = 'New name';

Result
New name

But if we add the materialize hint in the subquery (without doing a commit or rollback), we have the original value hence my issue :
WITH DUMMY_NAME AS
(
SELECT /*+ materialize */ "NAME"
FROM TABLE(PKG_DUMMY.FUNC_GET_DUMMY_NAME())
)
SELECT "NAME"
FROM DUMMY_NAME

Result
Original name



I know I can force my subquery to use an inline hint instead of the "materialize" hint chose by the optimizer but then the query lose a lot of performance. Is there a way to force Oracle to use current DML changes with the materialize hint on a pipelined funtion in a subquery?

This thread is also for this issue : http://stackoverflow.com/questions/1597467/is-using-a-select-inside-a-pipelined-pl-sql-table-function-allowed