Table Blob to Oracle Directory

How to save table images to oracle directory?

Preview

Files from table to Oracle directory

DECLARE
   l_file          UTL_FILE.file_type;
   l_buffer        RAW (32767);
   l_amount        BINARY_INTEGER := 32767;
   l_position      INTEGER := 1;
   l_blob_length   INTEGER;
BEGIN
   FOR l_cnt IN (SELECT filename, product_image FROM demo_product_info)
   LOOP
      l_file := UTL_FILE.fopen ('MY_DIR', l_cnt.filename, 'WB');

      -- Start sending file to Oracle directory      l_blob_length := DBMS_LOB.getlength (l_cnt.product_image);
      WHILE l_position < l_blob_length
      LOOP
         DBMS_LOB.read (l_cnt.product_image,
                        l_amount,
                        l_position,
                        l_buffer);
         UTL_FILE.put_raw (l_file, l_buffer, TRUE);
         l_position := l_position + l_amount;
      END LOOP;

      UTL_FILE.fclose (l_file);

      IF UTL_FILE.is_open (l_file)
      THEN
         UTL_FILE.fclose (l_file);
      END IF;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (l_file)
      THEN
         UTL_FILE.fclose (l_file);
      END IF;
END;

Preview How to Send all files in ZIP?

DECLARE
   l_file          UTL_FILE.file_type;
   zip             BLOB;
   l_buffer        RAW (32767);
   l_amount        BINARY_INTEGER := 32767;
   l_position      INTEGER := 1;
   l_blob_length   INTEGER;
BEGIN
   l_file := UTL_FILE.fopen ('MY_DIR', 'Products.zip', 'WB');

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

   -- FInish Zip File   apex_zip.finish (p_zipped_blob => zip);

   -- Start sending file to Oracle directory   l_blob_length := DBMS_LOB.getlength (zip);
   WHILE l_position < l_blob_length
   LOOP
      DBMS_LOB.read (zip,
                     l_amount,
                     l_position,
                     l_buffer);
      UTL_FILE.put_raw (l_file, l_buffer, TRUE);
      l_position := l_position + l_amount;
   END LOOP;

   UTL_FILE.fclose (l_file);

   IF UTL_FILE.is_open (l_file)
   THEN
      UTL_FILE.fclose (l_file);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (l_file)
      THEN
         UTL_FILE.fclose (l_file);
      END IF;
END;

Preview

You might Like:


comments powered by Disqus