Below is my SP.
I want to make sure the SELECT statement returns only the rows updated by UPDATE statement.
Actual problem is - my application is running on more than one server and this SP (select statement) is returning the same rows to more than one server. It should not do like that. It should NOT return the same records to any other server.
Please suggest me how to achive this.
SET WNDW_SVC_ID = i_server_id
WHERE MQ_ID IN (SELECT MQ_ID
FROM (SELECT MQ_id, ROWNUM rn
FROM (SELECT MQ_ID
WHERE STATUS = '1'
AND NVL (WNDW_SVC_ID, 0) = 0
ORDER BY MQ_ID
WHERE rn <= i_rec_count
OPEN o_recordset FOR
SELECT MQD.MQ_ID, MQD.MQ_TYPE, MQD.MQ_DATA_COL.GETSTRINGVAL() XMLDATA, MQD.S_ROW_UPD
FROM GPQDBO.GPQ_MQ_DATA MQD
WHERE WNDW_SVC_ID = i_server_id
AND STATUS = '1'; -- 1 scheduled to process.
WHEN NO_DATA_FOUND THEN
WHEN OTHERS THEN
raise_application_error (-20101, SQLERRM, TRUE);