title: Oracle PL/SQL - ORA-01403 "No data found" when using "SELECT INTO"
body: <p>I faced this problem while developing a Trigger in Oracle: <a href="http://www.techonthenet.com/oracle/errors/ora01403.php" rel="noreferrer">ORA-01403: no data found</a>. I did some research and understood the root of the problem. Nevertheless <a href="http://www.orafaq.com/wiki/ORA-01403" rel="noreferrer">handling the error exception</a> prevents the above error, but does not solve my problem.</p>
  
  <p>What I am currently looking for is an <em>optimal</em> workaround to perform the lesser query amount/achieve the best performance as possible. I'll try to describe the scenario creating simple examples to the real structure.</p>
  
  <h2>Scenario</h2>
  
  <p>I have a "date reference" table to establish periods of time, say:</p>
  
  <pre><code>CREATE TABLE DATE_REFERENCE (
      DATE_START                  DATE NOT NULL,
      DATE_END                    DATE NOT NULL,
      -- Several other columns here, this is just a silly example
      CONSTRAINT PK_DATE_REFERENCE PRIMARY KEY(DATE_START, DATE_END)
  );
  </code></pre>
  
  <p>When the trigger is triggered, I'll have one <code>DATE</code> field - say <code>DATE_GIVEN</code> (for example sake). What I need is:</p>
  
  <ol>
  <li>To find the <code>DATE_REFERENCE</code> row in which <code>DATE_GIVEN BETWEEN DATE_START AND DATE_END</code> (easy); <em>OR</em></li>
  <li>If the previous option returns <em>no data</em>, I need to find the next closest <code>DATE_START</code> to <code>DATE_GIVEN</code>.</li>
  </ol>
  
  <p>In both cases, I need to retrieve <strong>the row with all columns</strong> from table <code>DATE_REFERENCE</code>, no matter if it matches Opt 1 or 2. That's exactly where I faced the problem described.</p>
  
  <p>I wrote this test block to <em>test</em> and try to find a solution. The example below <strong>is not working</strong>, I know; <strong>but it is <em>exactly</em> what I want to accomplish</strong> (in concept). I have added comments like <code>-- Lots of code</code> to make clear that will be part of a more elaborate trigger:</p>
  
  <pre><code>DECLARE
      DATE_GIVEN       DATE; 
      RESULTROW        DATE_REFERENCE%ROWTYPE;
  BEGIN
  
      -- Lots of code
      -- Lots of code
      -- Lots of code
  
      DATE_GIVEN := TO_DATE('2014-02-26 12:30:00', 'YYYY-MM-DD HH24:MI:SS');
  
      -- This one throws the ORA-01403 exception if no data was found
      SELECT 
         * INTO RESULTROW
      FROM
         DATE_REFERENCE
      WHERE
         DATE_GIVEN BETWEEN DATE_START AND DATE_END;
  
      -- If the above didn't throw exceptions, I would continue like so:
      IF RESULTROW IS NULL THEN
  
          SELECT 
             * INTO RESULTROW
          FROM
             DATE_REFERENCE
          WHERE
             DATE_START &gt; DATE_GIVEN
             AND ROWNUM = 1
          ORDER BY DATE_START ASC;
  
      END IF;
  
      -- Now RESULTROW is populated, and the rest of the trigger code gets executed ~beautifully~
  
      -- Lots of code
      -- Lots of code
      -- Lots of code
  
  END;
  </code></pre>
  
  <h2>Question</h2>
  
  <p>Knowing that the above PL/SQL block is more of a <strong><em>concept</em></strong> than <strong><em>working code</em></strong>, what is the best way to get <code>RESULTROW</code> populated, minding performance and the lesser queries as possible?</p>
  
  <p>Sorry for the long question, but I figured scenario explanation was necessary. Thanks in advance for any help/thoughts!</p>