• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem: Spring - Stored Procedure call

 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Viswa Ambal
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
It is working fine.
It is because of Driver Version conflict.

Thanks.
-Visa
 
reply
    Bookmark Topic Watch Topic
  • New Topic