I agree. Interesting comments.

Especially this enable inline row storage hint. I will do that. Can I enable it post factum with alter and/or rebuild or do I have to re-create the table?

Seems like 10g will render obsolete most of my custom Java code in Oracle DB (compression, regex).

If you want I can post my compression code (Java and PLSQL wrappers) so that other people might use it in 8i or 9i.

Thanks a lot.

Regards,
Piotr

...and self-explanatory.

Java part:

create or replace and compile java source named asco."LobCompressor" as
import java.lang.*;
import oracle.sql.*;
import java.io.*;
import java.util.zip.InflaterInputStream;
import java.util.zip.DeflaterOutputStream;

/**
* A simple class for LOB compression and decompression in Oracle Database. Will work in 8i and better.
*
* @author <a href="mailto:pjarmuz@poczta.onet.pl">Piotr Jarmuz</a>
*/
public class LobCompressor {
/**
* Compresses the CLOB into BLOB
*
* @param clob the source CLOB (plain text)
* @param blob the target BLOB (will hold compressed binary data) it should be an empty BLOB retrieved for example with dbms_lob.createtemporary(l_blob,true);
* @throws Exception mostly I/O exception if ever
*/
public static void compress(CLOB clob, BLOB blob) throws Exception {
InputStream in=clob.getAsciiStream();
DeflaterOutputStream z=new DeflaterOutputStream(blob.getBinaryOutputStream());
byte[] buffer=new byte[clob.getBufferSize()];

int cnt;
while ((cnt=in.read(buffer))!=-1) {
z.write(buffer,0,cnt);
}
in.close();
z.close();
}

/**
* Decompresses the BLOB into CLOB
*
* @param blob the source BLOB (compressed binary data)
* @param clob the target CLOB (will hold plain text) it should be an empty CLOB retrieved for example with dbms_lob.createtemporary(l_clob,true);
* @throws Exception mostly I/O exception if ever
*/
public static void decompress(BLOB blob, CLOB clob) throws Exception {
OutputStream out=clob.getAsciiOutputStream();
InflaterInputStream z=new InflaterInputStream(blob.getBinaryStream());
byte[] buffer=new byte[blob.getBufferSize()];

int cnt;
while ((cnt=z.read(buffer))!=-1) {
out.write(buffer,0,cnt);
}
z.close();
out.close();
}

/**
* Compresses the BLOB into BLOB
*
* @param slob the source BLOB (plain binary data)
* @param blob the target BLOB (will hold compressed binary data) it should be an empty BLOB retrieved for example with dbms_lob.createtemporary(l_blob,true);
* @throws Exception mostly I/O exception if ever
*/
public static void compress(BLOB slob, BLOB blob) throws Exception {
InputStream in=slob.getBinaryStream();
DeflaterOutputStream z=new DeflaterOutputStream(blob.getBinaryOutputStream());
byte[] buffer=new byte[slob.getBufferSize()];

int cnt;
while ((cnt=in.read(buffer))!=-1) {
z.write(buffer,0,cnt);
}
in.close();
z.close();
}

/**
* Decompresses the BLOB into CLOB
*
* @param blob the source BLOB (compressed binary data)
* @param slob the target CLOB (will hold plain binary data) it should be an empty CLOB retrieved for example with dbms_lob.createtemporary(l_blob,true);
* @throws Exception mostly I/O exception if ever
*/
public static void decompress(BLOB blob, BLOB slob) throws Exception {
OutputStream out=slob.getBinaryOutputStream();
InflaterInputStream z=new InflaterInputStream(blob.getBinaryStream());
byte[] buffer=new byte[blob.getBufferSize()];

int cnt;
while ((cnt=z.read(buffer))!=-1) {
out.write(buffer,0,cnt);
}
z.close();
out.close();
}
};
/

PL/SQL package specification:

create or replace package compressor is
function clob_compress(p_clob clob) return blob;
function clob_decompress(p_blob blob) return clob;
function blob_compress(p_blob blob) return blob;
function blob_decompress(p_blob blob) return blob;
end;
/


And PL/SQL package implementation:

create or replace package body compressor is

procedure clob_decompress(p_blob blob, p_clob clob)
as language java
name 'LobCompressor.decompress(oracle.sql.BLOB, oracle.sql.CLOB)';

procedure clob_compress(p_clob clob, p_blob blob)
as language java
name 'LobCompressor.compress(oracle.sql.CLOB, oracle.sql.BLOB)';

procedure blob_decompress(p_slob blob, p_blob blob)
as language java
name 'LobCompressor.decompress(oracle.sql.BLOB, oracle.sql.BLOB)';

