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]
