CODEWITHSUNDEEP
javaoracle-databasefunctionejbsys-refcursor
gogagubi
gogagubi

Reputation: 1015

can't get oracle function result into java(ejb)

I have oracle function which returns sys_refcursor.

create or replace function get_person_salaries(p_year  in number,
                                                   p_month in number)
      return sys_refcursor

and I want to call from ejb(java). how to do this? how can i get all the result list of sys_refcursor?

Thanks

Upvotes: 0

Views: 315

Answers (2)

garfield
garfield

Reputation: 595

Since you need to use Entity Manager, if your container is EE7 compliant, then JPA 2.1 should be supported, in this case you can create a JPA query that invokes a stored procedure. An example from PRO JPA book that seems similar to your needs, is as follows, :

StoredProcedureQuery q = em.createStoredProcedureQuery("fetch_emp");
q.registerStoredProcedureParameter("empList", void.class, ParameterMode.REF_CURSOR);
if (q.execute()) {
List<Employee> emp = (List<Employee>)q.getOutputParameterValue("empList");
// ...
}

Also like @NamedQuery, in JPA 2.1 you can create @NamedStoredProcedure as follows:

@NamedStoredProcedureQuery(
name="fetch_emp",
procedureName="fetch_emp",
parameters={
@StoredProcedureParameter(name="empList", type=void.class,
mode=ParameterMode.REF_CURSOR)
},
resultClasses=Employee.class)

Upvotes: 1

vssk
vssk

Reputation: 465

I think something like that should work:

Connection conn = getConnection(); 
CallableStatement call =
  conn.prepareCall ("{ ? = call java_refcursor.job_listing (?, ?)}");

call.registerOutParameter (1, OracleTypes.CURSOR); // this is a key line
call.setString (2, arg1);
call.setString (3, arg2);
call.execute ();
ResultSet rs = (ResultSet)call.getObject (1); // Just casting sys_refcursor to ResultSet
while (rs .next ())
  System.out.println (rs.getString(1));

The way to get connection in ejb:

@Stateless
public class YourBean {

    @Resource(lookup = "java:/jdbc/yourdatasource")
    private DataSource dataSource;

    public Connection getConnection() throws SQLException {
       return connection = dataSource.getConnection());        
    }
}

Upvotes: 1

Related Questions