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
