Why is my result of a java call to stored procedure in oracle null?-CodePudding

I have following procedure that, when called with sqlplus:

SQL> exec test_proc('test_search', :varcursor);

works just fine and gives me the expected results (a list of ids from the table).

CREATE OR REPLACE PROCEDURE test_proc
    (search_term IN varchar2,
    c1 OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN c1 for
    select test.id 
        from test_table t
        WHERE JSON_TEXTCONTAINS(t.test_col, '$', search_term);
    DBMS_SQL.RETURN_RESULT(c1);
END test_proc;

However if called from my spring application with JPA/eclipselink the result is always null.

StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("test_proc")
            .registerStoredProcedureParameter(1, String.class, ParameterMode.IN)
            .registerStoredProcedureParameter(2, Class.class, ParameterMode.REF_CURSOR)
            .setParameter(1, "test");
    // execute SP
    storedProcedure.execute(); --> true
    List<Object[]> res = storedProcedure.getResultList();

And directly over the jdbc driver it's also null.

Connection conn = DataSourceUtils.getConnection(dataSource);
    CallableStatement st = null;
    ResultSet rs = null;
    try {
        st = conn.prepareCall( "{call test_proc(?,?)}" );
        st.setString(1, "test");
        st.registerOutParameter( 2, OracleTypes.CURSOR );
        boolean hadResults = st.execute();
        System.out.println( hadResults ); --> always false
        
        rs = (ResultSet) st.getObject(2);
        while ( rs.next() )
        {        
            System.out.println( rs.getInt(1) );
        }
        st.close();
    } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

CodePudding user response：

It seems there are a couple of odd things going on here.

Firstly, your stored procedure is odd in that it attempts to return the data from the cursor as both an OUT parameter and an implicit result. I don't know whether you have control over this stored procedure, but if you do, I suggest you modify the stored procedure to return the cursor either (a) via the OUT parameter or (b) via an implicit result, but not both. (My recommendation would be to return it via the OUT parameter, by removing the call to DBMS_SQL.RETURN_RESULT.)

However, if you don't have the option to change the stored procedure, then you'll have to live with it returning an implicit result. It seems that returning the cursor via the implicit result overrides returning it via the OUT parameter.

The second odd thing is that it seems that the Oracle JDBC driver doesn't quite follow the JDBC standard for implicit results. The Oracle JDBC driver requires you to call .getMoreResults() before returning the first implicit result (Oracle provide example code here), whereas the JDBC standard states that you use .getMoreResults() to return subsequent results, suggesting that the first result should be available before you call .getMoreResults(). There's a discussion on this at the Oracle forums here.

Try calling st.getMoreResults(), check that this returns true, and then obtain the result set by calling st.getResultSet(). The following worked for me:

    Connection conn = DataSourceUtils.getConnection(dataSource);
    CallableStatement st = null;
    ResultSet rs = null;
    try {
        st = conn.prepareCall( "{call test_proc(?,?)}" );
        st.setString(1, "test");
        st.registerOutParameter( 2, OracleTypes.CURSOR );
        boolean hadResults = st.execute();
        System.out.println( hadResults ); // --> always false
        
        System.out.println(st.getMoreResults()); // printed 'true' for me.
        rs = st.getResultSet();
        while ( rs.next() )
        {        
            System.out.println( rs.getInt(1) );
        }
        st.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }