Robert, Thanks for your reply. I checked before (forgot to mention in my last message) that the DB login for tomcat (java bean) is the same with the one for Apache (servlet). They are pointing to the same function under the same schema. The privliges is ok for the login as it's the creator.
Thanks, I'm still very puzzled. Geraldine -----Original Message----- From: Robert F Hall [mailto:[EMAIL PROTECTED] Sent: Monday, 8 August 2005 2:21 PM To: Tomcat Users List Subject: Re: Oracle function problem in using tomcat 5.5.9 Geraldine, Is the same DB login being used in all cases? If not, then the problem may be due to the DB user not having execute privileges on the procedure in question. Another possibility is that the DB's are different and procedure actually does not exist. - Robert [EMAIL PROTECTED] wrote: > >Hi, > >I got an error message as below when I use an oracle function in a >statement in a java bean, but I use the same statement in a java servlet, it's ok. > >The statement in the bean is: >public String[] getDetailAD(){ > >try{ > Connection conn = null; > > if (conn == null) { > conn = JDBCConnectionPool.getConnection(ORACLE_DATASOURCE); > } > if (conn == null) { > throw new Exception("Can't connect to Oracle"); > } > Statement stmt = conn.createStatement(); > > String query = "select ADV_MERCURY_DESC(club_mercury) from >ops$profai.faagent where no = 12345"; > > ResultSet rs = stmt.executeQuery(query); > while (rs.next()) {... > >The function is: >(p_str IN STRING) RETURN VARCHAR2 > -- > IS > > leftstr varchar2(1); > BEGIN > leftstr := substr (p_str ,1,1); > > IF p_str IS NULL THEN > RETURN 'NONE'; > ELSIF leftstr = ' ' THEN > RETURN 'NONE'; > ELSIF leftstr = 'G' THEN > RETURN 'GOLD'; > ELSIF leftstr = 'S' THEN > RETURN 'SILVER'; > ELSIF leftstr = 'B' THEN > RETURN 'BLUE'; > ELSIF leftstr = 'T' THEN > RETURN 'TLC'; > ELSIF leftstr = 'R' THEN > RETURN 'RED'; > ELSIF leftstr = 'N' THEN > RETURN 'NOW'; > ELSIF leftstr = 'W' THEN > RETURN 'NOW GOLD'; > > ELSE > RETURN ('UNKNOWN:' || leftstr || '!'); > END IF; > >END ADV_MERCURY_DESC; > >The statement and the function are always ok if I run them directly in >Oracle environment or use a servlet which is running under jdk2 and >Apache server 1.3, but get error when run the bean in Tomcat5.5.9, the >oracle driver is classes12.jar. > >Could anyone give me a clue? > >Thanks in advance. > >Geraldine > >-------- >Error message is here: > >java.sql.SQLException: ORA-00904: "ADV_MERCURY_DESC": invalid >identifier > > at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168) > at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208) > at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543) > at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405) > at >oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java: >643) > at >oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.jav >a:1674) > at >oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme >nt.java:1870) > at >oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java: >538) > at >org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingSt >atement.java:162) > at oiintranet.MainADBean.getDetailAD(MainADBean.java:106) > at >org.apache.jsp.DetailAD_jsp._jspService(org.apache.jsp.DetailAD_jsp:6 >3) > at >org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97) > at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) > at >org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper >.java:322) > at >org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:2 >91) > at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241) > at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) > at >org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl >icationFilterChain.java:252) > at >org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF >ilterChain.java:173) > at >org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV >alve.java:213) > at >org.apache.catalina.core.StandardContextValve.invoke(StandardContextV >alve.java:178) > at >org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j >ava:126) > at >org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j >ava:105) > at >org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal >ve.java:107) > at >org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav >a:148) > at >org.apache.coyote.http11.Http11Processor.process(Http11Processor.java >:856) > at >org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proce >ssConnection(Http11Protocol.java:744) > at >org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpo >int.java:527) > at >org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFol >lowerWorkerThread.java:80) > at >org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadP >ool.java:684) > at java.lang.Thread.run(Unknown Source) > >******************************************************************* >The information contained in this mail message is confidential and may also be legally privileged. >If you are not the intended recipient, please note that any use, >dissemination, further distribution, or reproduction of this message in any form what so ever, is strictly prohibited. >If the mail is in error, please notify me by return E-mail, delete your >copy of the message, and accept my apologies for any inconvenience caused. >******************************************************************* > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] ******************************************************************* The information contained in this mail message is confidential and may also be legally privileged. If you are not the intended recipient, please note that any use, dissemination, further distribution, or reproduction of this message in any form what so ever, is strictly prohibited. If the mail is in error, please notify me by return E-mail, delete your copy of the message, and accept my apologies for any inconvenience caused. *******************************************************************
