Our problem is that we want to use a stored procedure to collect and return
a more complex structure.
For example, it should be possible to return a list of orders
where each row in the list inlcudes a number of order rows.
We have tested this with an Oracle Stored Procedure
Code:
PROCEDURE test
(v_result OUT SYS_REFCURSOR)
IS
v_order_list order_list := order_list();
v_order_row_list order_row_list := order_row_list();
BEGIN
v_order_row_list.extend(1);
v_order_row_list(1) := order_row_obj(4712,'text1');
v_order_row_list.extend(1);
v_order_row_list(2) := order_row_obj(4713,'text2');
v_order_list.extend(1);
v_order_list(1) := order_obj(4711,'text3',v_order_row_list);
OPEN v_result FOR
SELECT * FROM TABLE (v_order_list);
END test;
ORDER_OBJ and ORDER_ROW_OBJ are defined like this:
Code:
CREATE OR REPLACE TYPE "ORDER_ROW_OBJ"
AS OBJECT (ORDER_ROW_ID NUMBER, ORDER_ITEM VARCHAR2(100))
/
CREATE OR REPLACE TYPE "ORDER_ROW_LIST"
AS TABLE OF "ORDER_ROW_OBJ"
/
CREATE OR REPLACE TYPE "ORDER_OBJ"
AS OBJECT (ORDER_ID NUMBER, ORDER_TITLE VARCHAR2(100), ORDER_ROWS ORDER_ROW_LIST)
/
CREATE OR REPLACE TYPE "ORDER_LIST"
AS TABLE OF "ORDER_OBJ"
/
How can we retrieve this kind of data structure in a hibernate request?
We have tried this:
Code:
@Entity
@NamedNativeQuery (
name = "Order.test",
query = "{call test(?)}",
resultClass = Order.class,
hints = {
@QueryHint(name = "org.hibernate.callable", value = "true"),
@QueryHint(name = "org.hibernate.readOnly", value = "true")
}
)
public class Order {
@Id
@Column(name = "order_id")
private Integer id;
@Column(name = "order_title")
private String title;
//Getters and Setters
}
The code above works, but how can we retrieve the order_rows?
Is it possible for hibernate to map this kind of structure?
Is there a recommended solution for this problem?
We are running JBoss 5.1 and Oracle 10g, Release 2, Enterprise Edition.
Login
FAQ
Search