Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Newcomers » Newcomers » ref_cursor in select with @NamedStoredProcedureQuery(How )
ref_cursor in select with @NamedStoredProcedureQuery [message #1794546] Sun, 02 September 2018 05:00
Daniel Araujo is currently offline Daniel AraujoFriend
Messages: 2
Registered: September 2018
Location: Brazil
Junior Member
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

Previous Topic:Cannot use the "Generate JavaFX getters and setters"
Next Topic:Crash
Goto Forum:
  


Current Time: Wed Nov 14 04:17:55 GMT 2018

Powered by FUDForum. Page generated in 0.03543 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top