I want to use the OCI driver. A third-party package I am trying to
install (Redhat's CCM/CMS system) asks for OCI driver due to issues
with LOB handling.
"Ed Yu" <ekyu_at_sc.rr.com> wrote in message news:<YN0u9.14768$hp5.1452333_at_twister.southeast.rr.com>...
> Is there a reason to use the OCI driver? Try the thin driver, it always
> works.
>
> "Paul Shields" <shields_at_passport.ca> wrote in message
> news:68e612a4.0210241512.3015b81a_at_posting.google.com...
> > I am trying to get tomcat to talk to an Oracle 9.2 with oci driver and
> > database connection pooling and the JSTL (standard taglibs 1.0.1),
> > and I am getting a message
> > java.sql.SQLException: closed connection
> > when I hit it from a JSP page.
> >
> >
> > Notes:
> > It works if I use the thin driver, but I have reason to want
> > to use the oci driver;
> > I've successfully tested database connection pooling with Postgres;
> > Tomcat has had LD_LIBRARY_PATH=/home/oracle/lib added to its
> > startup,
> > and ~tomcat/common/lib has the ojdbc14.jar file in it.
> > Base system is a Mandrake 9.0 linux (2.4.19-16mdkenterprise)
> >
> > I have scanned the tomcat 4.1 docs...
> >
> http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-how
> to.html
> >
> > I am out of ideas. Any help would be appreciated.
> >
> > thanks in advance,
> > Paul Shields
> >
> > shields at passport.ca
> >
> >
> > == Oracle database is operational; listener is active on port 1521;
> >
> > $ ORACLE_SID=orcl sqlplus scott/tiger
> >
> > SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 24 18:23:13 2002
> >
> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> >
> > Connected to:
> > Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> > With the Partitioning, OLAP and Oracle Data Mining options
> > JServer Release 9.2.0.1.0 - Production
> >
> > SQL> select * from paul;
> >
> > PAUL_ID PAUL_NAME
> > ------- --------------------------------------------
> > 1 paul
> > 2 shields
> >
> > SQL> exit;
> > Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 -
> > Production
> > With the Partitioning, OLAP and Oracle Data Mining options
> > JServer Release 9.2.0.1.0 - Production
> >
> >
> > == server.xml contains ==
> >
> > <Resource name="jdbc/orcl" auth="Container" scope="Shareable"
> > type="javax.sql.DataSource"/>
> > <ResourceParams name="jdbc/orcl">
> > <parameter>
> > <name>url</name>
> > <value>jdbc:oracle:oci8:@localhost:1521:orcl</value>
> > </parameter>
> > <parameter>
> > <name>validationQuery</name>
> > <value>select sysdate from dual</value>
> > </parameter>
> > <parameter>
> > <name>maxIdle</name>
> > <value>2</value>
> > </parameter>
> > <parameter>
> > <name>maxActive</name>
> > <value>4</value>
> > </parameter>
> > <parameter>
> > <name>driverClassName</name>
> > <value>oracle.jdbc.driver.OracleDriver</value>
> > </parameter>
> > <parameter>
> > <name>maxWait</name>
> > <value>5000</value>
> > </parameter>
> > <parameter>
> > <name>username</name>
> > <value>scott</value>
> > </parameter>
> > <parameter>
> > <name>factory</name>
> > <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> > </parameter>
> > <parameter>
> > <name>password</name>
> > <value>tiger</value>
> > </parameter>
> > </ResourceParams>
> >
> > == /home/tomcat/webapps/wpstest/jstlOracleSample.jsp contains...
> >
> >
> > <%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %>
> > <%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %>
> >
> > <html>
> > <head>
> > <title>JSP JSTL Oracle DB Sample</title>
> > </head>
> > <body bgcolor="white">
> >
> > <sql:query var="result" dataSource="jdbc/orcl">
> > select * from paul
> > </sql:query>
> >
> > <c:forEach items="${result.rows}" var="row">
> > <c:out value="${row.paul_id}"/>
> > <c:out value="${row.paul_name}"/><BR>
> > </c:forEach>
> >
> > </body>
> > </html>
> >
> >
> > == Searching thru Oracle documentation at
> > http://tahiti.oracle.com/pls/db92/db92.homepage
> > I found the following...
> >
> > Oracle9i Database Search Results: 'closed connection' in
> > Troubleshooting Topics
> >
> > java.sql.SQLException: closed connection
> >
> > SQL State: 08000
> >
> > Cause: An attempt was made to close a connection context object
> > whose
> > underlying JDBC connection object was already closed.
> >
> > Action: Ensure that the underlying JDBC connection was not
> > inadvertently closed. Also, if several SQLJ connection contexts
> > share
> > the same underlying JDBC connection, you have to ensure that when
> > you close the connection context objects all but the last close()
> > invokes the method close(ConnectionContext.KEEP_CONNECTION). This
> > ensures that the underlying JDBC connection remains open for the
> > duration and all associated JDBC resources -such as JDBC statement
> > objects- can be properly released.
> >
> >
> >
> >
> >
> > == web server output follows ==
> >
> >
> > <html><head><title>Apache Tomcat/4.1.12 - Error report</title></head>
> > <body><h1>HTTP Status 500 - </h1><hr size="1" noshade><p><b>type</b>
> > Exception report</p><p><b>message</b> <u></u></p><p><b>description</b>
> > <u>The server encountered an internal error () that prevented it from
> > fulfilling this request.</u></p><p><b>exception</b>
> > <pre>org.apache.jasper.JasperException: Unable to get connection,
> > DataSource invalid: "java.sql.SQLException: Closed Connection"
> > at
> org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:2
> 48)
> > at
> org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:289)
> > at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:240)
> > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
> FilterChain.java:247)
> > at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
> ain.java:193)
> > at
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.ja
> va:260)
> > at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.java:643)
> > at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> > at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > at
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.ja
> va:191)
> > at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.java:643)
> > at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> > at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > at
> org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2396)
> > at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180
> )
> > at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.java:643)
> > at
> org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.
> java:170)
> > at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.java:641)
> > at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172
> )
> > at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.java:641)
> > at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> > at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java
> :174)
> > at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.java:643)
> > at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> > at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > at
> org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
> > at
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:405)
> > at
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConne
> ction(Http11Protocol.java:380)
> > at
> org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508)
> > at
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.jav
> a:533)
> > at java.lang.Thread.run(Thread.java:536)
> > </pre></p><p><b>root cause</b> <pre>javax.servlet.ServletException:
> > Unable to get connection, DataSource invalid: "java.sql.SQLException:
> > Closed Connection"
> > at
> org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImp
> l.java:494)
> > at
> org.apache.jsp.jstlOracleSample_jsp._jspService(jstlOracleSample_jsp.java:10
> 8)
> > at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:136)
> > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > at
> org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:2
> 04)
> > at
> org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:289)
> > at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:240)
> > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
> FilterChain.java:247)
> > at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
> ain.java:193)
> > at
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.ja
> va:260)
> > at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.java:643)
> > at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> > at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > at
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.ja
> va:191)
> > at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.java:643)
> > at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> > at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > at
> org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2396)
> > at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180
> )
> > at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.java:643)
> > at
> org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.
> java:170)
> > at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.java:641)
> > at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172
> )
> > at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.java:641)
> > at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> > at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java
> :174)
> > at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
> eNext(StandardPipeline.java:643)
> > at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> > at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > at
> org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
> > at
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:405)
> > at
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConne
> ction(Http11Protocol.java:380)
> > at
> org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508)
> > at
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.jav
> a:533)
> > at java.lang.Thread.run(Thread.java:536)
> >
> > </pre></p><hr size="1" noshade><h3>Apache
> > Tomcat/4.1.12</h3></body></html>
Received on Fri Oct 25 2002 - 13:28:24 CDT
