Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    78

    Unanswered: call PL/SQL procedure from java problem

    hello experts,

    I have configured oracle9i datasource in wsad 5.0.1, and test the connection from servelt, everythings fine. I could get data via execute a query. I have a PL/SQL stored procedure, test it good from SQL*PLUS. I tried to call the stored procedure from a servlet:

    CallableStatement cs = con.prepareCall("{call SCREENTAGS(?,?,?)}");
    cs.setString(1,"logon");
    cs.setString(2,"E");
    cs.registerOutParameter(3, OracleTypes.CURSOR);
    ResultSet rs = (ResultSet)((OracleCallableStatement)cs).getCursor (3);
    while (rs.next()) {
    out.println("tagid =" + rs.getString("TAGID") + ", tagtext = " + rs.getString("TAGTEXT") + "<br>");
    }

    I got:

    java.lang.ClassCastException: com.ibm.ws.rsadapter.jdbc.WSJdbcCallableStatement at testOra.doGet(testOra.java:44) at javax.servlet.http.HttpServlet.service(HttpServlet .java:740) at javax.servlet.http.HttpServlet.service(HttpServlet .java:853) at com.ibm.ws.webcontainer.servlet.StrictServletInsta nce.doService(StrictServletInstance.java:110) at com.ibm.ws.webcontainer.servlet.StrictLifecycleSer vlet._service(StrictLifecycleServlet.java:174) at com.ibm.ws.webcontainer.servlet.IdleServletState.s ervice(StrictLifecycleServlet.java:313) at com.ibm.ws.webcontainer.servlet.StrictLifecycleSer vlet.service(StrictLifecycleServlet.java:116) at com.ibm.ws.webcontainer.servlet.ServletInstance.se rvice(ServletInstance.java:258) at com.ibm.ws.webcontainer.servlet.ValidServletRefere nceState.dispatch(ValidServletReferenceState.java: 42) at com.ibm.ws.webcontainer.servlet.ServletInstanceRef erence.dispatch(ServletInstanceReference.java:40) at com.ibm.ws.webcontainer.webapp.WebAppRequestDispat cher.handleWebAppDispatch(WebAppRequestDispatcher. java:872) at com.ibm.ws.webcontainer.webapp.WebAppRequestDispat cher.dispatch(WebAppRequestDispatcher.java:491) at com.ibm.ws.webcontainer.webapp.WebAppRequestDispat cher.forward(WebAppRequestDispatcher.java:173) at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForwar d(WebAppInvoker.java:79) at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleIn vocationHook(WebAppInvoker.java:199) at com.ibm.ws.webcontainer.cache.invocation.CachedInv ocation.handleInvocation(CachedInvocation.java:71) at com.ibm.ws.webcontainer.srp.ServletRequestProcesso r.dispatchByURI(ServletRequestProcessor.java:182) at com.ibm.ws.webcontainer.oselistener.OSEListenerDis patcher.service(OSEListener.java:331) at com.ibm.ws.webcontainer.http.HttpConnection.handle Request(HttpConnection.java:56) at com.ibm.ws.http.HttpConnection.readAndHandleReques t(HttpConnection.java:432) at com.ibm.ws.http.HttpConnection.run(HttpConnection. java:343) at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.j ava:592)

    I tried to do call this in a java application, I got the following error right before I set the resultSet:

    java.sql.SQLException: Invalid column index
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:180)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:222)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:285)
    at oracle.jdbc.driver.OracleStatement.prepare_for_new _get(OracleStatement.java:2804)
    at oracle.jdbc.driver.OracleStatement.getCursorValue( OracleStatement.java:3179)
    at oracle.jdbc.driver.OracleCallableStatement.getCurs or(OracleCallableStatement.java:604)
    at testOraApp.main(testOraApp.java:32)


    Please help me. I'd appreciated.

    christine

  2. #2
    Join Date
    Feb 2002
    Posts
    28
    Try if this works..
    Hope the 3rd parameter of the called procedure returns a ref cursor...

    CallableStatement cs = con.prepareCall("{call SCREENTAGS(?,?,?)}");
    cs.setString(1,"logon");
    cs.setString(2,"E");
    cs.registerOutParameter(3, OracleTypes.CURSOR);
    ResultSet rs = (ResultSet)cs.getObject(3);


    PC

  3. #3
    Join Date
    Jul 2003
    Posts
    78

    Unhappy

    No, I did tried this. It is the same error. Any idea?

    thanks,
    christine



    Originally posted by vaidyanathanpc
    Try if this works..
    Hope the 3rd parameter of the called procedure returns a ref cursor...

    CallableStatement cs = con.prepareCall("{call SCREENTAGS(?,?,?)}");
    cs.setString(1,"logon");
    cs.setString(2,"E");
    cs.registerOutParameter(3, OracleTypes.CURSOR);
    ResultSet rs = (ResultSet)cs.getObject(3);


    PC

  4. #4
    Join Date
    Feb 2002
    Posts
    28
    Request you to send the details of the procedure that you are calling from the java code.

    Regards,
    PC

  5. #5
    Join Date
    Jul 2003
    Posts
    78

    Here is the code from database:

    PACKAGE SPYGLASSTYPES AS

    TYPE Gen_Cur IS REF CURSOR;

    END;

    PROCEDURE SCREENTAGS (CurrentPage in varchar2, CurLanguage in varchar2, c1 OUT SpyGlassTypes.Gen_Cur) AS
    BEGIN
    DBMS_OUTPUT.PUT_LINE('Begin the procedure: 2 input parameters are: ' || CurrentPage || ',' || CurLanguage || '.');

    open c1 for
    Select a.screenid as SCREENID, a.tagid as TAGID, b.tagtext as TAGTEXT
    From Screentag a, Rbasrndict b
    Where a.refno = b.refno and
    TRIM(a.Screenid) = TRIM('' ||CurrentPage|| '') and
    TRIM(b.Lang) = TRIM('' ||CurLanguage|| '');

    END;


    It works fine from database side. Thanks a lot for your reply.
    Christine


    Originally posted by vaidyanathanpc
    Request you to send the details of the procedure that you are calling from the java code.

    Regards,
    PC

  6. #6
    Join Date
    Feb 2002
    Posts
    28
    Hi,
    you missed the "callable.execute();" in your java code...
    Try if the code below works.. We tried it here by creating your tables and executing it thru java.
    We have put in our variable, so pls check the variable names before executing.

    Regards,
    PC

    CallableStatement callable=connection.prepareCall("{call SCREENTAGS(?,?,?)}");
    callable.setString(1,"logon");
    callable.setString(2,"E");
    callable.registerOutParameter(3,OracleTypes.CURSOR );
    callable.execute();
    ResultSet result=(ResultSet)callable.getObject(3);

    while(result.next())
    {
    System.out.println(result.getString("TAGID")+" "+result.getString("TAGTEXT"));
    }

  7. #7
    Join Date
    Jul 2003
    Posts
    78

    Talking

    Thank you point that out. I forgot to un-comment the statement. But I still got :
    java.lang.ClassCastException: com.ibm.ws.rsadapter.jdbc.WSJdbcCallableStatement

    Then I change to :
    rs =(ResultSet)cs.getObject(3);

    It work! Thanks a lot.


    Originally posted by vaidyanathanpc
    Hi,
    you missed the "callable.execute();" in your java code...
    Try if the code below works.. We tried it here by creating your tables and executing it thru java.
    We have put in our variable, so pls check the variable names before executing.

    Regards,
    PC

    CallableStatement callable=connection.prepareCall("{call SCREENTAGS(?,?,?)}");
    callable.setString(1,"logon");
    callable.setString(2,"E");
    callable.registerOutParameter(3,OracleTypes.CURSOR );
    callable.execute();
    ResultSet result=(ResultSet)callable.getObject(3);

    while(result.next())
    {
    System.out.println(result.getString("TAGID")+" "+result.getString("TAGTEXT"));
    }

  8. #8
    Join Date
    Feb 2002
    Posts
    28
    Right Ho...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •