CODEWITHSUNDEEP
oracle-database
user3098484
user3098484

Reputation: 83

temporary memory allocated for BLOB and CLOB

What I want to do is to say I have one package in that 2 and procedure and 2 function below

look like ..

create or replace PACKAGE BODY xml_zip_pkg AS

PROCEDURE ZIP(src IN CLOB, dst IN OUT BLOB)
AS LANGUAGE JAVA
NAME 'com.oracle.ZipXml.zip(oracle.sql.CLOB, oracle.sql.BLOB[])';

PROCEDURE UNZIP(src IN BLOB, dst IN OUT CLOB)
AS LANGUAGE JAVA
NAME 'com.oracle.UnzipXml.unzipClob(oracle.sql.BLOB, oracle.sql.CLOB[])';

FUNCTION ZIP(src IN clob) RETURN blob
IS
 lvResult blob;
BEGIN
 IF src is not null THEN
  DBMS_LOB.createtemporary(lvResult, true, DBMS_LOB.CALL);
  xml_zip_pkg.ZIP(src, lvResult);
 END IF;
 RETURN lvResult;
END ZIP;

FUNCTION UNZIP(src IN blob) RETURN clob
IS
 lvResult clob;
BEGIN
 IF src is not null THEN
  DBMS_LOB.createtemporary(lvResult, true, DBMS_LOB.CALL);
  xml_zip_pkg.UNZIP(src, lvResult);
 END IF;
 RETURN lvResult;
END UNZIP;
END;

Output :-

select XML_ZIP_PKG.UNZIP(XML_ZIP_PKG.ZIP('abc xyz welcome to capa town') ) from dual;

abc xyz welcome to capa town

If using the DBMS_LOB. FREETEMPORARY (lvResult); in function, it not RETURN the value

select XML_ZIP_PKG.UNZIP(XML_ZIP_PKG.ZIP('abc xyz welcome to capa town') ) from dual;

null value

Please let me know how close to the memory issue for this package.

Upvotes: 0

Views: 2787

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

It isn't clear if you actually have a memory issue at the moment. From the documentation:

The temporary LOB instance exists in your application until it goes out of scope, your session terminates, or you explicitly free the instance. Freeing a temporary LOB instance is recommended to free system resources.

Your function is returning the temporary LOB, so it's still in-scope to the caller. In this case the caller is your SQL query. The temporary LOB will be freed when the query completes, as it will then go out of scope. You don't need to, and can't, free it before you return it. You will only have a memory issue if you're calling your function(s) for multiple values or multiple rows at a time; the temporary LOBs from the various calls may all be in scope at the same time and could collectively use more memory than you want. As a single call it isn't really an issue though.

You can see this by running this query before and after your function call:

select * from v$temporary_lobs where sid = sys_context('USERENV','SID');

In SQL*Plus that shows cache_lobs as zero both before after after. In SQL Developer though it shows cache_lobs incrementing, which I wasn't expecting, which might be a JDBC issue. They are still freed when the session ends, but they persist until then, apparently.

Calling the function from an anonymous PL/SQL block still frees it automatically, in both SQL*Plus and SQL Developer (which I suppose makes sense as JDBC never sees the actual CLOB):

declare
  tmp clob;
begin
  tmp := XML_ZIP_PKG.UNZIP(XML_ZIP_PKG.ZIP('abc xyz welcome to capa town') );
end;
/

You can also add DBMS_LOB.FREETEMPORARY(tmp); within the block to free it explicitly if you prefer.

Upvotes: 1

vav
vav

Reputation: 4684

Where is a problem? returned though the procedure or function blob allocator will be released after rollback/commit/disconnect.

Upvotes: 0

Related Questions