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.

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();        }  }