Support Oracle Result Set - mhewedy/spwrap GitHub Wiki

Oracle Database doesn't return result set from the following call to JDBC callable statement:

callableStatement.getResultSet();

However, You need to register the result set as Output parameter of type CURSOR and then call callableStatement.getObject method to get the result set.

spwrap starting with version 0.0.17 will supports returning result sets from Oracle, but the output parameter that holds the result set need to be the last output parameter in the stored procedure (just before the status parameters).

Example Stored Procedures:

CREATE PROCEDURE list_customers(rs OUT SYS_REFCURSOR, code OUT NUMBER, msg OUT VARCHAR2)
AS
   	BEGIN
    	open rs for SELECT id, first_name firstname, last_name lastname FROM CUSTOMERS;
     	code := 0;
  	END;

CREATE PROCEDURE list_customers_with_date(db_date OUT DATE, rs OUT SYS_REFCURSOR, code OUT NUMBER, msg OUT VARCHAR2)
AS
   	BEGIN
    	open rs for SELECT * FROM CUSTOMERS;
     	db_date := SYSDATE;
     	code := 0;
  	END;

CREATE PROCEDURE list_tables(rs OUT SYS_REFCURSOR)
AS
  	BEGIN
  		open rs for SELECT table_name FROM all_tables;
     	code := 0;
   	END;

NOTE: Again, the output parameter that will hold the result set (the rs in the previous examples) need to come the latest parameter in the parameter list, but just before the status fields (if your stored procedure choose to have the status fields)

Now to call the stored procedure there's nothing special, use the regular way that is illustrated in many places in the wiki pages.

Example:

@Mapper(CustomResultSetMapper.class)
@StoredProc("list_customers")
List<Customer> listCustomers();

@Mapper(DateMapper.class)
@StoredProc
Tuple<Customer, Date> list_customers_with_date();

See complete example at spwrap-examples.