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?