Press "Enter" to skip to content

How to Save BLOB as File in PL/SQL?

Here I am giving an example to save BLOB data as a file in PL/SQL. BLOB data you can get it from the table having BLOB column, or you can get it from a file on the disk.

PL/SQL Function Example to Save BLOB as a File

The below procedure takes the following three arguments:

  1. Oracle Directory Object name (as i_dir).
  2. Save as filename (as i_file).
  3. BLOB data (as i_blob).
CREATE OR REPLACE PROCEDURE blob_to_file (i_dir    IN VARCHAR2,
                                          i_file   IN VARCHAR2,
                                          i_blob   IN BLOB)
AS
   l_file       UTL_FILE.file_type;
   l_buffer     RAW (32767);
   l_amount     BINARY_INTEGER := 32767;
   l_pos        INTEGER := 1;
   l_blob_len   INTEGER;
BEGIN
   l_blob_len := DBMS_LOB.getlength (i_blob);

   l_file :=
      UTL_FILE.fopen (i_dir,
                      i_file,
                      'WB',
                      32767);

   WHILE l_pos < l_blob_len
   LOOP
      DBMS_LOB.read (i_blob,
                     l_amount,
                     l_pos,
                     l_buffer);
      UTL_FILE.put_raw (l_file, l_buffer, TRUE);
      l_pos := l_pos + l_amount;
   END LOOP;

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

      RAISE;
END blob_to_file;

Test

The following PL/SQL block will call the above procedure by passing the Oracle directory object, filename and BLOB data. For the BLOB data, I am using the GET_BLOB function for which I have given an example in my previous blog post: Get BLOB from a File in PL/SQL. In the below case, it will get the BLOB data from a file myfile.jpg from MY_DIR location and will save it to file abc.jpg in IMG_DIR location.

DECLARE
   f_blob   BLOB;
BEGIN
   /* check the above mentioned link for get_blob function example */
   f_blob := get_blob ('MY_DIR', 'myfile.jpg');
   /* now pass the blob to blob_to_file procedure to save it as a file */
   blob_to_file ('IMG_DIR', 'abc.jpg', f_blob);
END;

Now you can check the location of IMG_DIR directory for the file created through BLOB.

See also:

Vinish Kapoor

I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me, it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub and get notifications for new posts.

You may also like:

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *