Oracle+Spring Security Authentication: SQLException: Invalid column index

I'm using Spring MVC+Security and Oracle 10g. When I try authenticate I get the following error:

Error : PreparedStatementCallback; invalid ResultSet access for SQL [SELECT PAYGATEMANAGER.AUTHENTICATION_PKG.getUser(?) FROM DUAL]; nested exception is java.sql.SQLException: Invalid column index 

security.xml:

<authentication-manager>
    <authentication-provider>
        <jdbc-user-service data-source-ref="dataSource"
            users-by-username-query=
                "SELECT PAYGA开发者_如何学运维TEMANAGER.AUTHENTICATION_PKG.getUser(?) FROM DUAL"
            authorities-by-username-query=
                "SELECT PAYGATEMANAGER.AUTHENTICATION_PKG.getAuthorities(?) FROM DUAL" />
    </authentication-provider>
</authentication-manager>

PL/SQL:

CREATE OR REPLACE PACKAGE PAYGATEMANAGER.AUTHENTICATION_PKG AS
TYPE T_REFCURSOR IS REF CURSOR;

  FUNCTION getUser(username IN VARCHAR2) RETURN T_REFCURSOR;  
  FUNCTION getAuthorities(username IN VARCHAR2) RETURN T_REFCURSOR;

END AUTHENTICATION_PKG;

CREATE OR REPLACE PACKAGE BODY PAYGATEMANAGER.AUTHENTICATION_PKG AS 

FUNCTION getUser(username IN VARCHAR2) 
    RETURN T_REFCURSOR IS
        userInfo T_REFCURSOR;
BEGIN

    OPEN userInfo FOR
    SELECT
        U.NAME AS username ,
        P.PASSWORD AS password,
        'true' AS enabled            
    FROM
        PAYGATEMANAGER.USERS U INNER JOIN PAYGATEMANAGER.PASSWORDS P
        USING(USER_ID)
    WHERE 
        U.NAME = username;

RETURN userInfo;
END;

FUNCTION getAuthorities(username IN VARCHAR2) 
    RETURN T_REFCURSOR IS
        userAuthorities T_REFCURSOR;
BEGIN

    OPEN userAuthorities FOR
    SELECT
        U.NAME AS username ,
        UR.ROLE AS authorities           
    FROM
        PAYGATEMANAGER.USERS U INNER JOIN PAYGATEMANAGER.USERS_ROLES UR
        USING(USER_ID)
    WHERE 
        U.NAME = username;

RETURN userAuthorities;
END;

END AUTHENTICATION_PKG;

I think the reason is in return type T_REFCURSOR, which is not fetching into username, password, enabled. But how to fix this? To add some functionality in future (e.g. login attempts audit) I think I should do it in PL/SQL functions, not hard coding in SQL.

---

This definitely won't work with the "out of the box" JdbcDaoImpl (which is what <jdbc-user-service> maps to). I would suggest reviewing the source code of the standard implementation, and then writing your own implementation which implements the Spring Security UserDetailsService, but uses JDBC and CallableStatement under the covers.

You will declare your implementation class as a regular spring bean:

 <bean id="MyPlsqlUserDetailsService" class="my.great.JdbcCallableStatementImpl"/>

... and then use the ref attribute on <user-details-service> as follows:

 <user-details-service ref="MyPlsqlUserDetailsService"/>

(Note that I'm omitting the intricacy of XML namespaces etc etc, hopefully you understand those).

Good luck!