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

Question

While creating an Oracle trigger, I ran into the ORA-01403: no data found. issue. I looked into the issue and discovered its cause. Although handling the error exception stops the aforementioned error, it does not resolve my issue.

I'm currently looking for the best workaround to handle the less queries and attain the optimum performance. I'll try to explain the scenario by using straightforward examples to show the actual 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)
);

One DATE field, say DATE GIVEN, will be present when the trigger is activated (for example sake). What I require is:

To locate the DATE REFERENCE row where DATE GIVEN IS BETWEEN DATE START AND DATE END (simple);
Finding the next closest DATE START to DATE GIVEN is necessary if the preceding option fails to produce any results.

Regardless of whether it matches Opt 1 or 2, I must in both situations extract the row from table DATE REFERENCE that has all of the columns. I encountered the issue there just as it was mentioned.

I created this test block in an effort to test and research a solution. I am aware that the example below is ineffective, but it achieves the goal I have in mind (in concept). I've included 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?

narikkadan · Answer 1 · Sep 18, 2022

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.