Endless Loop in JdbcTemplate class with Oracle stored proc

[dortman]

Hi,

I'm trying to execute a store procedure in Oracle. Every time I tried, my application hung while my CPU utiliztion remained high. I traced the problem to an endless loop which is occuring in the JdbcTemplate class.

I then went and copied this code exactly:
http://www.springframework.org/docs/...toredProcedure

This code also results in an endless loop in the extractReturnedResultSets method. The code in the JdbcTemplate class:

	protected Map extractReturnedResultSets(CallableStatement cs, List parameters, int updateCount) throws SQLException {
		Map returnedResults = new HashMap();
		int rsIndex = 0;
		boolean moreResults; 
		do {
			if (updateCount == -1) {
				Object param = null;
				if (parameters != null && parameters.size() > rsIndex) {
					param = parameters.get(rsIndex);
				}
				if (param instanceof SqlReturnResultSet) {
					SqlReturnResultSet rsParam = (SqlReturnResultSet) param;
					returnedResults.putAll(processResultSet(cs.getResultSet(), rsParam));
				}
				else {
					logger.warn("ResultSet returned from stored procedure but a corresponding " +
											"SqlReturnResultSet parameter was not declared");
				}
				rsIndex++;
			}
			moreResults = cs.getMoreResults();
			updateCount = cs.getUpdateCount();
			if (logger.isDebugEnabled()) {
				logger.debug("CallableStatement.getUpdateCount returned [" + updateCount + "]");
			}
		}
		while (moreResults || updateCount != -1);
		return returnedResults;
	}

When I run it, moreResults becomes false, while updateCount is 1, and the do while loop never terminates.

Can somebody verify that this problem isn't exclusive to my environment?

Thanks,
-Dave

[dortman]

Is it some sort of law that you'll find the source of the problem as soon as you post an inquiry to a forum? :roll:

I swapped out our Oracle JDBC drivers. I think I had the drivers for release 1, not release 2:

http://www.oracle.com/technology/sof...jdbc/index.htm

Thanks,
-Dave

[trisberg]

This seems to be a feature of Oracle's 10g driver (driver version is 10.1.0.2.0). This is the most recent driver that is currently available for download.

Funny thing is that the ojdbc14.jar that comes with WebLogic SP3 works fine and it reports the exact same version number. It is slightly different size though so it must be a different build. WebLogic's ojdbc14.jar is 1,353,229 bytes vs 1,352,918 bytes for the one downloaded from Oracle's OTN site.

I'll have to do some more research into this. What version did you use when you hit this?

[dortman]

This seems to be a feature of Oracle's 10g driver (driver version is 10.1.0.2.0). This is the most recent driver that is currently available for download.

Funny thing is that the ojdbc14.jar that comes with WebLogic SP3 works fine and it reports the exact same version number. It is slightly different size though so it must be a different build. WebLogic's ojdbc14.jar is 1,353,229 bytes vs 1,352,918 bytes for the one downloaded from Oracle's OTN site.

I'll have to do some more research into this. What version did you use when you hit this?

It seems I was using Oracle's 10g drivers. I had thought that I had the 9.1 release - seems I need to pay more attention to what drivers I'm using.

The 9.2 drivers work fine, though I suppose you knew that. The JAR file which was creating problems for me is the 10g build you were referring to (1,352,918 bytes).

Thanks,
-Dave

[trisberg]

This is indeed a published bug in the Oracle 10.1.0.2 driver. (Bug #3563038 from May 19, 2004) where Statement.getUpdateCount() returns 1 instead of -1.

It is fixed in 10.1.0.3 (part of server patch set) and will also be fixed in 10g Release 2.

It also appears to be fixed in the 10.1.0.2 version distributed as part of WebLogic 8.1 SP3.

[dortman]

This is indeed a published bug in the Oracle 10.1.0.2 driver. (Bug #3563038 from May 19, 2004) where Statement.getUpdateCount() returns 1 instead of -1.

It is fixed in 10.1.0.3 (part of server patch set) and will also be fixed in 10g Release 2.

It also appears to be fixed in the 10.1.0.2 version distributed as part of WebLogic 8.1 SP3.

Thanks for the info,
-Dave