title: What is the correct way to deal with this Oracle ORA-01403: no data found Exception?
body: <p>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). </p>
  
  <p>This leads to the familiar <strong>ORA-01403: no data found</strong> 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. </p>
  
  <pre><code>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;
  </code></pre>