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

0 votes

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?

Sep 17 in Database by Kithuzzz
• 12,240 points
42 views

1 answer to this question.

0 votes

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.

answered Sep 18 by narikkadan
• 20,880 points

Related Questions In Database

0 votes
1 answer

ORACLE LIVE SQL IMPORT CSV DATA (live oracle sql)

Convert CSV to SQL http://www.convertcsv.com/csv-to-sql.htm This programme may be ...READ MORE

answered Feb 11 in Database by Neha
• 8,920 points
3,207 views
0 votes
1 answer

What is the Oracle PL/SQL "--+rule"?

The answer is in the material you ...READ MORE

answered Feb 23 in Database by Neha
• 8,920 points
122 views
0 votes
0 answers

Oracle SQL: Update a table with data from another table

Table 1: id name ...READ MORE

Aug 13 in Database by Kithuzzz
• 12,240 points
53 views
0 votes
0 answers
0 votes
1 answer

Please name some online websites to compile and run PL/SQL?

For executing Oracle SQL queries and PL/SQL ...READ MORE

answered Feb 11 in Database by Neha
• 8,920 points
154 views
0 votes
1 answer

Number of rows affected by an UPDATE in PL/SQL

You can try using the following query: SELECT ...READ MORE

answered Feb 14 in Database by Vaani
• 7,020 points
208 views
0 votes
1 answer

SQL error "ORA-01722: invalid number"

When an effort is made to convert ...READ MORE

answered Feb 15 in Database by Neha
• 8,920 points
846 views
0 votes
0 answers

Display names of all constraints for a table in Oracle SQL

I have given each constraint a name ...READ MORE

Aug 15 in Database by Kithuzzz
• 12,240 points
63 views
0 votes
1 answer

How can I confirm a database is Oracle & what version it is using SQL?

Run this SQL: select * from v$version; Output: BANNER ---------------------------------------------------------------- Oracle Database ...READ MORE

answered Sep 12 in Database by narikkadan
• 20,880 points
30 views
0 votes
1 answer

How to display databases in Oracle 11g using SQL*Plus

A MySQL "database" can be compared to ...READ MORE

answered Sep 12 in Database by narikkadan
• 20,880 points
51 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP