This leads to the familiar ORA-01403: no data found exception. I tried to change the SP so that it would return NULL record in this case - the same sort of result you'd get it a query couldn't find any records - but to no avail. I am doing something wrong here.
PROCEDURE sp_GetNextEmailFromQueue (pAgentId IN NUMBER, pRecs OUT recordSet)IS
EMAIL_ID INTEGER;BEGINSELECT id INTO EMAIL_ID FROM(SELECT id, is_replied_to, is_being_worked, date_received
FROM SSQ_EMAILS
WHERE is_replied_to =0AND is_being_worked =0ORDERBY date_received ASC)WHERE rownum =1;UPDATE SSQ_EMAILS x
SET x.is_being_worked =1,
x.agent_id = pAgentId,
x.work_started_date = SYSDATE
WHERE x.id = EMAIL_ID;OPEN pRecs FORSELECT x.id,
x.message_id,
x.to_email,
x.from_email,
x.subject,
x.message,
x.date_received,
x.href_link,
x.is_being_worked,
x.work_started_date,
x.is_replied_to
FROM SSQ_EMAILS x
WHERE x.id = EMAIL_ID;
EXCEPTION
WHEN no_data_found
THEN OPEN pRecs FOR SELECT NULL FROM SSQ_EMAILS;
END;
答:
Solved it by doing this:
EXCEPTION
WHEN no_data_found THEN
OPEN pRecs FOR
SELECT NULL FROM SSQ_EMAILS s
WHERE s.id ISNULL;
It works because the RefCursor has to be opened.
I need an empty result, and this seems like a safe way to guarantee that, because the ID is the PK and cannot be null.