how to get the result set data from the procedure

ORACLE Retrivind data from a procedure

CREATE OR REPLACE PROCEDURE GET_ANS_TO_EDIT (
ARG_CAT_ID in varchar2,
ARG_LOB_ID_LIST in MKD_CHAR_ARRAY,
ARG_COUNTRY_CD_LIST in MKD_CHAR_ARRAY,
ARG_STATE_CD_LIST in MKD_CHAR_ARRAY,
ARG_QSTN_SEQ_LIST in MKD_NUM_ARRAY,
ARG_SEQ_QID_LIST in MKD_CHAR_ARRAY,
ARG_PAGE_NO in number,
ARG_ROWS_PER_PAGE in number,
ANSWER_COUNT out number,
PAGE_NO out number,
ANSWER_CURSOR out SYS_REFCURSOR,
STATUS out number)
IS
BEGIN
DECLARE
TOTAL_PAGES number;
START_REC_NO number;
END_REC_NO number;
LOB_SP_ID char(2);
BEGIN
STATUS:=0;
IF ARG_LOB_ID_LIST IS NULL THEN
DBMS_OUTPUT.PUT_LINE ('Lob list is empty.');
STATUS:=1;
RETURN;
END IF;

IF ARG_CAT_ID = 'C5' THEN
SELECT column_value INTO LOB_SP_ID FROM TABLE(ARG_LOB_ID_LIST)
WHERE ROWNUM < is_dashboard_country =" 'D'" country_cd="s.COUNTRY_CD" country_last_update_dt =" s.COUNTRY_LAST_UPDATE_DT" cat_id =" ARG_CAT_ID" lob_id =" l.LOB_ID" lob_last_updated_dt =" l.LOB_LAST_UPDATED_DT"> 0 THEN
TOTAL_PAGES := CEIL(ANSWER_COUNT/ARG_ROWS_PER_PAGE);

START_REC_NO := 0;
END_REC_NO := ARG_ROWS_PER_PAGE;

PAGE_NO := ARG_PAGE_NO;

IF PAGE_NO > TOTAL_PAGES THEN
PAGE_NO := TOTAL_PAGES;
END IF;

-- calculate next record start record and end record
IF PAGE_NO > 0 THEN
START_REC_NO := ABS((PAGE_NO-1)*ARG_ROWS_PER_PAGE);
END IF;

IF ANSWER_COUNT < (START_REC_NO+ARG_ROWS_PER_PAGE) THEN END_REC_NO := ANSWER_COUNT; ELSE END_REC_NO := START_REC_NO + ARG_ROWS_PER_PAGE; END IF; START_REC_NO := START_REC_NO + 1; OPEN ANSWER_CURSOR FOR SELECT QCSL.QSTN_ID, QCSL.SEQ_NO, QCSL.QSTN_SHRT_TX, QCSL.QSTN_LONG_TX, QCSL.LOB_ID, QCSL.LOB_DESC, a.ANSW_SHRT_TX, a.ANSW_LONG_TX, a.ANNOTATION_TX, QCSL.COUNTRY_DESC, QCSL.STATE_CD, QCSL.STATE_DESC, QCSL.ANSID, QCSL.RNUM FROM (SELECT * FROM ( SELECT TBL.*, ROWNUM as RNUM FROM ( SELECT q.QSTN_ID, q.SEQ_NO, q.QSTN_SHRT_TX, q.QSTN_LONG_TX, l.LOB_ID, l.LOB_DESC, cs.COUNTRY_DESC, cs.COUNTRY_CD, cs.STATE_CD, cs.STATE_DESC, cs.ANSID, cs.COUNTRY_LAST_UPDATE_DT, cs.STATE_LAST_UPDATE_DT, q.QSTN_LAST_UPDATE_DT FROM TQUESTION q, TLOB l, (SELECT CNTYSTATE.*, ROWNUM as ANSID FROM ( SELECT c.country_cd , c.country_desc, c.COUNTRY_LAST_UPDATE_DT, s.state_cd, s.state_desc, s.STATE_LAST_UPDATE_DT FROM tcountry c, TSTATE_PROVINCE s WHERE c.IS_DASHBOARD_COUNTRY = 'D' AND c.COUNTRY_CD=s.COUNTRY_CD AND c.COUNTRY_LAST_UPDATE_DT = s.COUNTRY_LAST_UPDATE_DT AND c.EXP_DT IS NULL AND s.EXP_DT IS NULL AND (c.COUNTRY_CD in (SELECT column_value FROM table(ARG_COUNTRY_CD_LIST)) OR s.STATE_CD in (SELECT column_value FROM table(ARG_STATE_CD_LIST))) ORDER by UPPER(c.country_desc), UPPER(s.state_desc) ) CNTYSTATE) cs WHERE q.CAT_ID = ARG_CAT_ID AND q.LOB_ID in (SELECT column_value FROM table(ARG_LOB_ID_LIST)) AND q.SEQ_NO in (SELECT column_value FROM table(ARG_QSTN_SEQ_LIST)) AND q.EXP_DT IS NULL AND q.LOB_ID = l.LOB_ID AND q.LOB_LAST_UPDATED_DT = l.LOB_LAST_UPDATED_DT AND l.EXP_DT IS NULL ORDER BY q.SEQ_NO ASC, UPPER(l.LOB_DESC) ASC, cs.ANSID ASC) TBL WHERE ROWNUM <= END_REC_NO ) WHERE RNUM >= START_REC_NO) QCSL LEFT OUTER JOIN TANSWER a
ON QCSL.QSTN_ID = a.QSTN_ID
AND QCSL.QSTN_LAST_UPDATE_DT = a.QSTN_LAST_UPDATE_DT
AND QCSL.COUNTRY_CD = a.COUNTRY_CD
AND QCSL.STATE_CD = a.STATE_CD
AND QCSL.COUNTRY_LAST_UPDATE_DT = a.COUNTRY_LAST_UPDATE_DT
AND QCSL.STATE_LAST_UPDATE_DT = a.STATE_LAST_UPDATE_DT
AND a.EXP_DT IS NULL;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
STATUS := 1;
DBMS_OUTPUT.PUT_LINE ('status 1');
DBMS_OUTPUT.PUT_LINE (SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('status 2');
DBMS_OUTPUT.PUT_LINE (SQLERRM);
STATUS := 2;
END;
END;



//How to handle in java



String callStatement = "{ call get_ans_to_edit(?,?,?,?,?,?,?,?,?,?,?,?) }";
dbConnection = getConnection(entityName);
prepStatement = dbConnection.prepareCall(callStatement);
oracle.sql.ArrayDescriptor num_array_type =
oracle.sql.ArrayDescriptor.createDescriptor("MKD_NUM_ARRAY", dbConnection);
oracle.sql.ArrayDescriptor str_array_type =
oracle.sql.ArrayDescriptor.createDescriptor("MKD_CHAR_ARRAY", dbConnection);
// Prepare Lob list
String lobIds[] = (String[]) editInfo.getLobList().toArray();
java.sql.Array lob_list = new
oracle.sql.ARRAY(str_array_type, dbConnection, lobIds);
// Prepare country state list
List countryCombo = (List) editInfo.getCountryList();
ArrayList countryList = new ArrayList();
ArrayList stateList = new ArrayList();
for(int j=0; j < countrystate =" countryCombo.get(j).toString().split("> 1) {
if (countryState[1] != null && !stateList.contains(countryState[1])) {
stateList.add(countryState[1]);
}
}
else {
if (countryState[0] != null && !countryList.contains(countryState[0]))
{
countryList.add(countryState[0]);
}
}
}

