Oracle caches CLOB to Temporary Table

Question

I have two custom types in my Oracle DB

TYPE SETTING IS OBJECT (
   SETT_NAME VARCHAR2(32767),
   SETT_VALUE CLOB,
);  

TYPE SETTINGS_SET IS TABLE OF SETTING;

And I have simple function that returns SETTINGS_SET

FUNCTION Revision RETURN SETTINGS_SET PIPELINED IS
   INSTANCE SETTING;
BEGIN 
-- body is simplified for example
   INSTANCE = SETTING('Id', 'Long clob value');
   PIPE ROW(INSTANCE);
END;    

or non-pipelined function

FUNCTION Revision RETURN SETTINGS_SET IS
   SET SETTINGS_SET = SETTINGS_SET();
BEGIN 
-- body is simplified for example
   SETTINGS_SET(1) := SETTING('Id', 'Long clob value');
END; 

And this works well, but oracle caches CLOB value to temporary storage for current session.
This storage can be observed in v$session system table.

My problem is that I have only one Oracle session in my application, and this storage filling up quickly.

1) Why oracles caches clob in this case?
2) Can I turn off caching CLOB values in my function?
Also I have tried non-pipelines function, but rhis wasn't helpful.

Answer 1

Since you're piping the output, you'll need to cleanup the temporary clobs on the user end. Meaning you create the pointer inside the function, and pass it on (pipe it) out the end user, who will need to cleanup. For example:

CREATE OR REPLACE
type MY_CLOB as object
(
  some_char varchar2(4000),
  some_clob clob
);

CREATE OR REPLACE
type MY_CLOB_TAB as table of my_clob;

Function is:

CREATE OR REPLACE function pipe_clobs
return my_clob_tab
pipelined IS
    l_clob_obj my_clob;
    l_clob CLOB;
BEGIN

  for i in 1 .. 10
  loop
    l_clob := 'This is temp clob ' || i;
    l_clob_obj := my_clob('My varchar2 value', l_clob);
    pipe row(l_clob_obj);
  end loop;

END;

And the user would be (for example):

begin
    for rec in (select some_char, some_clob from table(pipe_clobs))
    loop
        -- use it
        dbms_output.put_line('Clob value is: ' || rec.some_clob);
        -- and free it
        dbms_lob.freetemporary(rec.some_clob);
    end loop;
end;

Check via:

select * from v$temporary_lobs;