Table Blob to Oracle Directory

 1DECLARE
 2   l_file          UTL_FILE.file_type;
 3   l_buffer        RAW (32767);
 4   l_amount        BINARY_INTEGER := 32767;
 5   l_position      INTEGER := 1;
 6   l_blob_length   INTEGER;
 7BEGIN
 8   FOR l_cnt IN (SELECT filename, product_image FROM demo_product_info)
 9   LOOP
10      l_file := UTL_FILE.fopen ('MY_DIR', l_cnt.filename, 'WB');
11
12      -- Start sending file to Oracle directory      l_blob_length := DBMS_LOB.getlength (l_cnt.product_image);
13      WHILE l_position < l_blob_length
14      LOOP
15         DBMS_LOB.read (l_cnt.product_image,
16                        l_amount,
17                        l_position,
18                        l_buffer);
19         UTL_FILE.put_raw (l_file, l_buffer, TRUE);
20         l_position := l_position + l_amount;
21      END LOOP;
22
23      UTL_FILE.fclose (l_file);
24
25      IF UTL_FILE.is_open (l_file)
26      THEN
27         UTL_FILE.fclose (l_file);
28      END IF;
29   END LOOP;
30EXCEPTION
31   WHEN OTHERS
32   THEN
33      IF UTL_FILE.is_open (l_file)
34      THEN
35         UTL_FILE.fclose (l_file);
36      END IF;
37END;

 1DECLARE
 2   l_file          UTL_FILE.file_type;
 3   zip             BLOB;
 4   l_buffer        RAW (32767);
 5   l_amount        BINARY_INTEGER := 32767;
 6   l_position      INTEGER := 1;
 7   l_blob_length   INTEGER;
 8BEGIN
 9   l_file := UTL_FILE.fopen ('MY_DIR', 'Products.zip', 'WB');
10
11   -- Zipping the files   FOR l_cnt IN (SELECT filename, product_image FROM demo_product_info)   LOOP      apex_zip.add_file (p_zipped_blob   => zip,                         p_file_name     => l_cnt.filename,                         p_content       => l_cnt.product_image);   END LOOP;
12
13   -- FInish Zip File   apex_zip.finish (p_zipped_blob => zip);
14
15   -- Start sending file to Oracle directory   l_blob_length := DBMS_LOB.getlength (zip);
16   WHILE l_position < l_blob_length
17   LOOP
18      DBMS_LOB.read (zip,
19                     l_amount,
20                     l_position,
21                     l_buffer);
22      UTL_FILE.put_raw (l_file, l_buffer, TRUE);
23      l_position := l_position + l_amount;
24   END LOOP;
25
26   UTL_FILE.fclose (l_file);
27
28   IF UTL_FILE.is_open (l_file)
29   THEN
30      UTL_FILE.fclose (l_file);
31   END IF;
32EXCEPTION
33   WHEN OTHERS
34   THEN
35      IF UTL_FILE.is_open (l_file)
36      THEN
37         UTL_FILE.fclose (l_file);
38      END IF;
39END;