java.sql.SQLException: executeQuery, Exception

Issue is due to the constrains in using the Statement Cache. Statement caching improves performance by caching executable statements that are used repeatedly, such as in a loop or in a method that is called repeatedly. JDBC 3.0 defines a statement-caching interface.
Usage Restrictions for the Statement Cache Getting unknown result from the executeQuery from DB/Driver:

####<01 déc. 2011 17 h 37 CET> <Debug> <JDBCSQL> <ptpdbs9s0eng.chanel.net> <TPDBWO_01> <[ACTIVE] ExecuteThread: '7' for queue: 'weblogic.kernel.Default (self-tuning)'> <> <BEA1-0065D610F0C21B670122> <3e09851f42c78f0c:-52630399:134544e1a3f:-7fd6-000000000000b2d0> <1324312647609> <BEA-000000> <[weblogic.jdbc.wrapper.CallableStatement_oracle_jdbc_driver_OracleCallableStatementWrapper@27d5] executeQuery()>
####<01 déc. 2011 17 h 37 CET> <Debug> <JDBCSQL> <ptpdbs9s0eng.chanel.net> <TPDBWO_01> <[ACTIVE] ExecuteThread: '7' for queue: 'weblogic.kernel.Default (self-tuning)'> <> <BEA1-0065D610F0C21B670122> <3e09851f42c78f0c:-52630399:134544e1a3f:-7fd6-000000000000b2d0> <1324312647610> <BEA-000000> <[weblogic.jdbc.wrapper.CallableStatement_oracle_jdbc_driver_OracleCallableStatementWrapper@27d5] executeQuery(unknown) throws: java.lang.NullPointerException
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:188)
at oracle.jdbc.driver.T4CCallableStatement.executeForDescribe(T4CCallableStatement.java:859)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)

Statement caching can:

Prevent the overhead of repeated cursor creation
Prevent repeated statement parsing and creation

Basics of Statement Caching

Use a statement cache to cache statements associated with a particular physical connection. For a simple connection, the cache is associated with an OracleConnection object. For a pooled connection, the cache is associated with an OraclePooledConnection or PooledConnection object. The OracleConnection and OraclePooledConnection objects include methods to enable statement caching. When you enable statement caching, a statement object is cached when you call the “close” methods.

Because each physical connection has its own cache, multiple caches can exist if you enable statement caching for multiple physical connections. When you enable statement caching on a pooled connection, all the logical connections will use the same cache. If you try to enable statement caching on a logical connection of a pooled connection, this will throw an exception.

There are two types of statement caching: implicit and explicit. Each type of statement cache can be enabled or disabled independent of the other: you can have either, neither, or both in effect. Both types of statement caching share a cache.

Implicit Statement Caching

When you enable implicit statement caching, JDBC automatically caches the prepared or callable statement when you call the close() method of this statement object. The prepared and callable statements are cached and retrieved using standard connection object and statement object methods.

Plain statements are not implicitly cached, because implicit statement caching uses a SQL string as a key, and plain statements are created without a SQL string. Therefore, implicit statement caching applies only to the OraclePreparedStatement and OracleCallableStatement objects, which are created with a SQL string. When one of these statements is created, the JDBC driver automatically searches the cache for a matching statement. The match criteria are the following:

The SQL string in the statement must be identical (case-sensitive) to one in the cache.
The statement type must be the same (prepared or callable).
The scrollable type of result sets produced by the statement must be the same (forward-only or scrollable). You can determine the scrollability when you create the statement.

If a match is found during the cache search, the cached statement is returned. If a match is not found, then a new statement is created and returned. The new statement, along with its cursor and state, are cached when you call the close() method of the statement object.

When a cached OraclePreparedStatement or OracleCallableStatement object is retrieved, the state and data information are automatically re-initialized and reset to default values, while metadata is saved. The Least Recently Used (LRU) scheme performs the statement cache operation.
Explicit Statement Caching

Explicit statement caching enables you to cache and retrieve selected prepared, callable, and plain statements. Explicit statement caching relies on a key, an arbitrary Java string that you provide.

Because explicit statement caching retains statement data and state as well as metadata, it has a performance edge over implicit statement caching, which retains only metadata. However, because explicit statement caching saves all three types of information for re-use, you must be cautious when using this type of caching–you may not be aware of what was retained for data and state in the previous statement.

With implicit statement caching, you take no special action to retrieve statements from a cache. Instead, whenever you call prepareStatement() or prepareCall(), JDBC automatically checks the cache for a matching statement and returns it if found.

With explicit statement caching, you use specialized Oracle “WithKey” methods to cache and retrieve statement objects.

Implicit statement caching uses the SQL string of a prepared or callable statement as the key, requiring no action on your part. Explicit statement caching requires you to provide a Java string, which it uses as the key.

During implicit statement caching, if the JDBC driver cannot find a statement in cache, it will automatically create one. During explicit statement caching, if the JDBC driver cannot find a matching statement in cache, it will return a null value.

To resolve “java.lang.NullPointerException” issue in WLS please try below settings:
Select the data source — > connection pool —> advanced —-> Make statement cache to zero
The above steps should resolve the NPE issue.

 

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.