*
The moose likes JDBC and the fly likes Passing array input in procedure - thin driver Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Passing array input in procedure - thin driver" Watch "Passing array input in procedure - thin driver" New topic
Author

Passing array input in procedure - thin driver

Rajan Janakra
Greenhorn

Joined: Jun 21, 2005
Posts: 9
Hey Guys,

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.

[Bear edit: reduced shouting.]
[ June 22, 2005: Message edited by: Bear Bibeault ]
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1131

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.

Here are some links that may be helpful:

http://tinyurl.com/c6lkp

http://tinyurl.com/8eldg

http://tinyurl.com/bm3d3

Alternatively, post some more details, including:
  • Oracle database version you are using
  • Java version you are using
  • JDBC driver (and version) you are using
  • entire error message and stack trace you are getting
  • the part of your java code that is causing the error
  • the call-spec for your PL/SQL procedure


  • (And I may be able to help you further.)

    Good Luck,
    Avi.
    Rajan Janakra
    Greenhorn

    Joined: Jun 21, 2005
    Posts: 9
    Hi Abi,
    This is how I'm passing the array type

    String str[] = { "MAST", "MAST" };
    ArrayDescriptor desc1 = ArrayDescriptor.createDescriptor("TWANG6.CHARARRAYTYP_TY",connection);
    ARRAY input1 = new ARRAY(desc1, connection, str);

    callableStmt.setArray(1, input1);
    callableStmt.registerOutParameter(2, OracleTypes.CURSOR);
    callableStmt.execute();

    I followed the steps mentioned in ask tom link.
    I'm afraid whether thin driver supports this.

    Both this type definitions in schema level as mentioned by tom.

    Somehow procedure is not able to get this input.
    A procedure sample would be highly helpfull.
    YUTHAM.
    Bear Bibeault
    Author and ninkuma
    Marshal

    Joined: Jan 10, 2002
    Posts: 61095
        
      66

    "YUTHAM",

    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.


    [Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
    Avi Abrami
    Ranch Hand

    Joined: Oct 11, 2000
    Posts: 1131

    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 ]
    Rajan Janakra
    Greenhorn

    Joined: Jun 21, 2005
    Posts: 9
    Hi Avi ,
    Thanks for your prompt reply.
    I fixed the problem .The type definitions were at the wrong place.
    thomas davis
    Ranch Hand

    Joined: Feb 01, 2003
    Posts: 207
    Please help me out to solve this error...


    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)
    Bear Bibeault
    Author and ninkuma
    Marshal

    Joined: Jan 10, 2002
    Posts: 61095
        
      66

    "Rajan",

    Your display is still not in compliance with the policy. You display name must be a first and a last name separated by a space character.

    Adjust your display name prior to your next post.

    Accounts with invalid display names are removed.

    bear
    Forum bartender
    Avi Abrami
    Ranch Hand

    Joined: Oct 11, 2000
    Posts: 1131

    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.

    Good Luck,
    Avi.
    Ravikumar Jothimani
    Greenhorn

    Joined: Dec 14, 2007
    Posts: 1
    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;

    return;

    END CCListByDistrict;


    3.Create java method
    import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection;
    import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil;
    public static TreeMap listCostCenterLookupByDistrict(String[] str) throws DAOException
    {
    logger.info("entering listCostCenterLookupByFinanceNo()");
    TreeMap map = new TreeMap();
    map.put("00","All");
    java.sql.Connection conn = null;
    java.sql.Connection myConn = null;
    ResultSet rs = null;
    OraclePreparedStatement ps = null;


    try{

    conn = DAOUtil.getDataSourceConnection(DPONE6_DATA_SOURCE);
    myConn = (java.sql.Connection)WSJdbcUtil.getNativeConnection((WSJdbcConnection)conn);
    ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("VC2ARRAY", myConn );
    ARRAY array_to_pass = new ARRAY( descriptor, myConn, str );
    ps = (OraclePreparedStatement)myConn.prepareStatement( "select cost_center, cost_center_name from table(db_query.CCListByDistrict(?))" );
    ps.setObject( 1, array_to_pass );
    rs = ps.executeQuery();

    while(rs.next())
    {
    map.put(rs.getString("cost_center"),rs.getString("cost_center_name")+" ("+rs.getString("cost_center")+")");
    }



    }
    catch(Exception e){
    throw new DAOException(e.getMessage());
    }
    finally {
    DAOUtil.close(rs);
    DAOUtil.close(ps);
    DAOUtil.close(conn);
    }
    logger.info("leaving listCostCenterLookupByFinanceNo()");
    return map;
    }

    The problem solved for me.


    To pass any parameter as java array to stored procedure we can use this method.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Passing array input in procedure - thin driver