String cntryCds[] = new String[countryList.size()];
countryList.toArray(cntryCds);
java.sql.Array cntry_list = new
oracle.sql.ARRAY(str_array_type, dbConnection, cntryCds);
String stateCds[] = new String[stateList.size()];
stateList.toArray(stateCds);
java.sql.Array state_list = new
oracle.sql.ARRAY(str_array_type, dbConnection, stateCds);
Integer questionNos[] = new Integer[editInfo.getQuestionSeqNoList().size()];
editInfo.getQuestionSeqNoList().toArray(questionNos);
java.sql.Array qstn_list = new
oracle.sql.ARRAY(num_array_type, dbConnection, questionNos);
java.util.List seqQstnIdList = editInfo.getSequenceQstnIdList();
String seqQstnId_list[] = new String[seqQstnIdList.size()];
seqQstnIdList.toArray(seqQstnId_list);
java.sql.Array seqQstnId_array = new
oracle.sql.ARRAY(str_array_type, dbConnection, seqQstnId_list);

PageInfo pageInfo = editInfo.getPageInfo();
if (pageInfo != null) {
cPageNo = pageInfo.getCurrentPaginationIndex();
iPageNo = cPageNo;
}
prepStatement.setString(1, categoryId);
prepStatement.setObject(2, lob_list, oracle.jdbc.OracleTypes.ARRAY);
prepStatement.setObject(3, cntry_list, oracle.jdbc.OracleTypes.ARRAY);
prepStatement.setObject(4, state_list, oracle.jdbc.OracleTypes.ARRAY);
prepStatement.setObject(5, qstn_list, oracle.jdbc.OracleTypes.ARRAY);
prepStatement.setObject(6, seqQstnId_array, oracle.jdbc.OracleTypes.ARRAY);
prepStatement.setInt(7, cPageNo);
prepStatement.setInt(8, iShowRows);
prepStatement.registerOutParameter(9, oracle.jdbc.OracleTypes.INTEGER);
prepStatement.registerOutParameter(10, oracle.jdbc.OracleTypes.INTEGER);
prepStatement.registerOutParameter(11, oracle.jdbc.OracleTypes.CURSOR);
prepStatement.registerOutParameter(12, oracle.jdbc.OracleTypes.INTEGER);
prepStatement.execute();

iTotalRows = prepStatement.getInt(9);
cPageNo = prepStatement.getInt(10);
status_cd = prepStatement.getInt(12);
if (CATEGORY_LOB_SPECIFIC_DATA.equals(categoryId.trim())) {
isLobSpecific = true;
}
qsView.getResultStatus().setSuccessStatus(status_cd);
StringBuffer htmlResult = new StringBuffer();
if (status_cd == 0 && iTotalRows > 0) {
dbResults = (ResultSet) prepStatement.getObject(11);
iTotalPages=((int)(Math.ceil((double)iTotalRows/iShowRows)));
// if current page is grater then number of pages then set iPage to 0
if (iPageNo > iTotalPages) {
iPageNo = iTotalPages;
cPageNo = iPageNo;
}
if(iPageNo==0) {
iPageNo=0;
cPageNo=1;
}
else{
cPageNo=iPageNo;
iPageNo=Math.abs((iPageNo-1)*iShowRows);
}
//// calculate next record start record and end record
if(iTotalRows<(iPageNo+iShowRows))
{
iEndResultNo=iTotalRows;
}
else
{
iEndResultNo=(iPageNo+iShowRows);
}
iStartResultNo=(iPageNo+1);

PEGA12

PEGA11

PEGA10

PEGA9

PEGA8