procedure blob_compress(p_slob blob, p_blob blob)
as language java
name 'LobCompressor.compress(oracle.sql.BLOB, oracle.sql.BLOB)';

function clob_compress(p_clob clob) return blob is
l_blob blob;
begin
if p_clob is null then
return null;
end if;
dbms_lob.createtemporary(l_blob,true);
clob_compress(p_clob,l_blob);
return l_blob;
end;

function clob_decompress(p_blob blob) return clob is
l_clob clob;
begin
if p_blob is null then
return null;
end if;
dbms_lob.createtemporary(l_clob,true);
clob_decompress(p_blob,l_clob);
return l_clob;
end;

function blob_compress(p_blob blob) return blob is
l_blob blob;
begin
if p_blob is null then
return null;
end if;
dbms_lob.createtemporary(l_blob,true);
blob_compress(p_blob,l_blob);
return l_blob;
end;

function blob_decompress(p_blob blob) return blob is
l_blob blob;
begin
if p_blob is null then
return null;
end if;
dbms_lob.createtemporary(l_blob,true);
blob_decompress(p_blob,l_blob);
return l_blob;
end;

end;
/

I made PL/SQL wrappers functions so that they can be called from SQL layer e.g. views hiding (de)compression

Regards,
Piotr

I needed precisely the same, since I am not running 10g but only 9i2. Therefore,this posting saved a lot of my time.
It took to me 30 minutes to find the code, but it would have taken me much more than that in developing it and debugging it.
Thankyou so much Piotr, for posting it !

Re your reply above <quote>make sure to enable inline row storage -- else the lobs will be stored on a
block all by itself<quote>

To use utl_compress package, do I have to enable inline row storage? If I kept the LOBS in another tablespace, can it be compressed as well?

Is this package for all LOBS - BLOB, CLOB? Thanks. You are awesome.

Thanks.

If I read a BLOB, would it uncompress automatically? Thanks.

Tom, I have a new issue,
We have an application (C#) that store JPG images on 9iR2, this images are document’s pages where a document can have 1..N pages. So, the application show to the user a preview (in a listview) of all pages of a specific document and the user can select one and see the whole page (image), but the application is slow.
We debug it and find that the query is not the problem (less than a second) but the blob data reading is too slow (around 10 seconds) getting 5 images,

//Read blob data into byte array
int i = blob.Read(byteBLOBData,0,System.Convert.ToInt32(blob.Length));

develop guys, said that the solution is to CTAS from the image table and store the same images in lower quality to show it on the listviewer and use the original image table to get the whole image to client, so the images will be stored twice (original and lower quality) on the database (redundancy).

Do you know another way to perform the code to read blob data?
Is there another solution? Point me the link, if it is one.
develop guys are right?

I was thinking of redundancy, I mean having to tables with the same structure, "same data" (image/thumbnail), but you clarify me, so I add a new column to image table to store the thumbnail, thanks.

Tom, it seems that most of the BLOB compression functionality requires the BLOB already be in the database. Is there any way to compress the BLOB as it's being inserted (from client app) and remove the need to save it uncompressed and then compress it?

We are on 9i2 and are thinking of using this solution to save disk space. Ours is a insert heavy system and wanted some data on the performance impact of using these compression routines on inserts.

It is just inserts.. Size of LOB is about 50-100KB.. We will have to call these compression routines about 1-2 million times a day..

Thanks to Piotr Jarmuz for sample code above. Using the functions to decompress LOBs on-the-fly in views, do we need to free the temporary LOBs created by the decompression functions? For example:

We have an underlying table containing a compressed BLOB. Data is inserted via a procedure call from a Java-based client, similar to:
--
tempBlob = BLOB.createTemporary( conn, true, BLOB.DURATION_SESSION );
tempBlob.open( BLOB.MODE_READWRITE );
OutputStream os = tempBlob.getBinaryOutputStream() ;
... < read file contents into outputstream > ...
tempBlob.close();
cs = (CallableStatement) conn.prepareCall( "begin LobUtility_pkg.insertFile( ?, ? ); end;" );
cs.setObject(1, fileid);
cs.setObject(2, tempBlob);
cs.execute();
--
The "insertFile" packaged procedure handles compressing the data and persisting it into our underlying table (filequeue).

For subsequent access to the BLOB via SQL, we have a view:
create or replace view v as
select fileid, lobutility_pkg.blob_decompress( filecontent ) as filecontent from filequeue;

Would this decompression-on-the-fly view work as simply as this, or do we need to be concerned with freeing temporary LOBs?

I've run brief testing from SQL*Plus, selecting from the view, and then checking (in another session) v$temporary_lobs -- all looks fine to me, but I'm concerned that I may be overlooking something.

Just to be sure I understand which LOB needs freeing:

1) The insertion calls a packaged procedure, passing in a temporary LOB. This LOB is created and closed() by the client, then bound to a callable statement. Calls to freeTemporary() on this side cause ORA-22922: nonexistent LOB value ( maybe because the LOB was already closed()? )

