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

Question

I've tried to insert BLOB data with SQL developer. But I can't find the insert statements which are actually used to insert BLOB data.

Apart from that, the database speed is really slow. For small files, it executes fine. But when I tried to import 50 mb avi file into BLOB, it took 3-4 minutes & still it was not completed. When I tried to export the BLOB data to a file, exporting process was also slow. I was using Oracle 10g Express Edition. If the database speed is slower than even file-system speed, then why database are used for storing BLOB data? Is there any other way to optimize the performance?

Solution 1

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.

Solution 2

This is not a code you can use with your sql-developer, it's Java code that uses a StringBuffer in order to INSERT a blob. Hope it will be useful:

private void addBlob(oracle.jdbc.OracleConnection oracleConn, StringBuffer content) throws Exception
{    
      PreparedStatement st = null;
      try {                
            oracle.sql.BLOB blob = oracle.sql.BLOB.createTemporary(oracleConn, true, oracle.sql.BLOB.DURATION_SESSION);

            blob.setBytes(1, content.toString().getBytes("UTF-8"));

            st = oracleConn.prepareStatement("INSERT INTO MYTABLE (id, content) VALUES (MYTABLE_S.NEXTVAL, ?)");
            st.setBlob(1, blob);
            st.execute();
            st.close();
        }
        catch (Exception e) {
            utils.writeLog("Blob insertion Failed", e, utils.ERR);
            throw e;
        }        
        finally{
            st.close();
        }
  }

Comments

[Nir Alfasi]

Sometimes you want to be able to access the (exact) same file from different machines in different locations. If you have one central DB it could be a good solution (otherwise you'll have to sync each file to all the machines and deal with possible sync issues).

[Eugen Rieck]

@alfasin: This is exactly what the first bullet covers: Unavailability of a (shared) file system. In effect you replace a file system with a database.