Search code examples
stored-proceduresjdbcoracle-cursor

How do I return a sys_refcursor from oracle SP in java?

I have a stored procedure (SP) in oracle:

CREATE OR REPLACE 
PROCEDURE "SP_SEL_LOGIN_INFO" (
p_username IN varchar2,
p_ResultSet OUT sys_refcursor
) AS

begin

OPEN p_ResultSet FOR
SELECT * FROM user_accounts
WHERE p_username = username;

end;

In my java class I have the following lines of code to call the SP:

currentCon = connectionpackage.ConnectionManager.getConnection(dbSource);
CallableStatement stmt = currentCon.prepareCall("{call SP_SEL_LOGIN_INFO(?, ?)}");
stmt.setString(1, username); 
stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
stmt.execute();
rs = stmt.getCursor(2);

That is what I have found online to call a SP and return a cursor. When I try to compile I get the following two errors:

error: cannot find symbol stmt.registerOutParameter(2, OracleTypes.CURSOR); AND error: cannot find symbol rs = stmt.getCursor(2); Where it says it cannot find OracleTypes and getCursor.

I tried importing import oracle.jdbc.driver.*; or import oracle.jdbc.*; and got the errors error: package oracle.jdbc does not exist import oracle.jdbc.driver.*; and error: package oracle.jdbc does not exist import oracle.jdbc.*; respectively.

I also have the ojdbc14.jar file in the proper folder and can connect using a query string. It is just when trying to use the SP that it is giving me trouble.

The SP is one we use on our current CF website, so I would like to just reuse it as is. Could someone please shed some light on why this may not be working? Or if there is an alternative bit of code to use to return a cursor from an Oracle SP? Thanks.

Solution

  • Please try this, it might solve the issue.

    Replace this

    rs = stmt.getCursor(2);

    with

     rs = ((OracleCallableStatement)stmt).getCursor(2);

    Besides, for calling procedure, use the below statement.

    CallableStatement stmt = conn.prepareCall("BEGIN SP_SEL_LOGIN_INFO(?, ?); END;");