Hello!!!

Please give me a direction!!

I have to run an Oracle stored procedure for doing a patient de-duplication. The procedure has as many as 18 joins. Once the procedure is called, I need to display all the results on a JSP page. Finding problems to read the result set from a stored procedure.

I have been reading the "Hibernate in Action". I could not find much information regarding the "READ" from a stored procedure.
I need to provide the POC for the OUT parameters for the stored proc.
Updating, creating and deletion was possible with the stored procs.

Thanks a lot.

stored procedure read support is only in cvs yet and very experimental. You can always use plain JDBC through session.connection()

Thanks for the update Micheal.

Pl take a minute to see one of my stored proc.

create or replace procedure testresults_report
(p_condition health_related_activity.condition_code%type, --input parameter
p_report_status health_related_activity.hra_status%type, --input parameter
p_completed_start_date disease_report.date_started%type, --input parameter
p_completed_end_date disease_report.date_completed%type, --input parameter
p_community_area postal_location.community_area_code, --input parameter
p_priority health_related_activity.priority_level, --input parameter
p_age person.age%type, --input parameter
p_refcur in out sys_refcursor) --output parameter (result set)

is
v_stmt varchar2(2000) := select hra.hra_id, en.given_name, phe.entity_id, pe.birth_date,
hra.condition_code, pl.community_area_code, en.entity_id, hra.priority_level,
dr.date_completed, dr.date_started, (select ien.last_name||', '||ien.given_name
from health_related_activity iact, activity_relationship ar,
public_health_entity iphe, entity_name ien, entity_hra_participation iehp
where iact.hra_id = ar.hra_id1
and ar.hra_id2 = hra.hra_id
and ar.act_rel_type_code = 'PERTAINS'
and iact.hra_id = iehp.hra_id
and iehp.part_type_code = 'INVESTIGATOR'
and iehp.entity_id = iphe.entity_id
and iphe.entity_id = ien.entity_id ) assigned_to_name,
row_number() over (order by phe.entity_id) cnt,
dense_rank() over (order by phe.entity_id) rnk
from health_related_activity hra, disease_report dr, postal_location pl,
public_health_entity phe, person pe, entity_name en,
entity_hra_participation ehp,
public_health_entity phe2, entity_name auth, entity_hra_participation ehp2,
public_health_entity phe3, entity_name org, entity_role er
where dr.hra_id = hra.hra_id
and hra.hra_id = ehp.hra_id
and ehp.entity_id = phe.entity_id
and ehp.part_type_code = 'SUBJECT'
and phe.entity_id = pl.entity_id
and en.entity_id = phe.entity_id
and phe.entity_id = pe.entity_id
and ehp.hra_id = ehp2.hra_id
and ehp2.entity_id = phe2.entity_id
and ehp2.part_type_code = 'AUTHOR'
and auth.entity_id = phe2.entity_id
and phe2.entity_id = er.entity_id1
and er.entity_id2 = phe3.entity_id
and er.role_class_code = 'EMPLOYEE'
and phe3.entity_id = org.entity_id

This is just one of the reports, we have. To do this as a plain JDBC, it would be very tuff. Would you have an idea, as when this read support can be released.

Thanks a lot!!!!!

in hibernate3 you will be able to do this soon - but the stored procedure must return the resultset - not just via a output parameter.

see http://opensource.atlassian.com/project ... se/HHH-238 for the hopefully soon to be integrated patch.

-max

Thanks a lot MAX.
I guess this would be a great help for the Hibernate developers.
Nice to know that the patch is released today. eventually it would evolve and be as efficient as the other parts of Hibernate. Shall try working on it.

Great job and thanks for all the help.