Skip to main content

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

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

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

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


Terms & ConditionsCookie settings