Oracle Pl/Sql - Ora-01403 "No Data Found" When Using "Select Into"
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...
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:
- To find the
DATE_REFERENCErow in whichDATE_GIVEN BETWEEN DATE_START AND DATE_END(easy); OR - If the previous option returns no data, I need to find the next closest
DATE_STARTtoDATE_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;
Must Read
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!