CLOB to Zip file

Question

我在Oracle数据库中有CLOB列，其中包含XML文件。

如何创建一个包含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;
/