2) The view encapsulating the decompression function returns a temporary blob via SQL. Clients could:
begin
for rec in (select fileid, filecontent from v)
loop
if dbms_lob.istemporary( rec.filecontent ) = 1 then
dbms_lob.freetemporary( rec.filecontent );
end if;
end loop;
end;

You're suggesting that we need to close the temporary blobs returned to us via the view, as in example # 2 above, correct? Anything to worry about on the insertion side (example # 1) ?

Found the documentation regarding the freeing of temporary lobs in SQL calls here:

</code> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96591/adl07mds.htm#135757 <code>

<quote>
Returned temporary LOBs automatically get freed at the end of a PL/SQL program block
</quote>

Given this tidbit, I think it's still a good practice to free the temporary LOB when processing a resultset containing LOBs, but at least this helps alleviate my concerns over temporary LOBs returned from PL/SQL function calls.

I have data in a CLOB column that I'd like to compress using the new-fangled UTL_COMPRESS. As you (and the documentation) point out, UTL_COMPRESS only handles RAW and BLOB.

So far I've been unsuccessful in figuring out how to coerce my CLOB data into a BLOB so I can then use UTL_COMPRESS on it... where should I be looking?

If all else fails, I can use Piotr's kindly contributed Java stored proc.

Hello. I am trying to create a trigger that will
automatically compress blobs while they are inserted
in the database (I use 10g). In:

</code> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg13trg.htm#376 <code>

there is an example...

"Example: Modifying LOB Columns with a Trigger"

...it clearly states that "Formerly, you could examine LOB
columns within a trigger body, but not modify them. Now,
you can treat them the same as other columns".

I have had ABSOLUTELY no luck modifing a blob value while
it is inserted in the db. No errors are generated, the
value just won't change! I know my problem is the way
I insert binary data in the database which is the
sql-loader.


My code:


CREATE OR REPLACE TRIGGER document_insert_trigger
BEFORE INSERT ON document
FOR EACH ROW
DECLARE
l_compressed_blob BLOB := TO_BLOB('1'); -- Initial value just in case...
BEGIN

-- Compress the data.
UTL_COMPRESS.lz_compress (src => :new.document_binary_data,
dst => l_compressed_blob);

-- update the blob value
SELECT l_compressed_blob INTO :new.document_binary_data FROM dual;

-- *** this does not work either ***
--:new.document_binary_data := l_compressed_blob;

DBMS_LOB.FREETEMPORARY(l_compressed_blob);
end;



Never works with the sql loader, but works nicely when I:


insert into document (document_binary_data)
values (TO_BLOB(UTL_RAW.CAST_TO_RAW('1234567890123456789012345678901234567890')));

commit;


How can I change this ? (I am on windows, but I would
also like to know how to make it work on various unix/linux
platoforms). I use direct=false (convensional method), the
trigger gets fired. It's only the blob column that does not
get updated.

Your help is INVALUABLE. Thank you.

Hello again. This is exactly what I do now. I run
a stored procedure to compress the BLOB data after
the import has finished.

What I am not really sure is if this saves space on
the hard disk, or if the space is never freed after
compressing the blob data. (Sorry if this is a faq
question, but I am a newbie on oracle)

Thank you very much.

Thanks Tom for the insight. Also, thanks to Piotr for sharing his Java and PL/SQL source in the third post.

It appears that inserting or updating a blob column as NULL is a valid statement. Why would/should one use empty_blob() instead of NULL? What is the effect?

Hi Tom,

I created a table as a copy of exisiting table which has LOB data type along with RAW data types also.

then I tried to compress the LOB data typed colum using utl_compress..
below is the complete test case.

SQL> c/SIGNMAINTENANCE/svsuser.SIGNMAINTENANCE
  1* create table t1 as select * from svsuser.SIGNMAINTENANCE where rownum < 10 and sign is
 not null
SQL> /

Table created.

