Wally, not sure if you have figured this out already. If so then you can ignore this.
Following is the example that I use in one of my projects. Changed SQL and names, but should be simple -
CREATE OR REPLACE PACKAGE
Test AS
TYPE refCursor IS REF CURSOR;
FUNCTION getAssets (instructionId NUMBER, status VARCHAR2)
RETURN refCursor;
END;
/
----------------------------------------------------------------------------
CREATE or REPLACE PACKAGE BODY Test
AS
FUNCTION getAssets (instructionId NUMBER,
status VARCHAR2)
RETURN refCursor
IS
assetCursor refCursor;
BEGIN
OPEN assetCursor FOR
SELECT
a.instruction_id,
a.instruction_type
a.asset_number,
l.lot_number,
l.amount
FROM asset a, asset_lot l
where a.instruction_id = instructionId
and a.asset_status = status;
RETURN assetCursor;
END;
----------------------------------------------------------------------------
END;
/
DataSource ds = (DataSource)ctx.lookup("Oracle_sid");
connection = ds.getConnection();
String storeProc = "{call ? = test.getAssets(?,?)}";
CallableStatement getAssets = connection.prepareCall(storeProc);
getAssets.setLong(2,43544);
getAssets.setString(3,"CLOSED");
getAssets.registerOutParameter(1,OracleType.CURSOR); // -10
getAssets.execute();
ResultSet rs = (ResultSet)getAssets.getObject(1);
In your example I dont think there's any problem in the JDBC code that you have in ya JSP. You can only return REF CURSOR from Oracle to
java clients. Your return type cursor is specific, and can only be understood by PL/SQL.
cheers.