Solved

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

  • 4 August 2022
  • 2 replies
  • 152 views

Userlevel 5
Badge +12

Hi All.

Is there any solution for signatures from MWO (Aurena Natives - MaintEngApp), that are more then 32767k Clob field (since they are stored in Clob “Picture _Data”)?

In data base shows OK or partial. But After convert to Blob, it does not show in crystal reports.

Signatures under 32767k works well.

Tks a lot for help

 

 

FUNCTION Convert_Base64_Clob_To_Blob(
   clob_value_ IN CLOB) RETURN BLOB
IS
  blob_value_ BLOB;
  raw_        RAW(32767);
  buffer_  PLS_INTEGER := 32767;
  start_   PLS_INTEGER := 1;
BEGIN
   DBMS_LOB.createtemporary (blob_value_, FALSE, DBMS_LOB.CALL);

   FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(clob_value_) / buffer_) LOOP
      raw_    := UTL_ENCODE.base64_decode(UTL_RAW.cast_to_raw(DBMS_LOB.SUBSTR(clob_value_, buffer_, start_)));
      DBMS_LOB.append (blob_value_, TO_BLOB(raw_));
      start_ := start_ + buffer_;
   END LOOP;
  RETURN blob_value_;
END Convert_Base64_Clob_To_Blob;

 

=========================================================

 

PROCEDURE Convert_Blob_To_Base64(
   clob_result_  IN OUT NOCOPY CLOB,
   blob_val_     IN BLOB)
IS
   l_length_ NUMBER;
   l_buffer_ CLOB;
   l_step_   NUMBER := 3*1024;
   l_converted_text_ VARCHAR2(32767);
BEGIN
   IF (blob_val_ IS NULL OR NVL(Dbms_Lob.GetLength(blob_val_),0) = 0) THEN
      clob_result_ := empty_clob();
      RETURN;
   END IF;      
   l_length_ := Dbms_lob.getlength(blob_val_);
   dbms_lob.createtemporary(l_buffer_, TRUE, dbms_lob.call); 

   FOR i_ IN 0 .. (trunc((l_length_ - 1 )/l_step_)) LOOP
     l_converted_text_ := utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_lob.substr(blob_val_, l_step_, i_ * l_step_ + 1)));
     dbms_lob.writeappend(l_buffer_, length(l_converted_text_), l_converted_text_);
   END LOOP; 
   
   IF clob_result_ IS NULL THEN
      dbms_lob.createtemporary(clob_result_, TRUE, dbms_lob.call); 
   END IF;
   dbms_lob.append(clob_result_, l_buffer_);
   dbms_lob.freetemporary(l_buffer_);         
END Convert_Blob_To_Base64;

icon

Best answer by lopespetro 5 August 2022, 17:12

View original

2 replies

Badge +1

I'm happy to see a particularly subject. MyAARPMedicare

Userlevel 5
Badge +12

Hi All.

With @rafgbr, we could sove this problem using the same logical as for Jt_Task_Signature view.

The function Clob_to_blob was created to use in Jt_Task_Survey_Answers view.

The Blob for the eforms signatures works with this:

ifspet.JT_TASK_SIGNATURE_API.Clob_Base64_Blob(REPLACE(a.picture_data,
                                                              'data:image/png;base64,',
                                                              '')) Signature_Blob

This way, all of them works well in crystal reports even with more than 32k

Tks a lot!

Br.

Lopes

Reply