SQL> declare
l_lob bl  2  ob ;
begin
for x in (select sign  3    4  id,SIGNGRPID,sign ,SALTVAL,ENCKEYS,EFFECTIVEDATE,EXPIRYDATE,
ACT  5  IVE,deleted,VERIFIED,LASTACTIVITY,CREATEDBY,CREATEDDATE,MODIFIEDBY,
MODIFIEDDATE  6  ,VERIFIEDBY,VERIFIEDDATE,REMARKS  from t1 ) loop
--
select utl_c  7    8  ompress.LZ_COMPRESS (x.sign) into l_lob from dual ;
insert  into  9   t2
values (x.s 10  ignid,x.SIGNGRPID,l_lob ,x.SALTVAL,x.ENCKEYS,x.EFFECTIVEDATE,x.EXPIRYDATE,
x.ACT 11  IVE,x.deleted,x.VERIFIED,x.LASTACTIVITY,x.CREATEDBY,x.CREATEDDATE,x.MODIFIEDBY,
x.MODIFIEDDATE,x.VERIFIEDBY,x.VERIFIEDDATE,x.REMARKS ) ;
DBMS_LO 13  B.FREETEMPORARY(l_lob) ;
commit  14  ;
end loop ;
end 15   16   ;
/ 17

PL/SQL procedure successfully completed.

SQL> select sum(bytes)/1024 from dba_segments where segment_name = 'T1' ;

SUM(BYTES)/1024
---------------
             64

SQL> c/T1/T2
  1* select sum(bytes)/1024 from dba_segments where segment_name = 'T2'
SQL> /

SUM(BYTES)/1024
---------------
             64

please note t2 is a copy of t1.

and 

SQL> Desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SIGNID                                             NVARCHAR2(30)
 SIGNGRPID                                          NVARCHAR2(30)
 SIGN                                               BLOB
 SALTVAL                                            RAW(8)
 ENCKEYS                                            RAW(48)
 EFFECTIVEDATE                                      TIMESTAMP(6)
 EXPIRYDATE                                         TIMESTAMP(6)
 ACTIVE                                             CHAR(1)
 DELETED                                            CHAR(1)
 VERIFIED                                           CHAR(1)
 LASTACTIVITY                                       NVARCHAR2(50)
 CREATEDBY                                 NOT NULL NVARCHAR2(50)
 CREATEDDATE                               NOT NULL TIMESTAMP(6)
 MODIFIEDBY                                         NVARCHAR2(50)
 MODIFIEDDATE                                       TIMESTAMP(6)
 VERIFIEDBY                                         NVARCHAR2(50)
 VERIFIEDDATE                                       TIMESTAMP(6)
 REMARKS                                            NVARCHAR2(650) 

T1 and T2 are the names of table segements.

lobs over 4000 bytes are stored out of line in their OWN segments.

You measured the table and the table was not "compressed"


ops$tkyte%ORA10GR2> select segment_name from user_segments;

no rows selected

ops$tkyte%ORA10GR2> create table t ( x clob );

Table created.

ops$tkyte%ORA10GR2> select segment_name from user_segments;

SEGMENT_NAME
------------------------------
T
SYS_IL0000127835C00001$$
SYS_LOB0000127835C00001$$
 

Tom,
I have following table with clob
SQL> desc smown.ms_usagesoapmsgs
Name Null? Type
----------------------------------------- -------- ----------------------------
EVENTID NOT NULL VARCHAR2(41)
MSGNAME NOT NULL VARCHAR2(64)
MPNAME NOT NULL VARCHAR2(64)
OPERATIONID NOT NULL NUMBER(38)
MSGCAPTUREDDTS NOT NULL DATE
MSGCAPTUREDMILLIS NOT NULL NUMBER(38)
MESSAGE NOT NULL CLOB


How can I compress clob o this table? It is 200k rows with 7 GB size.

CREATE TABLE "SMOWN"."MS_USAGESOAPMSGS"
( "EVENTID" VARCHAR2(41 BYTE) NOT NULL ENABLE,
"MSGNAME" VARCHAR2(64 BYTE) NOT NULL ENABLE,
"MPNAME" VARCHAR2(64 BYTE) NOT NULL ENABLE,
"OPERATIONID" NUMBER(38,0) NOT NULL ENABLE,
"MSGCAPTUREDDTS" DATE NOT NULL ENABLE,
"MSGCAPTUREDMILLIS" NUMBER(38,0) NOT NULL ENABLE,
"MESSAGE" CLOB NOT NULL ENABLE,
CONSTRAINT "MS_USAGESOAPMSG_PK" PRIMARY KEY ("EVENTID", "MSGNAME") ENABLE
) ;


ABove is the ddl

Similar to Glenn A's posting above, I've also set up a decompression-on-the-fly view :

create or replace view v as
select logid, compressor.clob_decompress( b1 ) as c1 from tab1;

doing:
select * from v;
returns the desired data, but each time I select from the view, the v$temporary_lobs.NOCAHE_LOBS count increases for each row returned. The view will be queried through a Python client via ADODB. How can I free the temporary lobs created here?

