Hiall,
I am using a stored procedure to get data from Oracle, am using RowMapper Implementators to map with the outparameter of the stored procedures.
Now we have a senario where we need a cursor as a collumn of the another cursor..... so can any one suggest me how to handle (in the spring way) or is there any best praticses in this....

Currently we are getting the internal cursor by a resultSet.getObject method
from that we are processing ......


Current sample code

public class FetchCourseSearchResultsSP extends StoredProcedure
public FetchCourseSearchResultsSP(DataSource ds)
{
setDataSource(ds);
setSql(HCConstants.FETCH_COURSE_SEARCH_RESULTS);
declareParameter(new SqlParameter("p_user_id", OracleTypes.NUMBER));
declareParameter(new SqlParameter("p_affiliate_id", OracleTypes.NUMBER));
declareParameter(new SqlParameter("p_region_id", OracleTypes.NUMBER));
declareParameter(new SqlParameter("p_aus_study_mode_id", OracleTypes.NUMBER));
declareParameter(new SqlParameter("p_aus_qual_level_id", OracleTypes.NUMBER));
declareParameter(new SqlParameter("p_search_text", OracleTypes.VARCHAR));
declareParameter(new SqlParameter("p_county_state_id", OracleTypes.NUMBER));
declareParameter(new SqlParameter("p_category_code", OracleTypes.VARCHAR));
declareParameter(new SqlParameter("p_category_display_level", OracleTypes.NUMBER));
declareParameter(new SqlParameter("p_search_on_what", OracleTypes.VARCHAR));
declareParameter(new SqlParameter("p_institution_id", OracleTypes.NUMBER));
declareParameter(new SqlParameter("p_page_no", OracleTypes.NUMBER));
declareParameter(new SqlParameter("p_order_by_what", OracleTypes.VARCHAR));
declareParameter(new SqlParameter("p_qual_option_desc", OracleTypes.VARCHAR));
declareParameter(new SqlOutParameter("p_total_inst_cnt", OracleTypes.NUMBER));

declareParameter(new SqlOutParameter("pc_course_results", OracleTypes.CURSOR, new CourseListRowMapperImpl()));


declareParameter(new SqlOutParameter("p_error_msg",
OracleTypes.VARCHAR));
declareParameter(new SqlOutParameter("p_ret_code", OracleTypes.NUMBER));
compile();
}

public Map execute(List params, HttpServletRequest request)
{
Map outMap = null;
try
{
// To translate keyword to english
String keyword = (String)params.get(1);
String countryDomain = HCIUtil.getCountryDomain(request);
ResourceBundle defaultbundle = ResourceBundle.getBundle("com.hcint.resources.Appl icationResources_language");
if(countryDomain != null && (defaultbundle.getString("nonenglish.domain").inde xOf(countryDomain)!=-1)) {
if(keyword != null && !keyword.equalsIgnoreCase("null")) {
String language = HCIUtil.getTranslateLang(defaultbundle,request);
keyword = Translate.translate(keyword, language, Language.ENGLISH);
}
}
Map inMap = new HashMap();
inMap.put("p_user_id", params.get(13)); // user id.
inMap.put("p_affiliate_id", params.get(14)); // affiliate id.
inMap.put("p_user_agent", params.get(16)); // user id.
inMap.put("p_client_id", params.get(15)); // affiliate id.
inMap.put("p_region_id", params.get(4)); // country id.
inMap.put("p_study_mode_id", params.get(9)); // study mode id.
inMap.put("p_int_qual_rel_id", params.get(8)); // qualification level id(Includes child qualification Id).
inMap.put("p_qual_display_level", params.get(18)); // Qualification display level.
inMap.put("p_qual_parent_rel_id", params.get(17)); // Qualification parent id(NULL if we display only parents).
inMap.put("p_keyword", keyword); // keyword entered by the user.
inMap.put("p_county_state_id", params.get(7)); // county / state id.
inMap.put("p_category_code", params.get(6)); // category code.
inMap.put("p_category_display_level", params.get(11)); // category display level.
inMap.put("p_keyword_search_on_what", String.valueOf(params.get(3)).toUpperCase()); // where to search, either course title or course title and summary.
inMap.put("p_institution_id", null); // institution id. (For future use).
inMap.put("p_page_no", params.get(12)); // for pagination. by default 1.
inMap.put("p_order_by_what", params.get(10));
inMap.put("p_request_url", params.get(21));
inMap.put("p_referred_url", params.get(20));


outMap = execute(inMap);
} catch (Exception e)
{
e.printStackTrace();
}
return outMap;
}





private class CourseListRowMapperImpl implements RowMapper
{
StringBuffer bf = new StringBuffer(10);
public Object mapRow(ResultSet rs, int rownum)
{
CourseBrowseVO coursebrowsevo = new CourseBrowseVO();
try
{
coursebrowsevo.setCourseCount(rs.getString("course _cnt"));
coursebrowsevo.setCollegeId(rs.getString("institut ion_id"));
coursebrowsevo.setCollegeName(rs.getString("instit ution_name"));
coursebrowsevo.setReviewRating(rs.getString("revie w_rating"));
coursebrowsevo.setReviewCount(rs.getString("review _cnt"));



ArrayList options = new ArrayList();

ResultSet advertResultSet = (ResultSet)rs.getObject("profile_rec");
try
{
//Iterating the cursor
while (advertResultSet.next())
{
//Instantiating the option value type
CourseBrowseVO coursebrowsevo1 = new CourseBrowseVO();
coursebrowsevo1.setKeyId(advertResultSet.getString ("key_id"));
coursebrowsevo1.setSection(advertResultSet.getStri ng("section"));
coursebrowsevo1.setMediaPath(advertResultSet.getSt ring("media_path"));
coursebrowsevo1.setMediaTypeId(advertResultSet.get String("media_type_id"));
coursebrowsevo1.setWhatProfile(advertResultSet.get String("what_profile"));
coursebrowsevo1.setAdvertName(advertResultSet.getS tring("advert_name"));
coursebrowsevo1.setInstProfileFlag(advertResultSet .getString("inst_profile_exists"));
coursebrowsevo1.setSubjProfileCount(advertResultSe t.getString("subj_profile_count"));

if (advertResultSet.getString("sys_var_value") != null)
{

if (advertResultSet.getString("media_type_id") != null && advertResultSet.getString("media_type_id").equalsI gnoreCase("1"))
{
bf.delete(0, bf.length());
bf.append(advertResultSet.getString("sys_var_value ") + advertResultSet.getString("media_path"));
if (bf != null && bf.lastIndexOf(".") != -1)
{
bf.insert(bf.lastIndexOf("."), "t");
coursebrowsevo1.setMediaPath(advertResultSet.getSt ring("sys_var_value") + advertResultSet.getString("media_path"));
coursebrowsevo1.setThumbNails(bf.toString()); //seting thumbnail path

}

}
}


options.add(coursebrowsevo1);
}
coursebrowsevo.setAdvertList(options);
} catch (Exception e)
{
throw new SQLException(e.getMessage());
} finally
{
try
{
advertResultSet.close();
} catch (Exception e)
{
throw new SQLException(e.getMessage());
}
}


} catch (Exception e)
{
e.printStackTrace();
}
return coursebrowsevo;
}
}