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);
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.