Skip to main content
Question

Error In Converting CLOB To BLOB


Forum|alt.badge.img+6

Hi  Team,


We need the signature to be included in the report.

Issue Description:
We attempted to convert CLOB  to a BLOB in the backend. However, during the conversion from CLOB to BLOB, we encountered the following error.
Note: The variable picture_data was tested using both Long Text and Binary data types, but the issue persists.
Note: This implementation is currently working in the SQL developer Studio.

Error:

Code:
SELECT question, answer, survey_id, date_created, CASE WHEN picture_data IS NOT NULL THEN 
JT_TASK_SIGNATURE_API.Clob_Base64_Blob(REPLACE(picture_data,'data:image/png;base64,',''))
 ELSE NULL END AS picture_data FROM Jt_Task_Survey_Answers WHERE task_seq = 113;

Thanks,

Venkat

3 replies

InfFilipV
Hero (Partner)
Forum|alt.badge.img+12
  • Hero (Partner)
  • 225 replies
  • May 16, 2025

Hi,
here is few implemetations of convert methods, which should work. 

FUNCTION Blob_To_Clob___(blob_ BLOB)
   RETURN CLOB
IS
   temp_    VARCHAR2(32767);
   offset_  PLS_INTEGER := 1;
   length_  PLS_INTEGER := 32767;
   clob_    CLOB;
BEGIN
   DBMS_LOB.CreateTemporary(clob_, TRUE);

   FOR i_ IN 1 .. CEIL(DBMS_LOB.GetLength(blob_) / length_)
   LOOP
      temp_ := UTL_RAW.Cast_To_Varchar2(DBMS_LOB.Substr(blob_, length_, offset_));
      DBMS_LOB.WriteAppend(clob_, LENGTH(temp_), temp_);
      offset_ := offset_ + length_;
   END LOOP;
   RETURN clob_;
END Blob_To_Clob___;

PROCEDURE Blob_To_Clob____
IS
   temp_    VARCHAR2(32767);
   offset_  PLS_INTEGER := 1;
   length_  PLS_INTEGER := 32767;
BEGIN
   DBMS_LOB.CreateTemporary(clob_, TRUE);

   FOR i_ IN 1 .. CEIL(DBMS_LOB.GetLength(blob_) / length_)
   LOOP
      temp_ := UTL_RAW.Cast_To_Varchar2(DBMS_LOB.Substr(blob_, length_, offset_));
      DBMS_LOB.WriteAppend(clob_, LENGTH(temp_), temp_);
      offset_ := offset_ + length_;
   END LOOP;
END Blob_To_Clob____;

PROCEDURE Clob_To_Blob____(src_ VARCHAR2, dst_ VARCHAR2)
IS
   temp_    VARCHAR2(32767);
   offset_  PLS_INTEGER := 1;
   length_  PLS_INTEGER := 32767;
BEGIN
   DBMS_LOB.CreateTemporary(blob_, TRUE);

   WHILE offset_ < DBMS_LOB.GetLength(clob_)
   LOOP
      length_ := DBMS_LOB.Instr(clob_, CHR(10), offset_) - offset_ + 1;
      IF length_ <= 0 THEN
         length_ := DBMS_LOB.GetLength(clob_) - offset_ + 1;
      END IF;
      temp_ := DBMS_LOB.Substr(clob_, length_, offset_);
      temp_ := REPLACE(temp_, src_, dst_);
      DBMS_LOB.WriteAppend(blob_, LENGTH(temp_), UTL_RAW.Cast_To_RAW(temp_));
      offset_ := offset_ + length_;
   END LOOP;
END Clob_To_Blob____;

BR
Filip


InfFilipV
Hero (Partner)
Forum|alt.badge.img+12
  • Hero (Partner)
  • 225 replies
  • May 16, 2025

You can also follow some of these methods:

 

in Report Designer you can use Database stored image.

https://docs.ifs.com/techdocs/Foundation1/050_development/025_operational_reporting/050_layout_design/020_layout_design/100_working_with_images/default.htm#:~:text=Working%20with%20images%20stored%20in%20database

Select must be from VIEW and column must be BLOB.

In your case should be used this:

SELECT data FROM BINARY_OBJECT_DATA_BLOCK where blob_id = Person_Info_API.Get_Picture_Id('IFSUSER');


Or from hardcoded Base64 ( or from db stored base64)

 

https://www.base64-image.de/

Max 32000 chars

select to_blob(UTL_ENCODE.base64_decode(UTL_RAW.cast_to_raw('iVBORw0KGgoAAAANSUhEUgAAADAAAAAwCAMAAABg3Am1AAAA21BMVEUAAAA/UbVAUbRAUrVBULRBUrZCU7VDULFFUK9FVrdFV7dHUK5IWrhLUKpMXrlPYLpQT6VST6NUZLtZTp1Zab1bbL5fTZZicr9jTZNqecJrTItse8JygMR0S4N8isd9S3p/SnmGk8qMSW2Qnc2ZR2GaptClR1SlsdSos9SvuteyRUi2wdi4RUO5w9m/ydvDQznFz9zH0N3L1N7NQi/P2N/R2ODT2+HV3eHV3uHWQSbW3uLX3+LZQSPhQBzoQBXtPxHwPg70Pgr3PQj6PQX9PQL9PgH+PQH/PQD///8u2j7wAAAAAXRSTlMAQObYZgAAAAFiS0dESPAC1OoAAACnSURBVEjH7cvFDUJRAATAt7i7u37c3Z3tvyMagIQ9kjD3MebvV+Unu68ZY4xBoLnVApAdiwG++loLQHooBnjrKy0AyYEY4KoutQAkemKAs7TQAhDriAH20lwLQLgtBqAwFQNCLTFE+nznY6icqYToiBSCrXahEuIzUgiO2pVKSG1IIbgbNyohsyeF4LHuVELuQArBbz2ohOKRFEKw+6QSyidSCn+/6AVZJ/lj6Iu4TgAAAABJRU5ErkJggg=='))) from dual

SQL


Forum|alt.badge.img+6
  • Author
  • Sidekick (Partner)
  • 10 replies
  • May 16, 2025

Hi ​@InfFilipV ,

Thanks for your response the exact requirement is in the link below. I saw your comment over there and tried it, that's when I got the above error. 

MWO Signature Clob>Blob more that 32767k - not showing in Crytal reports | IFS Community

Thanks

Venkat


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings