"Serializing Java Objects into the database (and getting them back out)", version version 8.1.6
Brad Bittiker, April 30, 2001 - 8:03 pm UTC
Excellent. This is exactly what I was looking for.
Very Useful but .....
Damien Michalosky, May 31, 2002 - 11:28 am UTC
It is a great alternative to what I am actually looking for. I wanted to write a Java Stored Function that returns a Java Object. It looks like that is not possible for my research but serializing an object created by a Java Stored Procedure is possible and can be retrieved by an outside class. Any other ideas? Please post or email me.
May 31, 2002 - 1:02 pm UTC
regardless of the method -- you will have to have a serializable class (we are going from JVM to JVM after all -- not within a single JVM)
So, given that, I think this would be the most straight forward way. You can use a temporary blob instead of a table and have your java stored procedure return a blob. Your java client code will
a) connect
b) run the java stored procedure
c) get the blob back to the java client
d) deserialized it
How about serialized object to stored java procedure?
Chris Bruun, October 18, 2002 - 7:40 pm UTC
This example is great for updating an object table. How can serialized objects be passed to and deserialized by a java stored procedure?
October 18, 2002 - 8:01 pm UTC
Well, you could just use this technique -- the caller would serialize into a LOB (Temporary lob for example) and pass the LOB locator to the java stored proc.
java object
Phil, March 12, 2004 - 9:27 am UTC
is it possible to
pass a Java object to an Oracle stored procedure, the object would be compiled from an XML schema and would hold the content of an XML file. To do something ( eg create a record) a stored procedure would then extract the relevant values from the object, presumably they would be accessible as properties, then process the values ????
not possible, totally foolish ?
thanks
March 12, 2004 - 9:57 am UTC
you could theoretically serialize the java object into a blob, send it to the database, unserialized it but....
I don't see the tie in between a java "object" (instance) and XML?
If you have XML, just send XML to the db -- lots of capabilities in there to process it.
</code>
https://docs.oracle.com#index-XML <code>
phil, March 12, 2004 - 10:32 am UTC
I want to validate the the xml against an xml schema first - is that possible in Oracle?
"read the doco phil!!"
thanks
HTT and Stamps
Yves Bergeron, July 05, 2004 - 1:18 pm UTC
Hi,
I'm currently working on a new PL/SQL bridge with the Stamps framework on which Matthew S. Piermarini worked (is Mark the brother of Matthew ???).
I used to work with HTT but it's a bit constraining regarding the templates storage. As far as I know, there is currently no public PL/SQL API for Stamps, the latest incarnation of HTT.
Within that framework, there is a template cache feature but it is not functional under mod_plsql as the JVM is refreshed on each call. So, I'm trying to replace the current cache feature with a database storage of parsed templates.
I think the examples given here will greatly help me.
I will also try to adapt the way the templates are get before the parsing. We intend to use XML-DB to keep the template sources and access them though a path or a list of possible resource folders (to mimic the PHP include_path).
If anyone got any hints regarding what I'm trying to do, don't hesitate to comments.
Thank you.
BLOB creation without database connexion
Yves Bergeron, July 09, 2004 - 11:14 am UTC
Hi everybody,
Always on my Stamps PL/SQL bridge, I'm trying to serialize a java object and create a lob with it and return that lob to PL/SQL to then store it in the database.
In my situation, I use the oracle JVM under mod_plsql. So, the PL/SQL procedure is connected to the database through the DAD definition. I don't know how to use that same connexion within java as I don't know any of the connexion parameters in the DAD.
In that context, is it possible to:
1- create a temporary lob within PL/SQL
2- pass the lob pointer to java
3- serialize my class using the lob pointer
4- return to PL/SQL and save the lob in the database using the temporary lob pointer
Thank you for any comments
July 09, 2004 - 11:24 am UTC
are you talking about a java stored procedure?
Java stored procedure and blob
Yves Bergeron, July 15, 2004 - 9:54 am UTC
Yes I'm talking about Java stored procedures.
I don't have any problem passing CLOB back and forth between PL/SQL and java.
With the current version of my coding, I got the message "ORA-29532: Java call terminated by uncaught Java exception: java.io.IOException: Closed LOB" even though I open my blob before streaming something inside.
Thank you for your help.
Here is the simplified PL/SQL and java coding for my procedures:
CREATE OR REPLACE PACKAGE BODY stamps
AS
------------------------------------------------------------------------------
FUNCTION getJavaFromCache (p_templateName in varchar2)
RETURN NUMBER
AS
LANGUAGE JAVA
NAME 'stampsXMLdb.StampsContext.getTemplateFromCache(java.lang.String) return int';
------------------------------------------------------------------------------
PROCEDURE getJavaBlob (p_template_ptr in number, p_template_blob in BLOB)
AS
LANGUAGE JAVA
NAME 'stampsXMLdb.StampsContext.getTemplateBlob(int, oracle.sql.BLOB)';
------------------------------------------------------------------------------
FUNCTION get (p_template_name IN VARCHAR2) RETURN NUMBER
AS
v_template_blob blob;
v_ptr_template integer;
BEGIN
-- Instantiate the template java object
-- and get a handle on that template
v_ptr_template := getJavaFromCache(p_template_name);
-- Create an empty blob
insert into stamps_template$ (stt_id,
stt_template)
values (seq_stamps_template$.nextval,
empty_blob())
returning stt_template into v_template_blob;
-- pass blob pointer to the java proc
getJavaBlob(v_ptr_template, v_template_blob);
-- After the proc, we assume the blob was
-- updated to contain the serialized java object
RETURN v_ptr_template;
END;
END stamps;
/
package stampsXMLdb;
import netwhit.stamps.*;
import java.io.*;
import java.util.*;
import oracle.sql.*;
public class StampsContext
{
private static StampsContext stampsInstance = null;
private static Cache templateCache;
static Vector activeTemplates;
private void StampsContext()
{
}
public static synchronized void initInstance() {
if (stampsInstance == null) {
stampsInstance = new StampsContext();
stampsInstance.templateCache = Cache.getCache();
activeTemplates = new Vector();
}
}
public static int getTemplateFromCache(String templateName)
throws Exception {
initInstance();
Template template = templateCache.get(templateName);
if (template == null) {
return -1;
} else {
activeTemplates.addElement(template);
return activeTemplates.size() - 1;
}
}
public static void getTemplateBlob(int templatePtr, BLOB templateBlob)
throws Exception
{
OutputStream os = null;
ObjectOutputStream oop = null;
Template template = (Template) activeTemplates.elementAt(templatePtr);
try {
templateBlob.open(BLOB.MODE_READWRITE);
os = templateBlob.getBinaryOutputStream();
oop = new ObjectOutputStream(os);
oop.writeObject(template);
oop.flush();
oop.close();
os.close();
} catch (Exception e) {
throw e;
} finally {
if (oop != null) { oop.close(); }
if (os != null) { os.close(); }
}
}
}
July 15, 2004 - 12:57 pm UTC
if you need the connection -- you just call
16 Connection conn =
17 new OracleDriver().defaultConnection();
in your java code -- is that what you are looking for?
else, can you get the example down to something really really small -- and use "create or replace java source" so I can reproduce in sqlplus..
Simplified case
Yves Bergeron, August 02, 2004 - 8:34 am UTC
Hi Tom,
Here is a runable script exposing my problem.
drop table tb_blob;
CREATE TABLE tb_blob
(blob_id NUMBER,
blob_content blob);
DROP SEQUENCE seq_blob_id;
CREATE SEQUENCE seq_blob_id START WITH 1 INCREMENT BY 1 NOCYCLE;
CREATE OR REPLACE PACKAGE pl_blob
AS
PROCEDURE storeBlob;
END pl_blob;
/
show errors
CREATE OR REPLACE PACKAGE BODY pl_blob
AS
------------------------------------------------------------------------------
PROCEDURE setBlobContent (p_blob_content in BLOB)
AS
LANGUAGE JAVA
NAME 'javaBlobClass.setBlobContent(oracle.sql.BLOB)';
------------------------------------------------------------------------------
PROCEDURE storeBlob
AS
v_blob_content blob;
BEGIN
-- Create an empty blob
insert into tb_blob (blob_id,
blob_content)
values (seq_blob_id.nextval,
empty_blob())
returning blob_content into v_blob_content;
-- pass blob pointer to the java proc
setBlobContent(v_blob_content);
-- After the proc, we assume the blob was
-- updated to contain the serialized java object
END;
END pl_blob;
/
show errors
create or replace and resolve java source named "javaBlobClass" as
import java.io.*;
import java.util.*;
import oracle.sql.*;
public class javaBlobClass
{
private static String stringToSerialize = "string content";
private void javaBlobClass()
{
}
public static void setBlobContent(BLOB blobContent)
throws Exception
{
OutputStream os = null;
ObjectOutputStream oop = null;
try {
blobContent.open(BLOB.MODE_READWRITE);
os = blobContent.getBinaryOutputStream();
oop = new ObjectOutputStream(os);
oop.writeObject(stringToSerialize);
oop.flush();
oop.close();
os.close();
} catch (Exception e) {
throw e;
} finally {
if (oop != null) { oop.close(); }
if (os != null) { os.close(); }
}
}
}
/
show errors
After creating the preceding objects, if I do
execute pl_blob.storeBlob
I get:
BEGIN pl_blob.storeBlob; END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.io.IOException: Closed LOB
ORA-06512: at "SHQYBN_STRUTS.PL_BLOB", line 16
ORA-06512: at "SHQYBN_STRUTS.PL_BLOB", line 23
ORA-06512: at line 1
Thank you for your help.
August 02, 2004 - 9:04 am UTC
well -- it really was closed!
finally gets called on success and failure. oop/os are not null, but they are "closed"
this works:
25 oop.writeObject(stringToSerialize);
26 oop.flush();
27 oop.close();<b>
28 oop = null;</b>
29 os.close();<b>
30 os = null;</b>
31 } catch (Exception e) {
32 throw e;
33 } finally {
34 if (oop != null ) { oop.close(); }
35 if (os != null ) { os.close(); }
36 }
37 }
38 }
39 /
Java created.
ops$tkyte@ORA10G> show errors
No errors.
ops$tkyte@ORA10G> set define on
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> execute pl_blob.storeBlob
PL/SQL procedure successfully completed.
Alex, January 31, 2006 - 4:09 pm UTC
Hi Tom,
I am trying to serialize java objects and store them in blobs. I don't understand how the above example works though. Could you please explain how the "write" method works? How exactly is the serialized object being written to the database? All I see is a insert statement that inserts an id and class and returns a blank blob. I suspect the writeObject method has something to do with it but once we serialize with that, it doesn't go back to the database to populate the blob column? I'm confused. Thanks.
calling java from pl/sql (oracle)
Amit, June 20, 2006 - 12:37 pm UTC
Hi Tom,
Thanks alot for all your help and support .
Am sure if we count all the "thanks" by all your followers in your site, we can't store it in any datatype in Oracle :-)
Now coming to my query -
Is there any method provided by oracle (pl/sql) to directly call Java code
1) without loading java into oracle as java procedures
2) without using any other language like C.
If yes please suggest some method or URL.
June 21, 2006 - 9:05 am UTC
not really. No. There are "things you can do" but they all require "not directly calling java". Eg: you can use utl_tcp to talk to a java server you write and run outside of the database - but you have to pack and unpack the data on the socket and such.
cache problems with java object serialization
Jaume, June 06, 2007 - 8:08 am UTC
Hi!
I found very interesting the thread and in fact we're using the same method to serialize java object into a Oracle 9.2 DB. The problem is that sometimes in multithreading environment where each thread has is own oracle connection, we're having retrieving obsolete (or cached) BLOBs (java objects). Basically, some threads do SELECT FOR UPDATE of some row of a table with one BLOB colunm. Then, these threads uses the commented method to serialize a java object. At the same time, another thread may be waiting in another SELECT FOR UPDATE for the same row (and BLOB). When the first thread (or connection) performs the COMMIT, the waiting thread/connection gets an obsolete/cached version of the BLOB. This only happens in heavy multithreading situations.
Any idea if the JDBC driver may be caching somehow the BLOBs across connections? (we're using the oracle ojdbc14_9.2.0.8 driver). How to solve that problem?
We only manage to avoid this problem by inserting the BLOB in a temp table and updating original BLOB again from the temp table before the commit.
Any help greatly welcome.
June 06, 2007 - 1:45 pm UTC
blobs are read consistent as of the time the lob locator is selected from the database.
if you
a) read a lob locator
b) someone commits a change to that blob
c) start reading the data out based on that lob locator
the data will be read consistently as of the point in time (a), it will not see bits of information committed at (b)
it follows the same rules as cursors (replace lob locator in a,b,c with CURSOR and it would be precisely the same)
Java Objects in the database
John Flack, June 07, 2007 - 8:42 am UTC
I read this thread with some alarm - sounds like the nasty old "a database is just a place to persist my objects" paradigm. I can't think of a single good reason to store serialized objects in the database. If the data is important enough to save, it is important enough to save in a form that can use the strengths of the database, not as a Blob. You can't query a Blob, can't enforce relationships, can't join ...
Use a good O/R framework and save your data as rows and columns.
newer methods?
Alex Jacoby, July 18, 2008 - 12:36 pm UTC
Serializing java objects
Ransford, December 22, 2009 - 11:52 am UTC
Hi Tom,
This is a very interesing article and the questions and feedbacks have helped in understanding the code provided. I am trying to serialize java objects and store them in blobs, specifically I want to store an instance of a sql statement object so later I can use it to cancel a long running query from a servlet. All my attempts so far has ended in failure, not sure how to proceed. This is the error I am getting using the write method provided:
private void setObject(Object obj) throws Exception
{
class SetObjectThread extends Thread
{
private Object obj;
private String className;
private Connection conn = null;
private ObjectOutputStream oos = null;
private CallableStatement stmt = null;
private OutputStream os = null;
SetObjectThread(Object obj) {
this.obj = obj;
this.className = obj.getClass().getName();
//className = "java.sql.Statement";
}
public void run() {
String sql = "begin \n" +
" insert into dlp_java_objects \n" +
" (id, classname, bytes) \n" +
" values(?,?,empty_blob()) \n" +
" return bytes into ?; \n" +
" end; ";
try {
conn = getDbConnection();
stmt = conn.prepareCall(sql);
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();
oos = new ObjectOutputStream(os);
oos.writeObject(obj);
oos.flush();
} catch (Exception e){
log.warn("ConnectDbBean.setObject(): " + e.toString());
} finally {
if(oos != null) {
try{
oos.close();
} catch(Exception e){}
}
if(os != null){
try{
os.close();
} catch(Exception e){}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {;}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {;}
conn = null;
}
log.info("ConnectDbBean.setObject(): Done storing " + className);
}
}
}
System.out.println("ConnectDbBean.setObject(): storing statement object");
SetObjectThread t = new SetObjectThread(obj);
t.start();
}
ConnectDbBean.setObject():java.io.NotSerializableException: org.apache.tomcat.dbcp.dbcp.DelegatingStatement
December 31, 2009 - 8:35 am UTC
did you read up on the the exception being thrown?
NotSerializableException
Your object is not serializable, Object's do not have to implement the serializable interface.
http://java.sun.com/j2se/1.4.2/docs/api/java/io/NotSerializableException.html <quote>
Thrown when an instance is required to have a Serializable interface. The serialization runtime or the class of the instance can throw this exception. The argument should be the name of the class.
</quote>
and you know, even if you could save this object, I've no idea what you would do with it since... Well, no one else could see this until you commit?
Why not set up a resource profile, to restrict long running queries? If that is your goal.
Serializing java objects followup
Ransford, January 01, 2010 - 3:57 pm UTC
...
Followup December 31, 2009 - 8am Central time zone:
did you read up on the the exception being thrown?
NotSerializableException
Your object is not serializable, Object's do not have to implement the serializable interface.
http://java.sun.com/j2se/1.4.2/docs/api/java/io/NotSerializableException.html <quote>
Thrown when an instance is required to have a Serializable interface. The serialization runtime or the class of the instance can throw this exception. The argument should be the name of the class.
</quote>
and you know, even if you could save this object, I've no idea what you would do with it since... Well, no one else could see this until you commit?
Why not set up a resource profile, to restrict long running queries? If that is your goal.
I read and understood the documentation, I just wanted to see if anyone had a workaround. My application is quering against a very large database for a number of similar queries in a loop (both the query request and the cancellation is controlled from a web interface). If any of these queries are running long for whatever reason the capability is there to stop this query. Currently I store the statement handle along with the thread name in a static variable (HashMap), this works perfectly well except that if the application server (Tomcat) is shutdown the query might still be running, at that point the HashMap would be lost. It seems that this doesn't worth the effort, thanks for the great article thought.
January 04, 2010 - 10:25 am UTC
... I just wanted to see if anyone had a
workaround....
sure, use it with serializable objects?
but the database does have gobs of monitoring tools and the ability to shutdown a long running query for you automagically - no code involved.
Class cast exception when we read the object
SRam, March 16, 2014 - 6:07 pm UTC
Hello,
There is no doubt this example has helped me in completing my project. However, here is the issue
When we read the object - Object o = oip.readObject();
it throws a class cast exception when we dont have the related class. I am bit confused how did it worked for you?
Please help.