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
View the original post :
http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3975203#3975203
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3975203
_______________________________________________
jboss-user mailing list
jboss-user@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/jboss-user
