Archives

Thursday, August 27, 2020

 Script to Zip a file from the attachment in BLOB format in an Oracle table

DECLARE
    CURSOR c IS
        SELECT a.document_id, c.file_data, c.file_id
          FROM fnd_attached_documents a, fnd_documents b, fnd_lobs c
         WHERE     entity_name = 'PO_VENDORS'
               AND a.document_id = b.document_id
               AND b.media_id = c.file_id
               AND b.url IS NULL;

    l_original_blob       BLOB;
    l_compressed_blob     BLOB;
    l_compressed_file     UTL_FILE.file_type;
BEGIN
    FOR i IN c
    LOOP
     exit when c%NOTFOUND;
        l_compressed_file := UTL_FILE.fopen ('<directory>', i.file_id, 'wb'); -- WB (Write in byte mode)
        l_original_blob := i.file_data;
        l_compressed_blob := TO_BLOB ('1');
        -- Compress the data.
        UTL_COMPRESS.lz_compress (l_original_blob, l_compressed_blob);
        UTL_FILE.put_raw (l_compressed_file, l_compressed_blob);
        UTL_FILE.fclose(l_compressed_file);
    END LOOP;
END;

No comments:

Post a Comment