How to Write Blob from Oracle Column to the File System

How to write a BLOB from db to the OS file system?

I don't know if you need this programmatically or just as an ad-hoc operation.

Our GUI allows for this. I talk about how to work with BLOBs in Oracle SQL Developer here.

In the BLOB dialog, there's a download link you can use to save the BLOB to a local file.

I want to insert a file to Oracle using BLOB column type over Hibernate?

In first you must annotate your entity field with @javax.persistance.Lob annotation.
Like this:

public class InfoMessage {

    private byte[] body;

    @Lob
    public byte[] getBody() {
        return body;
    }

    public void setBody(byte[] body) {
        this.body = body;
    }
}

and set it with bytes array. It's depends on wich File class you use. The first google result for java.io.File. I guess there's better solution for this operation.

public static byte[] getBytesFromFile(File file) throws IOException {
    InputStream is = new FileInputStream(file);
// Get the size of the file
long length = file.length();

if (length > Integer.MAX_VALUE) {
    // File is too large
}

// Create the byte array to hold the data
byte[] bytes = new byte[(int)length];

// Read in the bytes
int offset = 0;
int numRead = 0;
while (offset < bytes.length
       && (numRead=is.read(bytes, offset, bytes.length-offset)) >= 0) {
    offset += numRead;
}

// Ensure all the bytes have been read in
if (offset < bytes.length) {
    throw new IOException("Could not completely read file "+file.getName());
}

// Close the input stream and return bytes
is.close();
return bytes;

}

Insert large xml file as as blob in oracle table

You may convert the xml file into an sql script executing a suitably crafted anonymous plsql block. Loading this script into the db will populate the blob.

The basic idea is to split the xml file into chunks of 2000 characters. The first chunk may be inserted into the target table's blob column directly. Each other will be added by an update statement taking advantage of the dbms_lob.fragment_insert package procedure. !!! WARNING: This is not recommended practice !. Better get a dba to load it for you!

Example:

• Assumptions:

  • The target table has 2 columns, the pk and the blob.
  • the pk is 42.
  • 2000 is a sample chunk size deemed suitable. Technically, dbms_lob.fragment_insert handles up to 32767, however, other tools involved (eg. sqlplus) might have tighter bounds on line length.

• Code:

  declare
      l_b BLOB;
  begin
      insert
        into
    t_target ( c_pk, c_blob )
      values ( 42, utl_raw.cast_to_raw('<This literal contains the first 2000 (chunksize) characters of the xml file>') )
   returning c_blob
       into l_b
          ;
  
   dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset 2000>'));
   dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset 4000>'));
   dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset 6000>'));
   ...
   dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains the last chunk>'));
  
   commit;
  end;
  /
  show err
  

Preparatory work

1. You need to make sure that no single quote occurs inside your xml file.
   Otherwise the generated plsql code will contain syntax errors.

   If single quotes aren't used as attribute value delimiters, simply replace them with the numerical entity &x#28;.

2. Create the bulk of the anonymous plsql

Methods for inserting data into a file at regular intervals are presented in this SO question, the most flexible approach being outlined in this answer. Instead of newlines, insert the following string inserted:

"'));\n     dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('"

The remainder of the anonymous plsql can be copied/written by hand.

Caveat

As is, the script size will be of the same magnitude as the original xml and the plsql block will contain 200k+ lines. Very likely you will run into some limitations of the tools involved. However, the script can be split into an arbitrary number of chunks as follows:

declare
    l_b BLOB;
begin
    select c_blob
      into l_b
      from t_target
     where c_pk = 42
        ;

 dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset <k>*2000>'));
 dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset (<k>+1)*2000>'));
 dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset (<k>+2)*2000>'));
 ...
 dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset (<k>+<n_k>)*2000>'));
end;
/
show err

And once again: !!! WARNING: This is not recommended practice !. Better get a dba to load it for you!

What are the ways to insert & retrieve BLOB data from Oracle database using SQL?

First of all, you should expect storing BLOBs in a database to be (sometimes a bit, often significantly) slower, but definitly not faster than storing them in a file system. The reasons to store them in a DB do not center about performance, but about e.g.:

• Unavailability of a (shared) file system in a clustered or load-balanced scenario
• Ease of backup: Single process, a.o.t. 2 processes when files and DB are used
• Transaction safety: A BLOB is either there and complete or not, but not in a half-baked stage
• others I can't think of right now.

The general rule of thumb is, that if none of these concern you, you should store your files as ... files. Storing the metadata and pathname in a DB is IMHO good and common practice.

Concerning Oracle tuning: There are books written about that. I suspect to total them far over a ton in dead-tree-paperback format. You might first of all look at the Oracle process' memory consumption - rule of thumb: If it is less than a gig and you use BLOBs, you are in trouble. Read up on the different memory pools and how to increase them. Some limits for the express edition might apply.

How do I copy a BLOB image to the filesystem

