Hi all,
Below is the code to call Stored Procedure(SP).
RetrieveTerTemplateProc retTerTemplateProc = new RetrieveTerTemplateProc(
ServiceLocator.getInstance().getTMDataSource(), "DELETE_TERRITORY_PR");
try {
Map myMap = retTerTemplateProc.execute(territoryId);
_log.info("Map size :: "+myMap.size());
} catch (Exception e) {
throw new MarshSQLException("ERROR IN APPROVAL SQL", e);
}
RetrieveTerTemplateProc(Inner class)
------------------------
class RetrieveTerTemplateProc extends StoredProcedure {
public RetrieveTerTemplateProc(DataSource ds, String spName) {
super(ds, spName);
declareParameter(new SqlParameter("p_territory_id", Types.VARCHAR));
declareParameter(new SqlOutParameter("v_error", Types.NUMERIC));//errorcode
declareParameter(new SqlOutParameter("v_error_msg", Types.VARCHAR));//error msg
compile();
}
public Map execute(int territoryId) {
Map inParam = new HashMap();
inParam.put("p_territory_id", String.valueOf(territoryId));
return super.execute(inParam);
}
}
-----------------------------------
It gets failed at super.execute(inParam); line.
If I check with the log no response from Oracle.
After some 10-15 mts it gives below message in log file.
ThreadMonitor W WSVR0605W: Thread "ORB.thread.pool : 1" (00000066) has been active for 634359 milliseconds and may be hung. There are 1 threads in total in the server that may be hung.
After this the server went to hung state.
If I try for simple SQL call with JdbcTemplate it is working fine and fetching all values.But for Stored Procedure is not responding.
I'm using Oracle9i Enterprise Edition Release 9.2.0.1.0 as back end.
Driver is Oracle JDBC driver 10.1.0.2.0
Quries :
1) is it because of Driver version?
2) what is the impact of setFunction(true) not provided in the code?
Please help me .....
Regards, Visa.