In our company we currently use Oracle8i and currently our JBoss Server is mapped to Oracle 8i (maybe going forward we will use Oracle10g). Now for a new report that I am working on, my db team has written a stored procedure, the signature of which is some what like this
CREATE OR REPLACE PACKAGE hrdlchg_package as
Type cur_type is ref cursor;
procedure HRDLCHG_RPT(
I_FROMDTDATE IN ROOMCLASSHURDLES.DTDATE%TYPE,
I_TODTDATE IN ROOMCLASSHURDLES.DTDATE%TYPE,
I_LROOMCLASSID IN VARCHAR2,
I_NLOS IN VARCHAR2,
I_DTMRHDATESTART IN ROOMCLASSHURDLES.DTMRHDATETIME%TYPE,
I_DTMRHDATEEND IN ROOMCLASSHURDLES.DTMRHDATETIME%TYPE,
I_ORDERBY IN VARCHAR2,
list_cur in out cur_type );
procedure HISTORY_RPT(
I_STARTDATE IN DATE,
I_ENDDATE IN DATE,
I_RHDATESTART IN DATE,
I_NLOS IN VARCHAR2,
I_LROOMCLASSID IN VARCHAR2,
I_ORDERBY IN VARCHAR2,
list_cur in out cur_type);
end HRDLCHG_PACKAGE;
The thing to note here is the declaration of cur_type as ref cursor
Now it so happens that jBoss does not have a way to map the jdbc types Types.Other or Types.Ref to this sql type.
In my standardjbosscmp-jdbc.xml file I tried to map java.sql.ResultSet to Types.Ref and than to ref cursor but that did not work
Something like this
<mapping>
<java-type>java.sql.ResultSet</java-type>
<jdbc-type>REF</jdbc-type>
<sql-type>ref cursor</sql-type>
</mapping>
So would anyone know how to get Jboss to recognize refcursor.
In the java code I was making call like this
if( ServerUtil.isOracleDB() ) {
cStmt = GetConnection.getPreparedCall( "{call hrdlchg_package.HISTORY_RPT(?,?,?,?,?,?,?)}");
//register the OUT parameter of pl/sql function as a OracleTypes.Cursor datatype
cStmt.setTimestamp(1, this.arrivalFrom);
cStmt.setTimestamp(2, this.arrivalTo);
cStmt.setTimestamp(3, this.baselineDate);
cStmt.setString(4, this.LOS);
cStmt.setString(5, this.roomClasses);
cStmt.setString(6, this.colToOrderBy);
//cStmt.setNull(1, Types.REF);
cStmt.registerOutParameter(7, Types.REF);
cStmt.executeUpdate();
rs = (ResultSet)cStmt.getObject(1);let me know
