Pass Array From to Java to Stored Procedure

[thomas davis]

Oracle database version you are using = Oracle9i
Java version you are using = jdk 1.4
JDBC driver (and version) you are using = thin driver





I have declared an array of numbers in package.

TYPE NUM_ARRAY is TABLE OF NUMBER;
TYPE DOOUBLE_ARRAY is TABLE OF NUMBER;
 
I have written following stored procedure and it is receiving above array as a parameter.
 
 
PROCEDURE applychangesrollupdown (      
 p_editablecolumn   INVARCHAR2, 
 p_cartkey      IN      INTEGER, 
 p_data             IN      DOOUBLE_ARRAY,  
 p_array                IN      NUM_ARRAY , 
 p_errcode          OUT     INTEGER,  
 p_errmsg           OUT     VARCHAR2)   
AS
 
v_count NUMBER:= 0;
--Cursor which holds parents in a given cart 
CURSOR cartitemcursor IS
select KEY from cart_item WHERE cart_key = p_cartkey and product_key = (select key from product where part_num = 'IPT-INST-SUPPORT');
 
BEGIN
 
FOR cartitemcursor_rec IN cartitemcursor LOOP
BEGIN
v_count := is_itemschanged (p_array ,cartitemcursor_rec.KEY);
--If the parent is changed the roll down the changes 
IF v_count <> -1
THEN
getrolldown( p_editablecolumn, 
 p_cartkey, 
 p_data(v_count),  
  p_array  , 
  p_errcode ,  
  p_errmsg ,
  cartitemcursor_rec.KEY);
--in case there are any changes in children
getrollup ( p_cartkey,  
p_errcode,  
p_errmsg,
cartitemcursor_rec.KEY); 
ELSE 
--If parent is not changed the roll up child date to parent  
getrollup ( p_cartkey,   
p_errcode,  
p_errmsg,
cartitemcursor_rec.KEY); 
END IF;
DBMS_OUTPUT.put_line ('After rolling up and down.');
END;
END LOOP ; 
EXCEPTION
     WHEN NO_DATA_FOUND
     THEN
        p_errcode := 1;
        p_errmsg := 'No Data FOUND FOR the given Parameters';
     WHEN OTHERS
     THEN
        p_errcode := 2;
        p_errmsg :=
                  'Error WHILE getting data FOR Parent or child - ' || SQLERRM;
 
END applychangesrollupdown;
 
 
 
--*************************************************************************
   
END ngd_pa;

/


I am trying to call applychangesrollupdown procedure from java program using callable statement.

Is there anybody having the solution for this problem? I am not able to find out the solution which should work with websphere environment and it should throw any exception or error.

My java code is as follows:

public void applyRollupRolldown(String editableField, int cartID, int[] itemList, double[] data) throws DataAccessException, DatabaseConnectionException 
{
if (log.isDebugEnabled()) 
{
log.debug("In appluRollupRolldown method");
log.debug("Cart ID =" + cartID + " Data=" + data + "Editable Field =" + editableField);
}
Connection con = null;
CallableStatement cStmt = null;
try 
{
con = getConnection();
cStmt = con.prepareCall("call ngd_pa.applychangesrollupdown(?,?,?,?,?,?)");
ArrayDescriptor descriptor =ArrayDescriptor.createDescriptor( "NUM_ARRAY", con);
ARRAY array_to_pass_item =new ARRAY(descriptor, con, itemList);
ARRAY array_to_pass_data = new ARRAY(descriptor, con, data);
cStmt.setString(1, editableField);
cStmt.setInt(2, cartID);
cStmt.setArray(3, array_to_pass_data);
cStmt.setArray(4, array_to_pass_item);
cStmt.registerOutParameter(5, OracleTypes.NUMERIC);
cStmt.registerOutParameter(6, OracleTypes.VARCHAR);
cStmt.execute();
con.commit();
} 
catch (SQLException sqle) 
{
 throw new DatabaseConnectionException(sqle);

 }
}

ERROR STACK :





2005-07-01 18:37:25,208 [Servlet.Engine.Transports : 0] ERROR com.sbc.dcomm.ui.utility.DcExceptionHandler - Exception encountered:
java.lang.ClassCastException: com.ibm.ws.rsadapter.jdbc.WSJdbcConnection
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:108)
at com.sbc.dcomm.ui.pa.dao.PACartItemDAO.applyRollupRolldown(PACartItemDAO.java:236)
at com.sbc.dcomm.ui.pa.business.PAService.applyRollupRolldown(PAService.java:336)
at com.sbc.dcomm.ui.pa.actions.PAAction.applyChanges(PAAction.java:382)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java(Compiled Code))
at java.lang.reflect.Method.invoke(Method.java(Compiled Code))
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:274)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:194)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at com.sbc.dcomm.ui.utility.DcRequestProcessor.process(DcRequestProcessor.java:39)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:76)
at com.sbc.dcomm.ui.filter.AuthenticationFilter.doFilter(AuthenticationFilter.java:105)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:132)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:71)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:974)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:564)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:200)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:119)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:276)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:182)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:618)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:439)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:672)



[moved code tags to surround code]
[ July 02, 2005: Message edited by: Jeanne Boyarsky ]

[Avi Abrami]

Thomas,
I'm only guessing here, but the "java.sql.Connection" parameter you pass to the "ArrayDescriptor" constructor, needs to be an "OracleConnection" and not a "com.ibm.ws.rsadapter.jdbc.WSJdbcConnection". That's why you are getting a "ClassCastException".

I don't use "WebSphere", but "WSJdbcConnection" is probably a "wrapper" for a "java.sql.Connection". You probably need to extract the "Connection" from the wrapper, and pass that to the "ArrayDescriptor" constructor. But since I don't use "WebSphere", I can't tell you how to do that -- sorry!

Good Luck,
Avi.