Hi,
Can i map ref_cursor in a select returned by a proc with "NamedStoredProcedureQuery" using annotations jpa, spring data and eclipselink?
Example:
My oracle procedures returns a cursor in a cursor:
CREATE OR REPLACE PROCEDURE get_emp (p_name IN VARCHAR2,p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
select
employee_id,
name,
office,
proc_get_company(company_id) as company
from
table_employee
where
name like '%' || :p_name || '%'
END;
CREATE OR REPLACE PROCEDURE proc_get_company (company_id IN NUMBER,p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
select
company_id,
company_name
from
table_company
where
company_id = :company_id
END;
My classes
@NamedStoredProcedureQuery(
name = "getEmployee",
procedureName = "get_emp",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "p_name"),
@StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class, name = "p_recordset")
})
@Entity
public class Employee {
@Column(name = "employee_id")
private long employeeId;
@Column(name = "name")
private String name;
@Column(name = "office")
private String office;
private Company company;
...
}
@Entity
public class Company {
@Column(name = "company_id")
private long companyId;
@Column(name = "company_name")
private String name;
...
}
Spring Repository
@Repository
public interface EmployeeDAO extends CrudRepository<Employee, Long>{
@Procedure(name = "getEmployee",outputParameterName="p_recordset")
public List<Employee> findEmployeeByName(@Param("p_name") String name)
}
The other fields of the "select" works but the cursor ( proc_get_company(company_id) ) on it doesn't return by annotations. :(
can you help me ?
[Updated on: Mon, 03 September 2018 18:49]
Report message to a moderator