I'm trying to pass a Java Array type has a parameter in a procedure call using thin driver. I'm hitting with type cast error.If someone has done this before can you guide me through the steps.
YUTHAM, Since you mention the "thin" driver, I assume you are using Oracle's [thin] JDBC driver and you have a PL/SQL stored procedure.
If my assumption(s) are correct, then your question has been asked (and answered) several times previously (by me) in this forum, and in the OTN Forums.
We're pleased to have you here with us on the Ranch, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.
In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.
Thanks! bear Forum Bartender
P.S. Also please refrain from using all uppercase. It comes across as SHOUTING and could be construed as very rude.
YUTHAM, You only partially answered my fifth question, which means you supplied me with less than ten percent (10%) of the information I requested. Hence the remainder of this post may (or may not) be relevant for you.
My environment is:
Oracle 9i (9.2.0.4) database running on Red Hat linux
JDK 1.4.2_07
"ojdbc14.jar" (Oracle JDBC driver)
First I created the following database type (via SQL*Plus):
Then I created a stored procedure (in PL/SQL) that takes a single input parameter of type CHARARRAYTYP_TY (again, via SQL*Plus):
Now the java code that invokes my "P0" (PL/SQL) procedure:
Hope it helps.
Good Luck, Avi. [ June 23, 2005: Message edited by: Avi Abrami ]
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;
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)
Thomas, You don't need to hijack this thread -- I have answered your question in the other thread.
I often see people posting the same question to multiple forums, but posting the same question twice in the same forum is certainly new to me. How innovative of you! In any case, as you can see, it doesn't increase your chances of getting an answer.
Here is the soution 1.create oracle object CREATE OR REPLACE TYPE VC2ARRAY is table of varchar2(400);
2.create function using the above object as parameter FUNCTION CCListByDistrict (pDist IN VC2ARRAY) return CCListByDistrict_table pipelined
IS BEGIN FOR vRec in (SELECT a.cost_center, a.cost_center_name FROM cost_center_t a, ( SELECT column_value from TABLE(pDist)) b WHERE a.district_code = b.column_value ) LOOP PIPE ROW(CCListByDistrict_obj(vRec.cost_center, vRec.cost_center_name)); END LOOP;