Using application server’s connection pool we get following error: connections from the pool, do not match their preferences, which leads to errors in stored procedures calling (it is not enough permission to call it).
We use the following code for the connection and stored procedures call:
int result = -1;
try {
InitialContext ic = new InitialContext();
DataSource dataSource = (DataSource) ic.lookup("jdbc/StandardsLogin");
Connection connection = dataSource.getConnection();
CallableStatement statement = ((oracle.jdbc.OracleConnection)((com.sun.appserv.jdbc.DataSource) dataSource).getConnection(connection)).prepareCall("{call econ.common.appserver_ident(?,?,?, ?,?,?,?,?,?,?,?,?,?,?,?)}");
statement.registerOutParameter(4, java.sql.Types.INTEGER);
statement.registerOutParameter(5, java.sql.Types.VARCHAR);
statement.registerOutParameter(6, java.sql.Types.VARCHAR);
statement.registerOutParameter(7, java.sql.Types.VARCHAR);
statement.registerOutParameter(8, java.sql.Types.VARCHAR);
statement.registerOutParameter(9, java.sql.Types.INTEGER);
statement.registerOutParameter(10, java.sql.Types.VARCHAR);
statement.registerOutParameter(11, java.sql.Types.VARCHAR);
statement.registerOutParameter(12, java.sql.Types.VARCHAR);
statement.registerOutParameter(13, java.sql.Types.INTEGER);
statement.registerOutParameter(14, java.sql.Types.INTEGER);
statement.registerOutParameter(15, java.sql.Types.VARCHAR);
statement.setString(1, login);
statement.setString(2, password);
statement.setString(3, "127.0.0.1");
statement.execute();
result = statement.getInt(14);
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
Stack trace:
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'ECON.COMMON' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:191)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:944)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3482)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:3856)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1373)
at testbean.TestBean.login(TestBean.java:115)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1067)
at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:176)
at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2895)
at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:3986)
at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:203)
at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:120)
at $Proxy83.login(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.sun.corba.ee.impl.presentation.rmi.ReflectiveTie._invoke(ReflectiveTie.java:154)
at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatchToServant(CorbaServerRequestDispatcherImpl.java:687)
at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatch(CorbaServerRequestDispatcherImpl.java:227)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequestRequest(CorbaMessageMediatorImpl.java:1846)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:1706)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleInput(CorbaMessageMediatorImpl.java:1088)
at com.sun.corba.ee.impl.protocol.giopmsgheaders.RequestMessage_1_2.callback(RequestMessage_1_2.java:223)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:806)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.dispatch(CorbaMessageMediatorImpl.java:563)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.doWork(CorbaMessageMediatorImpl.java:2567)
at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:555)
Connection pool settings:
<jdbc-resource enabled="true" jndi-name="jdbc/StandardsLogin" object-type="user" pool-name="StandardsLoginPool"/>
<jdbc-connection-pool allow-non-component-callers="false" associate-with-thread="false" connection-creation-retry-attempts="0" connection-creation-retry-interval-in-seconds="10" connection-leak-reclaim="false" connection-leak-timeout-in-seconds="0" connection-validation-method="auto-commit" datasource-classname="oracle.jdbc.pool.OracleConnectionPoolDataSource" fail-all-connections="false" idle-timeout-in-seconds="300" is-connection-validation-required="false" is-isolation-level-guaranteed="false" lazy-connection-association="false" lazy-connection-enlistment="false" match-connections="false" max-connection-usage-count="0" max-pool-size="32" max-wait-time-in-millis="60000" name="StandardsLoginPool" non-transactional-connections="false" pool-resize-quantity="2" res-type="javax.sql.DataSource" statement-timeout-in-seconds="-1" steady-pool-size="8" validate-atmost-once-period-in-seconds="0" wrap-jdbc-objects="false">
<property name="DataSourceName" value="OracleConnectionPoolDataSource"/>
<property name="ImplicitCachingEnabled" value="false"/>
<property name="NetworkProtocol" value="tcp"/>
<property name="DatabaseName" value="ara"/>
<property name="Password" value="appserver"/>
<property name="LoginTimeout" value="0"/>
<property name="DriverType" value="thin"/>
<property name="User" value="e$appserver"/>
<property name="ExplicitCachingEnabled" value="false"/>
<property name="PortNumber" value="1521"/>
<property name="ServerName" value="ara"/>
<property name="MaxStatements" value="0"/>
</jdbc-connection-pool>
Application server: Glassfish V2 rc4
Database: Oracle 9.2.04
Oracle JDBC driver version: 10.2.0.3, 11.1.0.6.0
[Message sent by forum member 'falsehood' (falsehood)]
http://forums.java.net/jive/thread.jspa?messageID=233441