Oracle  column BLOB data type image compression and conversion

Sukku13
Sukku13 used Ask the Experts™
on
I have a oracle table in which a column has image stored in BLOB datatype. Image types stored are TIFF,JPG,GIFF etc . I want to move data from this table and insert into another table with a BLOB column but i want to convert all images to only JPEG format and i want to compress the image size to 20KB and store in new table.

Please advise how to achieve this using some Oracle plsq program
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA

Commented:
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.

Author

Commented:
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
johnsoneSenior Oracle DBA

Commented:
Did you look at the documentation?  There is example code in there to do almost exactly what you are asking to do.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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
johnsoneSenior Oracle DBA

Commented:
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.

Author

Commented:
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;
/

Author

Commented:
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
johnsoneSenior Oracle DBA

Commented:
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.

Author

Commented:
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
johnsoneSenior Oracle DBA

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

Author

Commented:
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;
johnsoneSenior Oracle DBA

Commented:
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.

Author

Commented:
do u mean the compression ratio

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial