title: Oracle+Spring Security Authentication: SQLException: Invalid column index
body: <p>I'm using Spring MVC+Security and Oracle 10g. When I try authenticate I get the following error:</p>
  
  <pre><code>Error : PreparedStatementCallback; invalid ResultSet access for SQL [SELECT PAYGATEMANAGER.AUTHENTICATION_PKG.getUser(?) FROM DUAL]; nested exception is java.sql.SQLException: Invalid column index 
  </code></pre>
  
  <p>security.xml:</p>
  
  <pre><code>&lt;authentication-manager&gt;
      &lt;authentication-provider&gt;
          &lt;jdbc-user-service data-source-ref="dataSource"
              users-by-username-query=
                  "SELECT PAYGATEMANAGER.AUTHENTICATION_PKG.getUser(?) FROM DUAL"
              authorities-by-username-query=
                  "SELECT PAYGATEMANAGER.AUTHENTICATION_PKG.getAuthorities(?) FROM DUAL" /&gt;
      &lt;/authentication-provider&gt;
  &lt;/authentication-manager&gt;
  </code></pre>
  
  <p>PL/SQL:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>