Oracle column BLOB data type image compression and conversion

A quick documentation search gives some references.   It is going to depend on your Oracle version.

Oracle 11.2
Oracle 12.2
Oracle 18


In 12 and 18, there appears to be an ORD_IMAGE package which will probably handle what you need.

In 11, there is an ORDImage object type, but it doesn't look like it has any features that you would need.

I cannot find any reference in 19 to that package, so either it moved somewhere I cannot see it, or they took it out.

Normally, this is the type of thing that would be done outside the database.  Oracle doesn't care what you put in the BLOB, it just wants to store it and give it back to you.  You might be best served by writing a piece of code to do this rather than trying to do it in the database.

Hi John thanks for response. Our version is oracle 12. I am not an expert code writer so I am looking for a piece of stub. In fact why we want to do in database is that we dont want to run a code to extract 100,000 plus images to a folder and then wriTe another to upload it. Rather we are looking to migrate table to table. So lets say I have a table IMG_SOURCE with columns EMPID,IMGID,IMGDATA(BLOB) - The data of IMGDATA i.e images stored are of multiple image formats like i said GIFF,TIFF,JPEG etc and sizes greater than 20KB . Now my target table IMG_TARGET having columns EMPID,IMGID,IMGDATA_COMPRSSD(BLOB) - IMGDATA_COMPRSSD Column i want to extract from column IMGDATA in source table but data to be extracted and loaded in size under 20KB and image format of all images to be coverted to JPG during that extraction load

Did you look at the documentation?  There is example code in there to do almost exactly what you are asking to do.

John no i didnt find code...can u paste it here

I need first bit of code to convert any image format to jpg and store and then want to run a second bit of code to compress and store it

There are different versions of the routines and I have no idea which one you would need.  I'm surprised that you couldn't find the sample code, it is included with just about every call.  Try going here and scrolling down a half a screen.  No idea if that is the correct routine for what you are trying to do, but that shows an example.

of the below i beleive will convert to one format to anotherDECLARE

DECLARE
   source_image BLOB;
   dest_image BLOB;
BEGIN
   SELECT img INTO source_image FROM timg WHERE N=1910;
   SELECT img INTO dest_image FROM timg WHERE N=2402 FOR UPDATE;
   
   -- convert source image to PNG
   ORDSYS.ORD_IMAGE.convert(source_image, 'PNG', dest_image);

   UPDATE timg SET img=dest_image WHERE N=2402;
   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
        RAISE;
END;
/

or
DECLARE
   image BLOB;
BEGIN
   SELECT img INTO image FROM timg WHERE N=2402 FOR UPDATE;
   
   -- convert image to JFIF and update in place
   ORDSYS.ORD_IMAGE.convert(image, 'JFIF');

   UPDATE timg SET img=image WHERE N=2402;
   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
        RAISE;
END;
/

now after I run this code what will be the second piece of code I can run so the image will get compressed to 20KB size

Compressing is not an exact science.  You really cannot specify a desired final size and achieve it.

Most image formats are already compressed.

As your final image format is JPEG, it is already compressed.  That is the meaning of the file format.  You can try and run them through a compression routine, but they likely aren't going to get any smaller.

can u let me know what would be that compression routine code. I know JPEG is compressed but I want to ensure that the image stored in the BLOB column is not of size greater than 20KB

In the database, I would think that UTL_COMPRESS would be what you would use.

u mean something as below
procedure myProcedure as
v_blob_column blob;
 begin  
  FOR myrectype IN (SELECT * FROM  tableA)
  LOOP
  v_blob_column := utl_compress.lz_compress(myrectype.blob_column);
  insert into tableB(id,blob_column) values(myrectype.id,v_blob_column);
  END LOOP

end myProcedure;

You would have to do the convert first, that is just a compression routine.  I would also think that you would want to specify the second parameter to LZ_COMPRESS.  But, I've never used it.

do u mean the compression ratio