There are a couple of ways to do this. If the file resides on the database server itself -- perhaps the easiest method is to just use DBMS_LOB.LOADFROMFILE and let the server do all of the work:
create table demo
( id int primary key,
theBlob blob
)
/
create or replace directory my_files as '/export/home/tkyte/public_html';
create or replace procedure load_a_file( p_file in varchar2 )
as
l_blob blob;
l_bfile bfile;
begin
insert into demo values ( 1, empty_blob() )
returning theBlob into l_blob;
l_bfile := bfilename( 'MY_FILES', p_file );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_blob, l_bfile,
dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
end;
/
would give you a procedure to call to load a file into the database. It also shows you how to create a BFILE as it created one temporarily to load the file. I could have just inserted l_bfile instead of writing l_blob if I wanted the files "external" from the database.
If the file is not on the database server itself, loadfromfile won't work, we can use a stream instead. Here is an example. We'll use a BLOB to serialize an object into (and out of) the database. The concept is identical for images, documents -- any binary data.
This example shows:
o creating an object
o serializing it into the database
o reading it back out
o printing it.
The second, more robust example with comments is a little more
flexible. It will serialize a java instance into the database
if passed no inputs. After it puts it in -- it'll print out the
"id" for that object instance. Later, you can run this same
java example and pass it that id -- it'll read out that object
instance and print it out, showing how to make this work across
invocations.
Things you will need to run this example in this case are:
create sequence java_obj_seq;
create table java_objects(
id number,
classname varchar2(2048),
bytes blob default empty_blob()
)
We use the sequence to create a "primary key" for out object
instances. We need this number later to retrieve a specific
instance. The table is what we use to store the object
instance. Here we store the classname of the original java instance in a varchar2 field and in the BLOB we'll store the
bytes of the serialized object.
Here is the first small example:
import java.lang.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
public class serial2 {
static String genID =
"select java_obj_seq.nextval from dual";
static String writeSQL =
"begin insert into java_objects(id,classname,bytes) "+
" values (?,?,empty_blob()) " +
" return bytes into ?; end;";
static String readSQL =
"select bytes from java_objects where id = ?";
public static long write(Connection conn, Object o)
throws Exception
{
long id = nextval(conn);
String className = o.getClass().getName();
CallableStatement stmt = conn.prepareCall(writeSQL);
stmt.setLong(1, id);
stmt.setString(2, className);
stmt.registerOutParameter(3, java.sql.Types.BLOB);
stmt.executeUpdate();
BLOB blob = (BLOB) stmt.getBlob(3);
OutputStream os = blob.getBinaryOutputStream();
ObjectOutputStream oop = new ObjectOutputStream(os);
oop.writeObject(o);
oop.flush();
oop.close();
os.close();
stmt.close();
System.out.println("Done serializing " + className);
return id;
}
public static Object read(Connection conn, long id)
throws Exception
{
PreparedStatement stmt = conn.prepareStatement(readSQL);
stmt.setLong(1, id);
ResultSet rs = stmt.executeQuery();
rs.next();
InputStream is = rs.getBlob(1).getBinaryStream();
ObjectInputStream oip = new ObjectInputStream(is);
Object o = oip.readObject();
String className = o.getClass().getName();
oip.close();
is.close();
stmt.close();
System.out.println("Done de-serializing " + className);
return o;
}
private static long nextval(Connection conn)
throws SQLException
{
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(genID);
rs.next();
long id = rs.getLong(1);
rs.close();
stmt.close();
return id;
}
public static void main(String[] argv)
throws Exception
{
String cs = "jdbc
racle
ci8:@ora8idev";
String user = "scott";
String pass = "tiger";
DriverManager.registerDriver(new OracleDriver());
Connection conn =
DriverManager.getConnection(cs, user, pass);
conn.setAutoCommit(false);
LinkedList l = new LinkedList();
l.add("This");
l.add("is");
l.add("a");
l.add("test");
l.add(new Long(123123123));
l.add(new java.util.Date());
long id = write(conn, l);
conn.commit();
System.out.println("ID= " + id);
System.out.println("Object= " + read(conn, id));
conn.close();
}
}
Now, if we run the code:
$ /usr/java1.2/bin/java serial2
Done serializing java.util.LinkedList
ID= 1
Done de-serializing java.util.LinkedList
Object= [This, is, a, test, 123123123, Sun Jan 21 09:45:21 EST
2001]
So, according to that, the object has been serialized and stored
in the database. In fact, we can log in using sqlplus and see
it if we like:
scott@DEV816> create or replace function clean( p_raw in blob )
return varchar2
2 as
3 l_tmp long default utl_raw.cast_to_varchar2(
dbms_lob.substr(p_raw,2000,1));
4 l_char char(1);
5 l_return long;
6 begin
7 for i in 1 .. length(l_tmp)
8 loop
9 l_char := substr( l_tmp, i, 1 );
10 if ( ascii(l_char) between 32 and 127 )
11 then
12 l_return := l_return | | l_char;
13 else
14 l_return := l_return | | '.';
15 end if;
16 end loop;
17 return l_return;
18 end;
19 /
Function created.
scott@DEV816> scott@DEV816> select id, classname,
2 dbms_lob.getlength(bytes) len,
3 clean(bytes) clean
4 from java_objects
5 /
ID CLASSNAME LEN CLEAN
---------- -------------------- ---- --------------------
1 java.util.LinkedList 191 ....sr..java.util.Li
nkedList.)S]J`."...x
pw.....t..Thist..ist
..at..testsr..java.l
ang.Long;.....#....J
..valuexr..java.lang
.Number...........xp
.....V..sr..java.uti
l.Datehj..KYt....xpw
.....1...xx
So, we see now how to serialize using a BLOB as the input stream
and deserialize using the BLOB again. We can also review the
contents (sort of) in SQLPlus.
UTL_RAW might not be installed on your system -- it comes with
Oracle7.1.6 and up. To install if you do not have it:
o cd $ORACLE_HOME/rdbms/admin
o connect as SYS or INTERNAL only using SVRMGRL
o @utlraw
o @prvtrawb.plb
Now for the longer, more robust example with error handling and
comments. You have all you need above -- the following is just
a little more sophisticated. After you compile this:
$ /usr/java1.2/bin/javac serial.java
we can run:
$ /usr/java1.2/bin/java serial 1
Done de-serializing java.util.LinkedList
Object= [This, is, a, test, 123123123, Sun Jan 21 09:45:21 EST
2001]
(after running the first example serial2.java that is). All
this did was read out our existing java instance and "rebuild
it" for us and then print it out. You can run serial without
any arguments to create new instances of the linked list and
read them back out by ID later. Here is the code:
import java.io.*;
import java.lang.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
public class serial {
static String genID =
"select java_obj_seq.nextval from dual";
static String writeSQL = "begin insert into java_objects "+
" (id,classname,bytes) values (?,?,empty_blob()) "+
" return bytes into ?; end;";
static String readSQL =
"select bytes from java_objects where id = ?";
// We attempt to serialize the object to the database table.
// We use a
// sequence number to identify the object and return that
// value to the
// caller. We also save the class name in case someone wants
// to search
// for objects based on that name. If we get any exception,
// we rethrow
// it to the caller but we always try to clean up after
// ourself.
public static long write(Connection conn, Object o)
throws Exception
{
ObjectOutputStream oop = null;
CallableStatement stmt = null;
OutputStream os = null;
long id = nextval(conn);
String className = o.getClass().getName();
try {
stmt = conn.prepareCall(writeSQL);
stmt.setLong(1, id);
stmt.setString(2, className);
stmt.registerOutParameter(3, java.sql.Types.BLOB);
stmt.executeUpdate();
BLOB blob = (BLOB) stmt.getBlob(3);
os = blob.getBinaryOutputStream();
oop = new ObjectOutputStream(os);
oop.writeObject(o);
oop.flush();
} catch (Exception e) {
throw e;
} finally {
if (oop != null) { oop.close(); }
if (os != null) { os.close(); }
if (stmt != null) { stmt.close(); }
System.out.println("Done serializing " + className);
}
return id;
}
// We attempt to de-serialize the object from the database
// table using
// the given identifier. If we get any exception, we rethrow
// it to the
// caller but we always try to clean up after ourself.
public static Object read(Connection conn, long id)
throws Exception
{
ObjectInputStream oip = null;
PreparedStatement stmt = null;
InputStream is = null;
Object o = null;
String className = null;
try {
stmt = conn.prepareStatement(readSQL);
stmt.setLong(1, id);
ResultSet rs = stmt.executeQuery();
// Even though we expect only one row back, the caller
// could have
// passed an invalid identifier so we wrap this in a
// loop to
// make sure we don't get null pointer exceptions. In
// the case
// where there are 0 rows, we would return a null
// value. Where
// there are > 1 rows, we would return only the first
// one.
while (rs.next()) {
is = rs.getBlob(1).getBinaryStream();
oip = new ObjectInputStream(is);
o = oip.readObject();
className = o.getClass().getName();
break;
}
} catch (Exception e) {
throw e;
} finally {
if (oip != null) { oip.close(); }
if (is != null) { is.close(); }
if (stmt != null) { stmt.close(); }
System.out.println("Done de-serializing " + className);
}
return o;
}
// Get the next sequence value. Ideally, this would be better
// if
// we just created a prepared statement and repeatedly used
// that.
// Obviously, we must assume the same connection is always
// used.
private static long nextval(Connection conn)
throws SQLException {
long id = -1;
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(genID);
while (rs.next()) {
id = rs.getLong(1);
}
} catch (SQLException e) {
throw e;
} finally {
if (rs != null) { rs.close(); }
if (stmt != null) { stmt.close(); }
}
return id;
}
// Here we just simply connect to the database and either
// serialize or de-serialize an object. If we get an
// exception then we will rollback whatever we have done.
// Commit changes as necessary and close the connection.
public static void main(String[] argv) throws Exception {
long id;
Connection conn = null;
String cs = "jdbc
racle
ci8:@ora816dev";
String user = "scott";
String pass = "tiger";
try {
DriverManager.registerDriver(new OracleDriver());
conn = DriverManager.getConnection(cs, user, pass);
conn.setAutoCommit(false);
if (argv.length == 0) {
id = write(conn, new java.util.Date());
conn.commit();
System.out.println("ID= " + id);
LinkedList l = new LinkedList();
l.add("This");
l.add("is");
l.add("a");
l.add("test");
id = write(conn, l);
conn.commit();
System.out.println("ID= " + id);
} else {
id = Long.parseLong(argv[0]);
Object o = read(conn, id);
System.out.println("Object= " + o);
}
} catch (Exception e) {
e.printStackTrace();
if (conn != null) { conn.rollback(); }
} finally {
if (conn != null) { conn.close(); }
}
}
}
