• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Passing array input in procedure - thin driver

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
    Posts: 9
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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.
     
    Sheriff
    Posts: 67746
    173
    Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    "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.
     
    Avi Abrami
    Ranch Hand
    Posts: 1143
    1
    Eclipse IDE Oracle Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    Posts: 9
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Hi Avi ,
    Thanks for your prompt reply.
    I fixed the problem .The type definitions were at the wrong place.
     
    Ranch Hand
    Posts: 207
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    Sheriff
    Posts: 67746
    173
    Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    "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
    Posts: 1143
    1
    Eclipse IDE Oracle Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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.
     
    Greenhorn
    Posts: 1
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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.
     
    Don't get me started about those stupid light bulbs.
    reply
      Bookmark Topic Watch Topic
    • New Topic