2 Replies Latest reply on Sep 25, 2002 6:33 AM by Peter Landmann

    Session bean calling oracle stored procedure with an array p

    Kari Pulkka Newbie

      Hi, I have the following problem:
      I'm trying to call an oracle stored procedure that takes an array as an input parameter.

      In my jboss.jcml I have included this:

      oracle.jdbc.driver.OracleDriver



      warrantPool
      org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
      NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
      jdbc:oracle:thin:@myserver.mydomain.fi:1521:MYSERVER
      issadmin
      issadm1n
      2
      10
      true


      In code I try to do the following:
      java.sql.Connection con = null;
      oracle.jdbc.driver.OracleCallableStatement pstmt = null;
      java.sql.ResultSet rset = null;
      try {
      String stmt = "{? = call DO_TITO_UNMATCH(?)}";
      javax.naming.InitialContext initCtx = new javax.naming.InitialContext();
      javax.sql.DataSource ds = (javax.sql.DataSource)initCtx.lookup("java:comp/env/skynet");
      con = ds.getConnection();
      pstmt = (oracle.jdbc.driver.OracleCallableStatement)con.prepareCall(stmt);
      java.math.BigDecimal[] table = {new java.math.BigDecimal("78"), new java.math.BigDecimal("79")};
      oracle.sql.ArrayDescriptor arr_desc = new oracle.sql.ArrayDescriptor("SUBSLIST", con);
      oracle.sql.ARRAY array = new oracle.sql.ARRAY(arr_desc, con, table);
      pstmt.setARRAY(2, array);
      pstmt.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.INTEGER);
      pstmt.execute();
      int i = pstmt.getInt(1);

      When executing this I get an error while trying to instantiate the ArrayDescriptor. I can get this to work if I get a connection using this code fragment:
      con = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@myserver.mydomain.fi:1521:MYSERVER", "issadmin", "issadm1n");

      But you're supposed to get your connections from the pool, right? When I compare the connection instance classes, the pooled connection that doesn't work is org.jboss.pool.jdbc.xa.wrapper.XAClientConnection and the one that does work is oracle.jdbc.driver.OracleConnection.

      Is it possible to change the jboss.jcml configuration to use an oracle datasource and if so, what is the class? Or how should I proceed?

      Thanks for taking the time to read my lengthy question. Any help would be apppreciated. TIA,

      Kari