Sorry, I'm still not clear. I see examples in this and other threads using dbms_lob.freetemporary to free temporary lobs that are explicitly created and assigned to a variable (like in a PL/SQL block). But it appears that implicit temporary lobs are created (with session duration) when you use a sql function returning a LOB in a select statement. For example, each time I execute the following statement in SQL Navigator or from a script, CACHE_LOBS increases by 1 till the session ends:

select substr(to_clob(lpad('X',2000,'X')), 1, 100) from dual;

The same thing happens using decompress-on-the-fly view.

The goal here is to have the client script use a simple select statement on the view, retrieving the result as an ADODB Recordset. It doesn't appear as if I can call dbms_lob.freetemporary on the recordset Fields. Is some way to use it in a view definition?

Oops, above example increases CACHE_LOBS by 2 each time it runs, not 1.

Yep, makes sense. But is this 'so-long-thanks-for-all-the-LOBS' call possible in a SQL? Or ADODB code? Can you please provide an example?

Hi Tom,

This post has been very useful. In our scenario, we have this table which is used for helpdesk, bugs reporting, change request etc. In this we have a BLOB column which will store all kind of attachments, right from zips to office documents to snapshots. Day by day the activities are increasing and this lobsegment has grown very big from 2 gb to 40 gb (about couple of months ago) is now 73 gb. This is becoming unmanageable now due and we are running short of disk space. We are on 9iR2 currently. Given the scenerio, what do you think is the best approach towards achieving max compression? Is Piotr's only solution for us? Also for each compress/decompress, it will consume that much CPU?

Thanks in advance.

With respect to the production, there is no issue of disk space or CPU. Our system is sized well for the next 3 years. But the disk problem comes when we want to replicate this production environment for UAT or development or any other enviromnent. Those are not sized as per production and we have to keep increasing the SAN allocation every time we want to refresh those env. Is there any way in which we can exclude this particular table from the export dump?

THanks..

I was referring to manageability & disk space issue while replicating to the other environments.. just read what i wrote.. sorry for not being clear...

can you help with some links to get started with the fine grained access control policy? have never tried that but seems like a good idea.

Hi Tom,

I'm learning a lot from this thread, and tried to implement it myself but received an error in the call to UTL_COMPRESS.LZ_COMPRESS stating "PLS-00306: wrong number or types of arguments in call to lz_compress". Pointed to line 44 of the following snippet (tried to be as concise as possible, but not sure if this is enough to work with):

...
DECLARE
image_A ORDSYS.ORDIMAGE; -- source BLOB 
image_B ORDSYS.ORDIMAGE; -- source BLOB    
image_C ORDSYS.ORDIMAGE; -- source BLOB    
image_D ORDSYS.ORDIMAGE; -- source BLOB
l_compressed_A ORDSYS.ORDIMAGE; -- destination BLOB   
l_compressed_B ORDSYS.ORDIMAGE; -- destination BLOB    
l_compressed_C ORDSYS.ORDIMAGE; -- destination BLOB
l_compressed_D ORDSYS.ORDIMAGE; -- destination BLOB
TEMP_ID RAW(32):='';
TEMP_BLOB BLOB := P_DATA; -- input from procedure  
TEMP_BLOB_LENGTH number :=P_FileSize; -- input from proceudre    
        
  BEGIN
    INSERT INTO images ... VALUES
    -- I'm initializing the BLOBs so they're not NULL
(...ORDSYS.ORDIMAGE.init(),ORDSYS.ORDIMAGE.init(),ORDSYS.ORDIMAGE.init(),ORDSYS.ORDIMAGE.init() 
...) RETURN IMAGE_ID into TEMP_ID;     
SELECT IMAGE,IMAGE_B,IMAGE_CSHOW,IMAGE_D INTO 
image_A,image_B,image_C,image_D FROM images WHERE IMAGE_ID = TEMP_ID FOR UPDATE;    

dbms_lob.copy(image_A.source.localData,TEMP_BLOB,TEMP_BLOB_LENGTH);     
dbms_lob.copy(image_B.source.localData,TEMP_BLOB,TEMP_BLOB_LENGTH);     
dbms_lob.copy(image_C.source.localData,TEMP_BLOB,TEMP_BLOB_LENGTH);     
dbms_lob.copy(image_D.source.localData,TEMP_BLOB,TEMP_BLOB_LENGTH);     
     
image_A.setLocal();     
image_B.setLocal();     
image_C.setLocal();     
image_D.setLocal();
                
-- line 44 starts here
utl_compress.lz_compress(image_A, l_compressed_A);
utl_compress.lz_compress(image_B, l_compressed_B);
utl_compress.lz_compress(image_C, l_compressed_C);
utl_compress.lz_compress(image_D,l_compressed_D);
...

