2 Replies Latest reply on Mar 8, 2007 7:58 AM by Thomas Rois

    Getting Jboss to accept ref cursor or recognize it

    rahul sood Newbie

      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