As usual, there are pro and contra for both options: keep images in the database, or keep them in a filesystem. In my opinion, if there are just a few (OK, let's measure it in ~100s) of them, I'd rather keep them in a table. It is easier to handle them. Because, once they are in filesystem, you'll have to fetch & display them in Apex, using some more (HTML) code.

Anyway, here's how I did that (in one of previous Apex versions, I believe it was 4.0): using the "File Browse" item I temporarily stored images into a table (it was WWW_FLOW_FILES; you'd rather use your own table now), and then - in process triggered by pressing a button - I moved them into a directory.

Process code:

declare
  -- l_dest_dir is name of the directory (Oracle object) which points
  -- to a filesystem directory
  l_dest_dir varchar2(30) := pkg_slike.f_dir_slike_upload_dp(:P0_DP);
begin
  for cur_r in (select id, filename 
                from wwv_flow_files
                where upper(filename) like 'TS%.JP%G' 
               )
  loop
    -- put a picture into L_DEST_DIR
    pkg_slike.p_write_blob_to_file(cur_r.id, l_dest_dir);

    delete from wwv_flow_files
      where id = cur_r.id;
  end loop;
end;

Procedure code:

   PROCEDURE p_write_blob_to_file (p_file_id IN NUMBER, p_dir IN VARCHAR2)
   IS
      /* 19.04.2012. Taken from Eddie Awad's Blog
                     http://awads.net/wp/2011/09/20/create-an-application-to-upload-files-using-oracle-apex-in-less-than-10-minutes-video/
      */
      l_blob            BLOB;
      l_blob_length     INTEGER;
      l_out_file        UTL_FILE.file_type;
      l_buffer          RAW (32767);
      l_chunk_size      BINARY_INTEGER := 32767;
      l_blob_position   INTEGER := 1;
      l_file_name       pkg_general.subt_ime_slike;
   BEGIN
      -- Retrieve the BLOB for reading
      SELECT blob_content, filename
        INTO l_blob, l_file_name
        FROM wwv_flow_files
       WHERE id = p_file_id;

      -- Retrieve the SIZE of the BLOB
      l_blob_length := DBMS_LOB.getlength (l_blob);

      -- Open a handle to the location where you are going to write the BLOB
      -- to file.
      -- NOTE: The 'wb' parameter means "write in byte mode" and is only
      --       available in the UTL_FILE package with Oracle 10g or later
      l_out_file :=
         UTL_FILE.fopen (p_dir,
                         l_file_name,
                         'wb' -- important. If ony w then extra carriage return/line brake
                             ,
                         l_chunk_size);

      -- Write the BLOB to file in chunks
      WHILE l_blob_position <= l_blob_length
      LOOP
         IF l_blob_position + l_chunk_size - 1 > l_blob_length
         THEN
            l_chunk_size := l_blob_length - l_blob_position + 1;
         END IF;

         DBMS_LOB.read (l_blob,
                        l_chunk_size,
                        l_blob_position,
                        l_buffer);
         UTL_FILE.put_raw (l_out_file, l_buffer, TRUE);
         l_blob_position := l_blob_position + l_chunk_size;
      END LOOP;

      -- Close the file handle
      UTL_FILE.fclose (l_out_file);
   END p_write_blob_to_file;

Hopefully, you'll manage to adjust it to your situation.

Using PL/SQL how do you I get a file's contents in to a blob?

To do it entirely in PL/SQL, the file would need to be on the server, located in a directory which you'd need to define in the database. Create the following objects:

CREATE OR REPLACE DIRECTORY
    BLOB_DIR
    AS
    '/oracle/base/lobs'
/

CREATE OR REPLACE PROCEDURE BLOB_LOAD
AS

    lBlob  BLOB;
    lFile  BFILE := BFILENAME('BLOB_DIR', 'filename');

BEGIN

    INSERT INTO table (id, your_blob)
        VALUES (xxx, empty_blob())
        RETURNING your_blob INTO lBlob;

    DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);

    DBMS_LOB.OPEN(lBlob, DBMS_LOB.LOB_READWRITE);

    DBMS_LOB.LOADFROMFILE(DEST_LOB => lBlob,
                          SRC_LOB  => lFile,
                          AMOUNT   => DBMS_LOB.GETLENGTH(lFile));

    DBMS_LOB.CLOSE(lFile);
    DBMS_LOB.CLOSE(lBlob);

    COMMIT;

END;
/

how to insert a huge file to BLOB (Oracle) without loading the complete file to memory?

For the ones out there...

Here is the process to get it done:

stmt.execute ("INSERT INTO my_blob_table VALUES ('row1', empty_blob())");
BLOB blob;
cmd = "SELECT * FROM my_blob_table WHERE X='row1' FOR UPDATE";
ResultSet rset = stmt.executeQuery(cmd);
rset.next();
BLOB blob = ((OracleResultSet)rset).getBLOB(2);
File binaryFile = new File("john.gif");
System.out.println("john.gif length = " + binaryFile.length());
FileInputStream instream = new FileInputStream(binaryFile);
OutputStream outstream = blob.setBinaryStream(1L);
int size = blob.getBufferSize();
byte[] buffer = new byte[size];
int length = -1;

Source: http://docs.oracle.com/cd/B19306_01/java.102/b14355/oralob.htm#CHDFHHHG

---

Related Topics