LZ_UNCOMPRESS inn. Oracle PL/SQL does not work as expected, unable to uncompress compressed data

49 Views Asked by At

The thing which I want to achieve is using Oracle Pl/sql stored procedure - I want to compress a blob data and put that in a json.

The structure will be like. - {"Client_id" : "12345", "Blob_data" : compressed_blob_data}

I want to store the above content in a file in object storage in OCI, or we can assume that I am storing the file in local machine as .json file. I have compressed the data using UTL.LZ_COMPRESS and have put the same in compressed_blob_data BLOB variable.

But after extracting the BLOB data from JSON file when I am trying to uncompress the data present for the key "Blob_data" in the JSON file, oracle is throwing error as below-

Error report - ORA-29294: A data error occurred during compression or uncompression. ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 60

ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 230

ORA-06512: at "SYS.UTL_COMPRESS", line 89

ORA-06512: at line 32 29294. 00000 - "A data error occurred during compression or uncompression."

*Cause: An error occurred while compressing or uncompressing input source.

*Action: Verify that source data is a valid compressed or uncompressed

DECLARE
  DATASET_JSON_WITH_COMPRESSED_BLOB                 JSON_OBJECT_T := JSON_OBJECT_T();
  DATASET_BLOB_DATA_CERNER_MONITORING               BLOB;
  DATASET_BLOB_DATA_CERNER_MONITORING_BASE64_STR    CLOB;
  DATASET_JSON_WITH_COMPRESSED_CLOB                 CLOB;
  CLIENT_ID VARCHAR2(50);
  DATASET_BLOB_DATA BLOB;
  OBJECT_STORAGE_URI_CERNER_MONITORING VARCHAR2(500);
  OCI_CREDENTIAL VARCHAR2(400) := 'OCI$RESOURCE_PRINCIPAL';
  OCI_REGION VARCHAR2(50) := 'us-zzz-1';
  OBJECT_STORAGE_NAMESPACE VARCHAR2(50) := 'abcdefgh';
  BUCKET_NAME VARCHAR2(50) := 'analyticsContentTestBucket';
  l_content BLOB;
  l_content_clob CLOB;
  l_json_data JSON_OBJECT_T;
  compressed_clob CLOB;
  compressed_blob BLOB;
  uncompressed_blob BLOB;
  blob_length INT;
BEGIN

       DATASET_BLOB_DATA := UTIL.CLOB_TO_BLOB(TO_CLOB('There is BLOB data'));
       blob_length := DBMS_LOB.GETLENGTH(DATASET_BLOB_DATA);     
       DBMS_OUTPUT.PUT_LINE('Length of compressed_blob: ' || blob_length);    
       DATASET_JSON_WITH_COMPRESSED_BLOB.PUT('CLIENT_ID',CLIENT_ID);-- := '{"CLIENT_ID": ' || CLIENT_ID || ', "BLOB_DATA": '||UTL_ENCODE.BASE64_ENCODE(DATASET_BLOB_DATA)||'}';
       DATASET_BLOB_DATA_CERNER_MONITORING :=  UTL_COMPRESS.LZ_COMPRESS(SRC => DATASET_BLOB_DATA);
        
        blob_length := DBMS_LOB.GETLENGTH(DATASET_BLOB_DATA_CERNER_MONITORING);
        DBMS_OUTPUT.PUT_LINE('Length of compressed_blob: ' || blob_length);
        DATASET_JSON_WITH_COMPRESSED_BLOB.PUT('BLOB_DATA', TO_CLOB(DATASET_BLOB_DATA_CERNER_MONITORING));
        
        DATASET_JSON_WITH_COMPRESSED_BLOB.PUT('BLOB_DATA', blob_to_clob(DATASET_BLOB_DATA_CERNER_MONITORING));
        
        DBMS_OUTPUT.PUT_LINE(DATASET_JSON_WITH_COMPRESSED_BLOB.TO_CLOB);
        DATASET_JSON_WITH_COMPRESSED_CLOB := DATASET_JSON_WITH_COMPRESSED_BLOB.TO_CLOB;
        DATASET_BLOB_DATA_CERNER_MONITORING := UTIL.CLOB_TO_BLOB(DATASET_JSON_WITH_COMPRESSED_CLOB);
        DBMS_OUTPUT.PUT_LINE(DATASET_JSON_WITH_COMPRESSED_CLOB);
       OBJECT_STORAGE_URI_CERNER_MONITORING  := 'https://objectstorage.' || OCI_REGION || '.oraclecloud.com/n/' || OBJECT_STORAGE_NAMESPACE || '/b/' || BUCKET_NAME || '/o/' || 'blobdata123.json';
END;```

CLOB_TO_BLOB are written following the functions CONVERTTOCLOB and CONVERTTOBLOB respectively.
0

There are 0 best solutions below