2 Replies Latest reply on Aug 23, 2007 2:33 PM by sirji

    Mapping of Oracle REF CURSOR in standardjbosscmp-jdbc.xml ?

    rkhurana

      Hi
      My program that runs outside the jboss environment opens a db connection using Jboss connection pool setting(data sources). It is able to execute sqls and stored procedures correctly. However if a stored procedure has output parameter defined as REF CURSOR, then it fails to execute that procedure and reports Proxy$4 exception without any details. I suspect there is a missing mapping in standardjbosscmp-jdbc.xml for REF CURSOR. Could someone advice if that indeed is the problem and how to configure this mapping?


      <java-type>java.sql.resultset</java-type>
      <jdbc-type>??
      <sql-type>??


      thanks
      RK

        • 1. Re: Mapping of Oracle REF CURSOR in standardjbosscmp-jdbc.xm
          roist

          if you are using the PreparedStatement or CallableStatement-interface to do so, all you have to do is

          // Cursor = -10
          cs.registerOutParameter (x, OracleTypes.Cursor );
          cs.execute();

          ResultSet rs = (ResultSet)cs.getObject(x);

          • 2. Re: Mapping of Oracle REF CURSOR in standardjbosscmp-jdbc.xm
            sirji

            Hi all,

            We are using JBoss 4.0.5 and Oracle 9i. and have exactly similar scenario where we are getting the connection object through Data source lookup and executing a stored procedure have ref cursor.

            We are also getting following error:-

            java.lang.ClassCastException: $Proxy2

            Code snippet:

            boolean success = stmt.execute();
            ResultSet rsRawData = ((OracleCallableStatement)stmt).getCursor(7);

            OR

            boolean success = stmt.execute();
            ResultSet rsRawData = ((ResultSet)stmt).getCursor(7);


            But if we don't use JBoss datasource and create a connection using DriverManager then it works perfectly fine.

            Any assistance on this will be helpful.