Can Oracle DBMS return a Java object from a Java stored procedure call?

Question

Can the Oracle database return a Java object from the return values of a Java stored procedure call?

I would like to query the Oracle database with a call to a java stored procedure and receive back java objects as the results. Is this possible? If so, could someone present a very simple example?

Note: I don't want to store serialized objects in the database. I want to run the Java stored procedure, and have this procedure return a Java object. So if the database is queried, each returned record will be a Java object.

For instance: I want the Java stored procedure to parse a binary file that is stored in a network shared drive, build a Java object with the information extracted from the binary file, and return this Java object as the query result.

I want to achieve something like this:

 #Using Java or Python programming language
 results = execute( Select java_procedure_call(parameter) From dual);
 For java_obj in results:
     print java_obj.name
     print java_obj.city

Other information: I am not using Java EE.

Thanks in advance.

It is impossible to parse a binary file that is stored in a network drive by using stored procedures. — erencan
– erencan, Commented Jul 14, 2013 at 19:34

Juned Ahsan · Accepted Answer · 2013-07-14 16:57:18Z

2

What you need to do is, serialize and de-serialize java objects to and from Oracle database table. You can store the serialized object bytes in Oracle table using BLOB column type. Here is a link describing how you can store and retrieve java objects from a table.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:1285601748584

answered Jul 14, 2013 at 16:57

Juned Ahsan

68.9k12 gold badges101 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

erencan · Accepted Answer · 2013-07-14 17:16:17Z

0

You can store any serializable data in Databases from text files, ,images to Java Objects. Basic logic here,any data is serialized to a byte stream and stored in DB. They are deserialized when fetching from DB. If you really want to this you can skip my answer. However, mapping database fields to Java classes is a better solution and my explanation will be in this direction.

You shoud use a Object Relational Mapping(ORM) framework for this purpose. It can be Hibernate, JPA or Spring JDBC Template. One of them can be used according to your requirements.

If you do not want to use any framework you can basically implement it.

Implement the structure for 2 fields such as username and password. I assume that both fields are String

First, you should have a model class which will be mapped o query results.

class Model
{
     private String username;
     private String password;

    //setter and getters.
}

Secondly, you should implement a Factory pattern to create Java objects from query results.

class BasicModelFactory
{

   public List<Model> getModels(ResultSet rs)
   {
        List<Model> models = new ArrayList<Model>();
        while(rs.next())
        {
             Model m = new Model();
             m.setUsername(rs.getString("username");
             m.setPassword(rs.getString("password");
             models.add(m); 
        }
        return models;
   }

}

edited Jul 14, 2013 at 17:16

answered Jul 14, 2013 at 16:58

erencan

3,7935 gold badges34 silver badges52 bronze badges

2 Comments

zd5151 Over a year ago

Im not using JEE for example. My biggest doubt is how to return a java object as a record. I've edited the question with more information.

erencan Over a year ago

You can not return a Java object if you did not serialized while inserting.

Collectives™ on Stack Overflow

Can Oracle DBMS return a Java object from a Java stored procedure call?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related