According to the documentation, all I need is a source (image_real) and a destination (l_compressed_real), which I think I've declared correctly(?) but I'm guessing I haven't since I do have the correct "number of arguments" so it must be the "type of argument". Maybe I can't decalre the destination BLOB like this?

Can you tell what I'm doing wrong from this information?

Thanks for your help.

doh! typo . . .

"According to the documentation, all I need is a source (image_real) and a destination (l_compressed_real)"

Should have said:

"According to the documentation, all I need is a source (image_A) and a destination (l_compressed_A)"

sorry about that

CREATE TABLE AUDIO_TBL
( "AUDIO_ID" RAW(32),
"AUDIO" "ORDSYS"."ORDAUDIO" );

CREATE OR REPLACE PROCEDURE compress_audio1 (p_audioID IN RAW) AS
  v_src_audio ORDSYS.ORDAudio;
  v_compressed_audio blob;
  ctx RAW(64) :=NULL;
BEGIN
  SELECT audio INTO v_src_audio FROM audio_tbl WHERE audio_id = p_audioID FOR UPDATE;
    dbms_lob.createtemporary(v_compressed_audio,true); -- make temp LOB
    obj.getContentInLob(ctx,v_compressed_audio); -- write from ORDAudio into LOB
    utl_compress.lz_compress(v_src_audio, v_compressed_audio); -- compress LOB
  UPDATE audio_tbl SET audio = v_compressed_audio WHERE audio_id = p_audioID; 
    dbms_lob.freetemporary(v_compressed_audio); -- free temp LOB
  COMMIT;
  EXCEPTION
    WHEN ORDSYS.ORDSourceExceptions.METHOD_NOT_SUPPORTED THEN
     DBMS_OUTPUT.PUT_LINE('ORDSourceExceptions.METHOD_NOT_SUPPORTED caught');
    WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('EXCEPTION caught');
END;
/

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE COMPRESS_AUDIO1:

LINE/COL ERROR
----------------------------------------------------------------------
8/5 PL/SQL: Statement ignored
8/9 PLS-00302: component 'GETCONTENTINLOB' must be declared
9/5 PL/SQL: Statement ignored
9/5 PLS-00306: wrong number or types of arguments in call to 'LZ_COMPRESS'

10/3 PL/SQL: SQL Statement ignored
10/28 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got BLOB

Tried to use the example in the docs here

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14297/ch_audref.htm#i1084527

where it's explained how to write out an ORDAudio type to a BLOB, which seems to be necessary to use UTL_COMPRESS.

I just can't see what I'm doing wrong here.

Hi 'A Reader'

In your example you changed the name of the ORDSYS.ORDAudio variable from 'obj' in the documentation example to 'v_src_audio' but you didn't change this line:

obj.getContentInLob(ctx,v_compressed_audio);

needs to be

v_src_audio.getContentInLob(ctx,v_compressed_audio);

Thats why you are getting the error

8/9 PLS-00302: component 'GETCONTENTINLOB' must be declared

And then your next problem will be not declaring the two OUT variables required to hold the mime type and format.

HTH

Chris

Hi Tom, Chris -

Thanks for that insight, it's very useful.

I give your suggestions a try, thanks again.

Paulie (forgot to put it in last time)

Hi tom,


How can we compress an image using Orcle 10g


Thnks in advance

in python,how go doing(select,insert,update,delete) python?

Tom,

In 10gR2.
1. Could you suggest a way to determine if image stored in blob already compressed or not; maybe there header string (hex, ascii) that would indicate that?
2. Is there a way to find file type stored inside blob (.doc, .jpg, .pdf, ..)?

Thanks in advance

Hi tom ,

I have around 15 Tb of High resolution images which i need to store in Database i am using 11g,by using secure file where i can use compression i want to know how blob compress in secure files.

Hi Tom,

I am planning to store VARCHAR(4000) columns compressed into BLOBs. I've read the full thread but note it dates back many years. I just wondered if your followup dated 4th Aug, 2005, to Doug's question, would be any different now in 2010?

