title: How to return temporary CLOB instance from stored function in Pl/SQL?
body: <p>My stored function creates temporary LOB instance using: <code>Dbms_Lob.CreateTemporary(BUFFER, TRUE, Dbms_Lob.SESSION);</code> where <code>BUFFER</code> is a local <code>CLOB</code> variable. After that the function fills <code>BUFFER</code> with some data and returns it.</p>
  
  <p>Duration parameter of <code>Dbms_Lob.CreateTemporary</code> in my case is <code>Dbms_Lob.SESSION</code>, but according to <a href="http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#i1015757" rel="nofollow">oracle documentation</a>:</p>
  
  <blockquote>
    <p><strong>The duration parameter</strong> passed to dbms_lob.createtemporary() <strong>is a hint</strong>.
    The duration of the new temp LOB is the same as the duration of the
    locator variable in PL/SQL. For example, in the preceding program
    block, the program variable a has the duration of the residing frame.
    Therefore at the end of the block, memory of a will be freed at the
    end of the function.</p>
  </blockquote>
  
  <p>So <code>BUFFER CLOB</code> may be destroyed by Oracle after leaving the function block. I can see that in some cases, when the BUFFER is more than 32K, I can’t read it’s value returned this way from Java (JDBC) side.</p>
  
  <p>Is there any other way to return temporary CLOB instance from a function?</p>