[转]Oracle ORA-01403: no data found Exception SYS_REFCURSOR

本文转自:http://stackoverflow.com/questions/9104153/what-is-the-correct-way-to-deal-with-this-oracle-ora-01403-no-data-found-except

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.

 

相关文章