Laurent Schneider

I have been asked yesterday how to read and write blobs in the database.

With java :
read from an input stream

InputStream myBlobInputStream = 
  connection . 
    createStatement() . 
      executeQuery("select myBlob from t") . 
        getBlob(1) . 
          getBinaryStream();

write to an output stream

OutputStream myBlobStream = 
  connection .
    createStatement() .
      executeQuery("select myBlob from t for update") . 
        getBlob(1) . 
          getBinaryOutputStream();

where connection is your java.sql.connection.

You can find a working demo here http://www.oracle.com/…/LOBSample.zip

You could also use PL/SQL and the DBMS_LOB API. There is a complete book (306 pages!) in the doc about working with large objects : Application Developer’s Guide - Large Objects.

Sometimes, you can use plain SQL.

SQL> create table t(x BLOB);
Table created

SQL> insert into t values(utl_raw.cast_from_number(1));
1 row inserted

SQL> select utl_raw.cast_to_number(x) from t;
UTL_RAW.CAST_TO_NUMBER(X)
-------------------------
                        1

A smart move may be to use SQL Loader. You can specify one file per row

LOAD DATA INFILE '/tmp/x.txt' INTO TABLE "T"
(name filler char(255), x lobfile(name) terminated by EOF)

and your import file /tmp/x.txt will look like

x.gif
y.gif

but you could also load a 10000 long characters column from your input file in a CLOB column, just by specifying VARCHARC(4,10000) as a datatype