CLOB到Zip文件

Question

在包含XML文件的Oracle DB中有CLOB列。

如何创建包含clob列中所有xml文件的单个zip文件？

表中只有一行，它具有带有xmls的CLOB列。

Answer 1

您可以利用APEX_ZIP包查询您的CLOB列，并将它们全部添加到将存储在BLOB中的zip文件中。然后，您可以使用该BLOB做您需要的任何事情。

DECLARE
    l_temp_blob      BLOB;
    l_dest_offset    INTEGER := 1;
    l_src_offset     INTEGER := 1;
    l_lang_context   INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
    l_warning        INTEGER;
    l_zip            BLOB;
    l_file_counter   PLS_INTEGER := 1;
BEGIN
    FOR i IN (SELECT entity
                FROM TB_INDEXFILES_STORE
               --Add whatever where clause you want here
               WHERE ROWNUM = 1)
    LOOP
        DBMS_LOB.CreateTemporary (lob_loc => l_temp_blob, cache => TRUE);

        DBMS_LOB.ConvertToBlob (dest_lob       => l_temp_blob,
                                src_clob       => i.entity,
                                amount         => LENGTH (i.entity),
                                dest_offset    => l_dest_offset,
                                src_offset     => l_src_offset,
                                blob_csid      => DBMS_LOB.DEFAULT_CSID,
                                lang_context   => l_lang_context,
                                warning        => l_warning);

        apex_zip.add_file (p_zipped_blob   => l_zip,
                           p_file_name     => 'file' || l_file_counter || '.xml',
                           p_content       => l_temp_blob);
                           
        l_file_counter := l_file_counter + 1;
    END LOOP;

    apex_zip.finish (l_zip);
    
    --Do whatever you want with your l_zip blob which is the zip file
END;
/