Is it possible to resize a blob image?

Question

I've got a table with an image blob field in it, which stores jpg files.

As those jpg files are shown in a web page, I would like to resize them in order to spend less bandwidth when my web page call them.

First, I thought I could resize a jpg file, stored in blob field, when doing a select statement and using command ORDimage.process. But it seems it's not possible.

CREATE OR REPLACE FUNCTION ORGPOLV2.resize_img (i_cod_op INTEGER)
 RETURN blob
 IS
 v_photo blob ;
 BEGIN
 select img_simbolo_op
 into v_photo
 from tbl_op
 where cod_op=i_cod_op for update;
 ordimage.process(v_photo, 'maxScale=32 32');
 update tbl_op set img_simbolo_op = v_photo where cod_op=0;
--
commit;
 select img_simbolo_op
 into v_photo
 from tbl_op
 where cod_op=0;
 RETURN v_photo;
 END resize_img;
/

With this function, I got error:

ORA-14551: cannot perform a DML operation inside a query 
ORA-06512: at "RESIZE_IMG", line 10

My question is: Is it possible to resize a jpg file in a blob field?

Maybe my approach with ordimage.process is wrong and there is another way.

• Michael Kutz: As long as you are not calling that function from a SELECT statement, I feel that your code should work.

Answer 1

Try this:

CREATE OR REPLACE FUNCTION resize_img (i_ID INTEGER)
 RETURN BLOB
IS
 vImageData BLOB;
 vSizedImage BLOB;
BEGIN
 SELECT img_simbolo_op
 INTO vImageData
 FROM tbl_yourtable
 WHERE my_ID = i_ID; 
 DBMS_Lob.createTemporary(vSizedImage, FALSE, DBMS_LOB.CALL);
 ORDSYS.OrdImage.processCopy(vImageData, 'maxScale=75 75', vSizedImage);
 return vSizedImage;
END resize_img;

• user_odoo: Do yo have any example for oracle 19c?