Are there any other compression/decompression functions, ones that work on character data? (Haven't found any.)

On a related note, is there anything similar to DBMS_UTILITY.GET_HASH_VALUE that guarantees a unique return value? It could be a character return value instead of a numeric. (Haven't found one.)

I am currently working with a 10.2.0.4 db but *may* be given an 11g one. I note that the documentation for UTL_COMPRESS looks the same in 11gR2 as it does in 10gR2.

Thanks in advance,
Clark.

ops$tkyte%ORA10GR2> create table t ( x varchar2(4000), y blob )
  2  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          l_data varchar2(4000) := dbms_random.string( 'a', 4000 );
  3          l_raw  blob;
  4  begin
  5          l_raw := utl_compress.lz_compress( cast( utl_raw.cast_to_raw( l_data ) as blob) );
  6          insert into t (x,y)
  7          values ( l_data, l_raw );
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select length( x ), dbms_lob.getlength(y) from t;

 LENGTH(X) DBMS_LOB.GETLENGTH(Y)
---------- ---------------------
      4000                  2933

I think in all likelihood, you are right, the savings might not outweight the benefits - but - I think your example is a little flawed. A random string of characters doesn't exhibit much in the way of patterns the way that real data does. I tried your example, but with 3980 characters copied from this thread in place of a random string, and the compression came in at about 50% original size (1932 bytes to be exact) instead of c.75% original size.

Also, the very first followup mentions 'inlining' the lob storage to avoid using a block for every lob and just using the actual size of the compressed data.

How is that achieved? Is it a db config parameter or an option on the CREATE TABLE statement?

Finally, I know it's not quite on the same subject, but is there anything similar to DBMS_UTILITY.GET_HASH_VALUE that guarantees a unique return value?

Many thanks,
Clark.

ops$tkyte%ORA11GR2> create table t ( x clob );

Table created.

ops$tkyte%ORA11GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T"
   (    "X" CLOB
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"
 LOB ("X") STORE AS BASICFILE (
  TABLESPACE "USERS" <b>ENABLE STORAGE IN ROW </b>CHUNK 8192 RETENTION
  NOCACHE LOGGING )

So if I understand correctly ... if you're storing BLOBs OUT OF LINE as SecureFiles and using Advanced Compression on those out of line BLOBs, there is no benefit at all and probably the BLOBs will be larger!?!?

Is that correct?

Followup February 15, 2010 - 3pm Central time zone:
... I am planning to store VARCHAR(4000) columns compressed into BLOBs. ...

but that would make them larger????


you can use securefiles, which are a supertype of lobs, they can be compressed. But, since lobs are stored in chunks when out of line (at least ONE BLOCK) and have a lob index - you would find they are going to be LARGER.

I guess I read that as "You can compress your securefile LOBs but because of the associated LOB index it will actually make the LOB larger". Sorry if I misunderstood that one.

But, if I store unstructured content inside the database then I have 2 down sides: loss of storage space because everything is in a CHUNK size so I always have wasted storage (if I have 1 million 1k files to store, then I need 1 million 8k chunks, essentially wasting roughly 7k per LOB stored in the database). the other problem I see is how to distribute these files around the world. Does Oracle ASM with SecureFiles support be used like a distributed file system and store content around the globe for quicker access? Maybe it's a bit off topic, but if you have any information on this it would be helpful.

Sorry again about the confusion

In my job I am facing 1 issue : Whenever a new product come in the product table I want to assign Flag as 'N' and if that product already exist in the product table and I assign as 'Y' and if the same product come to new state it see that it is mapped as 'Y' flag and automatically maps it to yes and i don't ever see it in product table. The whole idea behind the request is to have all new product alerts get automatically mapped to no and any exsisting codes that are already yes that come in to get automatically mapped to yes... My question is how to fixed this code. Rightnow I am doing this thing manually. But it take too much time. I don't want to manually now onwards. Can anybody help me in that my ID is sourabh782@gmail.com

I am trying the utl_compress approach for an xmltype column.
Average value for the column is around 250 Kb

xml is a good candidate for compression, but I don't get a good performance:
Original Length : 264266
Compressed Length : 129944

50% compression ratio, doesn't worth the effort.
(I tried quality = 9 and it's the same crap)

If I take that same xml, export it to file, and compress it with winrar, I get 11Kb : 3% compression ratio!!

Is there something wrong with utl_compress?
The performance difference is so huge that makes it barely usable.

At last I found it is a known bug in this version of Oracle

Bug 7337717: UTL_COMPRESS DELIVERS POOR COMPRESSION OF 1:2 AGAINST GZIP DELIVERS 1:20

Fixed in version 11.2

The bug seems to be limited to the oracle lz_compress implementation of the algorithm, as I have tested that the lz_uncompress procedure works fine with external (java) 20:1 ratio compressed blobs.

I spent quite some time looking for what I had done wrong, when a simple foot note in the online Oracle 10 utl_compress documentation stating the poor performance of the utl_compress would have saved me quite time of web surfing and sql testings.
As bad as it is issuing this utl_compress in this poor shape, it is far worse not to point it in the documentation.

utl_compress functionality suggested in original answer looks quite useful!
But in 10.2.0.3 it gives bad compress ratio due to bug #7337717 (fixed in 11.2).

N.B.: I tested this on BLOB holding about 700Mb of text data (flat handoff file). UTL_COMPRESS gives 1:3 compression rate, comparing to 1:10 by WinZip in "fastest mode" and 1:16 by WinRAR in "good mode".

Well, 1:3 is better then 1:1, so we are going to implement usage of utl_compress even while we are in 10g.
But we plan to migrate to v 11 next year.

So, the concern is whether UTL_COMPRESS in 11.2 (with the bug fixed) will be able to uncompress BLOBs that were compressed by UTL_COMPRESS in 10G (affected by the bug) or not?
I understand that we'll need to re-compress existing data manually to get better rate... but will we be able to so using only 11.2's UTL_COMPRESS?

Thanks & regards,
Mike

I have been using the PL/SQL wrapped Java compression utility to great affect. We have some very large extracts 1gig uncompressed that compress extremly well to 50Mb using the java compress (Using 10g so PL/SQL utl_compress does not really work)

I tried to improve the code provided by adding a new procedure java wrapped in pl/sql to append 32k raw's to a compressed blob in place.

This was to remove the need for the large uncompressed blob. It appears that PL/SQL java wrapping in the database does not allow non static methods. Any way around this? as the blob needs to be stored "java" side in order to add more 32k chunks to the end.

Hi, I am the original author of this Java-PL/SQL thing. I am flattered that this lives for so many years. I DO believe my idea and code was WRONG so seriously wrong because of the size it was only some 16k < 32k somewhat artificial limit for varchar2(?) raw(?). In short it was wrong!


I am going to provide you the C code that is doing better than Oracle utl_compress (no bugs), and alternative versions in Java which does comparaively.

Best regards,

Piotr

In 10g, the package utl_compress ... will this also regain space back from LOB content being removed?

we have a table with X million rows, each rows has a LOB field containing a file. Various rows from this table are deleted all the time BUT we dont get back the space from the LOB data that is removed!

I wanted to know if this utl_compress (or some other way) to compress or regain back this space.

btw we also ran into an issue (Ora-600) because (we think) the LOB index became too large and errors out... we would also love to rebuild or compress this too? almost run utl on the table that will remove/regain dead data and rebuilt LOB indexes ( ORA-600 [25027] ) - have you seen where this LOB index gets too large?

I'm using this block of code to compress files residing on my linux box.

DECLARE
in_filename VARCHAR2(100);
src_file BFILE;
v_content BLOB;
v_blob_len INTEGER;
v_file utl_file.file_type;
v_buffer RAW(32767);
v_amount BINARY_INTEGER := 32767;
v_pos INTEGER := 1;
BEGIN
src_file := bfilename('MY_DIR', in_filename);
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
v_content := utl_compress.lz_compress(src_file, 9);
v_blob_len := dbms_lob.getlength(v_content);
v_file := utl_file.fopen('MY_DIR',
in_filename || '.zip',
'wb');
WHILE v_pos < v_blob_len LOOP
dbms_lob.READ(v_content, v_amount, v_pos, v_buffer);
utl_file.put_raw(v_file, v_buffer, TRUE);
v_pos := v_pos + v_amount;
END LOOP;
utl_file.fclose(v_file);

EXCEPTION
WHEN OTHERS THEN
IF utl_file.is_open(v_file) THEN
utl_file.fclose(v_file);
END IF;
RAISE;
END;

A file of 9MB gets compressed to approx 6MB which is then emailed to users via utl_smtp.
Users are unable to open this file using winzip upon email receipt. Winzip raises an error saying: "File is unrecoverable / not a valid zip file". The file can be opened using WinRaR though.
However, as majority of the users have winzip installed on their machines, attachments fail to open.

Could you let me know what am i doing wrong here, and how to rectify it?

Regards,
Vaibhav

Hi Tom,
I want defalte compression algorithm for compressing my blob.
as utl_compress.lz uses lempel zeiv algorithm.
Because from my table a subscriber accepts only defalte compressed blobs.

will u plz hep?

Does utl_compress required Advanced Compression Option?

I saw MOS and other authoritative sources and not found affirmative information about this question, only controversial from non-authoritative sources.

Can you help?

Hello, I have a follow-up question on compressing CLOB column in a table.
I assume that that is a program to do the compression. And let's assume that it is UTL_COMPRESS. When looking at the signature of subprogram in 12c documentation, the src LOB is always is BFILE, raw or BLOB.
So somehow, the CLOB would have to be converted to BLOB first. But when I look DBMS_LOB.converttoblob ( again another assumption) the procedure needs a few input information such as BLOB_CSID and LANG_CONTEXT.
Now my question: how does Oracle ensure that during this chain of conversion: CLOB to BLOB, BLOB to compressed and reverse, no information is lost?

Of course, I may be worrying to much and Oracle just dump to CLOB somehow to file and compress the file, put it back into the CLOB column.