Calling Stored Procedure from Spring StoredProcedure throws Exception

[Sean Shrestha]

I am using Spring JDBC to call SQL statements. Recently I wrote a service to call a SP on Oracle from Spring. As explained on the Spring documentation on the link below, I created a class that extends StoredProcedure and everything that has been explained.

http://static.springsource.org/sprin...toredProcedure

public class InsertDaoImpl extends JdbcDaoSupport implements
	InsertDao {
	
	private Logger logger = Logger.getLogger(this.getClass());

	public int runStoreProc(String spName) {
		int status = -1;
		try {
			MyStoreProcedure sp = new MyStoreProcedure (getJdbcTemplate().getDataSource(), spName);
			sp.execute();
			status = 1;
		} catch (Exception e) {
			logger.error("SQL Exception occurred at runStoreProc.", e);
		}
		return status;
	}
	
	private class MyStoreProcedure extends StoredProcedure {
		public MyStoreProcedure(DataSource ds, String spName) {
			super.setDataSource(ds);
			super.setFunction(true);
			super.setSql(spName);
			super.compile();
		}
		
		public Map execute() {
			return super.execute(new HashMap());
		}
	}
}

I am getting the following exception.

  
org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call schema.sp_test()}]; SQL state [72000]; error code [1008]; ORA-01008: not all variables bound
; nested exception is java.sql.SQLException: ORA-01008: not all variables bound

Caused by: 
java.sql.SQLException: ORA-01008: not all variables bound

	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
	at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
	at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
	at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
	at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
	at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
	at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:656)
	at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:877)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:842)
	at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:875)
	at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:113)

  

parameter spName has the schema name and the SP name. example: [schema].[sp_test]

When I just run the SP on DBVisualizer Query Browser, the SP executes as desired.

This is the first time I am writing Spring to call SP. Please suggest me what I am missing. Thanks in advance.

[Sean Shrestha]

fixed it by just making this change.

  
private class ApolloStoreProcedure extends StoredProcedure {
		public ApolloStoreProcedure(DataSource ds, String spName) {
			super(ds, spName);
			super.compile();
		}

		public Map execute() {
			return super.execute(new HashMap());
		}
	}
  

[sushant verma]

Hi, I'm bit confused with the calling pattern of Stored Procedure using jdbcTemplate.
Do i need to add a new class everytime a new SP is to be called?

I WANT to call a SP with some IN and OUT parameters and want to store the result in some DTO object and want to do this using spring in such a manner
that many functions can be listed in a sinlge class some XXXDAO.java

So that it can be called from a bllImp class with some input arguments and gets a class object DTO/String/List/Result set in return.

Please give a running example or provide a .war with the example code or any link where i can clear my doubts.

Thanks in Advance.

[Mark Spritzler]

This thread is over a year old. Please don't resurrect old threads. Just create a new thread with your new question.

Thanks, I am going to close this thread.

Mark