Mapping Oracle collection in Java/Hibernate

H

Hole

Hi there,

I have a stored procedure returning an Oracle collection (a nested
table of object and not simple arrays).

Do you know how to map it in Java or, preferibally, Hibernate?

The collection type looks like this:

<code>
create or replace TYPE SAMPLED_VALUE AS OBJECT
( data_timestamp date,
data_value number(8,2)
);

create or replace TYPE SAMPLED_VALUES_ARRAY AS TABLE OF SAMPLED_VALUE;
</code>

While the stored procedure is declared as:

<code>
create or replace
PROCEDURE EXTRACTOR_EXTRACT(
id IN NUMBER, dateFrom IN DATE, dateTo IN DATE, step VARCHAR2,
valuesMap OUT NOCOPY SAMPLED_VALUES_ARRAY, error_code OUT NOCOPY
NUMBER, error_description OUT NOCOPY VARCHAR2
) AS
....
....
</code>

Can you suggest me a solution?
Thanks in advance.
 
H

Hole

I don't know about Hibernate, but calling PL/SQL from Java is discussed
in the "Java Developer's Guide":

<http://download.oracle.com/docs/cd/E11882_01/java.112/e10588/
chseven.htm#CACJDIAC>
Thanks John,

in fact I would know how to map an "Oracle collection of objects" to
the Java side. It seems that there is a lack of documentation on it
(I've found someone suggesting to use oracle.sql.STRUCT class to map
an Oracle object in Java but it's not so clear how to map a "nested
table" oracle type).

In Hibernate, I found that the right way to map a collection of Oracle
objects is, first of all, implements the UserType interface to create
a custom mapping:

https://www.hibernate.org/261.html
 
J

John B. Matthews

Hole said:
in fact I would know how to map an "Oracle collection of objects" to
the Java side. It seems that there is a lack of documentation on it
(I've found someone suggesting to use oracle.sql.STRUCT class to map
an Oracle object in Java but it's not so clear how to map a "nested
table" oracle type).

I see that mentioned in "JDBC Developer's Guide,"
"16 Working with Oracle Collections" under
"Using a Type Map to Map Array Elements":

<http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/oraarr.htm#i1049179>
 
H

Hole

in fact I would know how to map an "Oracle collection of objects" to
I see that mentioned in "JDBC Developer's Guide,"
"16 Working with Oracle Collections" under
"Using a Type Map to Map Array Elements":

<http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/oraarr.h...>

Great! Thanks again, John.
It seemed strange that no official documentation was provided for such
a thing...I tried to search for docs using different key words
(limitation of non-ontological search engines :p).
 
H

Hole

I see that mentioned in "JDBC Developer's Guide,"
Great! Thanks again, John.
It seemed strange that no official documentation was provided for such
a thing...I tried to search for docs using different key words
(limitation of non-ontological search engines :p).

Hi there,

only to post working code that solved my question.
Perhaps, there is room to improve the code either the design solution.

<code>
public List<SampledValue> getResults() {
//int progress = 0;
List<SampledValue> svList = new ArrayList<SampledValue>();

try {
CallableStatement cs = conn.prepareCall(CALL_SP);
cs.setBigDecimal(1, this.virtualId);
cs.setTimestamp(2, new Timestamp(dateFrom.getTime()));
cs.setTimestamp(3, new Timestamp(dateTo.getTime()));
cs.setString(4, this.dateTimeStep);
cs.setString(5, this.variables);

//the third parameter is the SQL_TYPE name of the NESTED
TABLE
//as declared in Oracle:
//create or replace TYPE SAMPLED_VALUES_ARRAY AS TABLE OF
SAMPLED_VALUE;
cs.registerOutParameter(6, Types.ARRAY,
"SAMPLED_VALUES_ARRAY");

cs.execute();


ARRAY a = (ARRAY) cs.getObject(6);
//from the ARRAY object, you need to get a ResultSet...
ResultSet rs = a.getResultSet();

while (rs.next()) {
//the first "column" is the row number while the
second is the real Oracle object
//Oracle objects are mapped as java.sql.STRUCT
STRUCT object = (STRUCT) rs.getObject(2);
//BigDecimal row = (BigDecimal) rs.getObject(1);

//get attributes from the Oracle object
Object[] attrs = object.getAttributes();

//you need how to map Oracle object into your java
object
//
// create or replace TYPE SAMPLED_VALUE AS OBJECT
// ( data_timestamp date,
// data_value number(8,2)
// );
SampledValue sv = new SampledValue();
sv.setDateTimeUtc((Timestamp) attrs[0]);
sv.setDataValue((BigDecimal) attrs[1]);
svList.add(sv);

}
cs.close();
} catch (Exception exc) {
svList = null;
exc.printStackTrace();
}
return svList;

}

</code>
 
G

Gunter Herrmann

Hi!
Hi there,

only to post working code that solved my question.


There is an alternative solution:

Rewrite your PL/SQL code to return the nested table from a function
(table function or Oracle9++ pipelined table function).

Then your query would simply be:

select * from TABLE(myTableFunction(parameter1, ...)

8i syntax: table(cast( ... as tabletype

Best regards

Gunter
 
H

Hole

Hi!



There is an alternative solution:

Rewrite your PL/SQL code to return the nested table from a function
(table function or Oracle9++ pipelined table function).

Then your query would simply be:

select * from TABLE(myTableFunction(parameter1, ...)

8i syntax: table(cast( ... as tabletype

Best regards

Gunter

Thanks Gunter,

I'll take a look at it and see if it helps me to simplify the design
(if so, I will apply a refactor cycle)...
At the moment, I call the SP simply by using this:

public final static String CALL_SP = "{call EXTRACTOR_EXTRACT
(?,?,?,?,?,?)}";

Perhaps, using that solution, I would be able to use my Hibernate
layer...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top