How do I return a sys_refcursor from oracle SP in

Question

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.

Answer 1

Kindly try this, it might solve the issue.

Replace this

rs = stmt.getCursor(2);

with

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

Besides for calling your procedure use this statement.

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

Answer 2

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?

If you would want to return only resultsets(records) then why not use a function which returns sys_refcursor than a stored procedure? Of course it doesn't make any difference if you use a procedure or function in terms of performance, as function would be a best practice as your procedure is not doing any thing other than select statement.

Regards