Eclipse Community Forums: EclipseLink » Nested REF

Help

Home

Home » Eclipse Projects » EclipseLink » Nested REF_CURSOR

Show: Today's Messages :: Show Polls :: Message Navigator

Nested REF_CURSOR [message #1697099]

Mon, 01 June 2015 14:51

Craig Jones

Messages: 5
Registered: December 2014

Junior Member

Hi,

i have a ref_cursor that returns a table type made up of an object type that contains some basic fields, but also another object type (test structure below).

I was looking for any hints on how i can read this from a REF_CURSOR returned from a stored procedure. I have tried JPA annotations @Enbedded/@Embeddable on the Java objects i created to replicate the database object types but it doesn't seem to read the nested object (it just gets set as null).

Any help on the best way to approach this would be appreciated.

nested_tab_type:
CREATE OR REPLACE TYPE nested_tab_type AS TABLE of nested_obj_type

nested_obj_type:
CREATE OR REPLACE TYPE nested_obj_type AS OBJECT
(
id NUMBER(10),
last_updated_timestamp TIMESTAMP(6),
values values_tab_type
)

values_tab_type:
CREATE OR REPLACE TYPE values_tab_type AS TABLE OF values_obj_type

values_obj_type:
CREATE OR REPLACE TYPE values_obj_type AS OBJECT
(
value_id NUMBER(10),
action_percentage NUMBER(7, 5),
)

[Updated on: Mon, 01 June 2015 14:52]

Report message to a moderator

Re: Nested REF_CURSOR [message #1697226 is a reply to message #1697099]

Tue, 02 June 2015 14:49

Craig Jones

Messages: 5
Registered: December 2014

Junior Member

I found a solution to this using @Array, @Embeddable and @Struct annotations.

Create objects to replicate the cursor structure, something like...

The top object has @Array with databaseType of the returned

@Entity
public class TopObject implements Serializable {
	
	private static final long serialVersionUID = -4489292941065930993L;

	@Id
	@Column(name="id")
	private long id;

	@Column(name="last_updated_timestamp")
	@Temporal(TemporalType.TIMESTAMP)
	private Date last_updated_timestamp;
	
	@Array(databaseType = "nested_tab_type")
	private List<EmbededValues> rule_values;

        ... getters and setters

The EmbeddedValues object has @Embeddable and @Struct

@Embeddable
@Struct(name="values_tab_type", 
	fields={"value_id","action_percentage"})
public class NestedSuggestedActionRuleValues implements Serializable {
	
	private static final long serialVersionUID = -5623263194285184453L;

	@Column(name="value_id")
	private long value_id;
	
	@Column(name="action_percentage")
	private double action_percentage;
	
        ... getters and setters

In the query you can then just call query.getResultList() at the appropriate time

Report message to a moderator