Oracle PL/SQL - ORA-01403 "No data found" when using "SELECT INTO"

Question

I faced this problem while developing a Trigger in Oracle: ORA-01403: no data found. I did some research and understood the root of the problem. Nevertheless handling the error exception prevents the above error, but does not solve my problem.

What I am currently looking for is an optimal 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.

Scenario

I have a "date reference" table to establish periods of time, say:

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

When the trigger is triggered, I'll have one DATE field - say DATE_GIVEN (for example sake). What I need is:

1. To find the DATE_REFERENCE row in which DATE_GIVEN BETWEEN DATE_START AND DATE_END (easy); OR
2. If the previous option returns no data, I need to find the next closest DATE_START to DATE_GIVEN.

In both cases, I need to retrieve the row with all columns from table DATE_REFERENCE, no matter if it matches Opt 1 or 2. That's exactly where I faced the problem described.

I wrote this test block to test and try to find a solution. The example below is not working, I know; but it is exactly what I want to accomplish (in concept). I have added comments like -- Lots of code to make clear that will be part of a more elaborate trigger:

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

Question

Knowing that the above PL/SQL block is more of a concept than working code, what is the best way to get RESULTROW populated, minding performance and the lesser queries as possible?

Sorry for the long question, but I figured scenario explanation was necessary. Thanks in advance for any help/thoughts!

Answer 1

Just populate the field directly, using ordering and rownum:

SELECT * INTO RESULTROW
FROM (SELECT *
      FROM DATE_REFERENCE
      ORDER BY (CASE WHEN DATE_GIVEN BETWEEN DATE_START AND DATE_END
                     THEN 1 ELSE 0
                END) DESC,
               (DATE_START - DATE_GIVEN)
     ) t
WHERE rownum = 1;

This will populate the information with one query.

EDIT:

If you want to put a condition in the subquery, it needs to be:

SELECT * INTO RESULTROW
FROM (SELECT *
      FROM DATE_REFERENCE
      WHERE DATE_GIVEN <= DATE_END
      ORDER BY (CASE WHEN DATE_GIVEN BETWEEN DATE_START AND DATE_END
                     THEN 1 ELSE 0
                END) DESC,
               (DATE_START - DATE_GIVEN)
     ) t
WHERE rownum = 1;

I believe the right condition is DATE_GIVEN <= DATE_END. This covers both the between condition and should imply DATE_GIVEN < DATE_START. This assumes that DATE_END is never NULL.