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

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 2

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.