What is the correct way to deal with this Oracle ORA-01403: no data found Exception?

Question

I have a DB table that I am more or less treating like a queue. I'm trying to fetch a single item from it. This works, except when the SELECT..INTO fails (which can happen if there is only one item in the queue and two users on separate machines try and fetch it; only one is going to win).

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;
  BEGIN

     SELECT id INTO EMAIL_ID FROM
           (SELECT id, is_replied_to, is_being_worked, date_received 
           FROM SSQ_EMAILS
           WHERE is_replied_to = 0 AND is_being_worked =0
           ORDER BY 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 FOR
      SELECT 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;

Answer 1

I'd put the exception handler around the piece of code that is actually causing the error to be thrown. If email_id is NULL, the UPDATE will not update any rows and the SELECT will not return any rows.

PROCEDURE sp_GetNextEmailFromQueue (pAgentId IN NUMBER, pRecs OUT recordSet)
  IS
  EMAIL_ID INTEGER;
  BEGIN
    BEGIN
      SELECT id 
        INTO EMAIL_ID 
        FROM (SELECT id, is_replied_to, is_being_worked, date_received 
                FROM SSQ_EMAILS
               WHERE is_replied_to = 0 AND is_being_worked =0
               ORDER BY date_received ASC)
       WHERE rownum = 1;
    EXCEPTION
      WHEN no_data_found 
      THEN
        email_id := null;
    END;

    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 FOR
      SELECT 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;
  END;

Be aware, though, that this code does not prevent two different callers from working on the same row. If two session call this procedure at the same time, it's entirely possible that both will select the same row. If you want to prevent that, the SELECT would need to lock the row it selected with the FOR UPDATE clause.

Answer 2

You can do nothing in this case:

exception
  when no_data_found then
    null;
end;

This will return null in pRecs right?

EDIT

Second approach:

cursor c_mail is
  SELECT id  
    FROM
       (SELECT id, is_replied_to, is_being_worked, date_received 
          FROM SSQ_EMAILS
         WHERE is_replied_to = 0 AND is_being_worked =0
         ORDER BY date_received ASC)
   WHERE rownum = 1;
....
open c_mail;
fetch c_mail into email_id; -- no_data_found not happens
close c_mail;

Answer 3

How about this, avoiding exception handling:

/* returns X */
SELECT DUMMY FROM DUAL WHERE 1 = 1;

/* no data found */
SELECT DUMMY FROM DUAL WHERE 1 = 0;

/* returns NULL */
SELECT MIN(DUMMY) FROM DUAL WHERE 1 = 0;

Answer 4

select null from ssq_emails will still get a 1403 if there are no records in the table. I'm not sure you want to do anything in the exception handler; not sure how your caller will deal with pRecs being empty though.

Answer 5

Solved it by doing this:

EXCEPTION
   WHEN no_data_found THEN 
      OPEN pRecs FOR
      SELECT NULL
      FROM SSQ_EMAILS  s
      WHERE s.id IS NULL;

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.