Solved

ORA-01403: no data found

Posted on 2011-03-17
5
871 Views
Last Modified: 2012-05-11
what is wrong with this code it gives
ORA-01403: no data found
ORA-06512: at line 21

//* Formatted on 3/17/2011 11:21:40 AM (QP5 v5.126.903.23003) */
DECLARE
   CURSOR a_cur
   IS
      SELECT   *
        FROM   myschema.order_tab pc;

   a_rec   a_cur%ROWTYPE;
   f1      DATE;
   f2      DATE;
   r1      DATE;
   r2      DATE;
BEGIN
   OPEN a_cur;

   LOOP
       BEGIN
      FETCH a_cur INTO a_rec;

      EXIT WHEN a_cur%NOTFOUND;

      SELECT   FFS_BASELINE_START_DT_USER,
               FFS_BASELINE_END_DT_USER,
               RX_BASELINE_START_DT_USER,
               RX_BASELINE_END_DT_USER
        INTO   f1,
               f2,
               r1,
               r2
        FROM   myschema_reports.ca_baseline
       WHERE   project_number = a_rec.project_number;

      UPDATE   myschema.order_tab
         SET   FFS_BASELINE_START_DATE = f1,
               FFS_baseline_end_date = f2,
               RX_BASELINE_START_DATE = r1,
               RX_BASELINE_END_DATE = r2
       WHERE   project_number = a_rec.project_number;
    END;
   END LOOP;

   CLOSE a_cur;
END;

0
Comment
Question by:it-rex
5 Comments
 
LVL 4

Expert Comment

by:qasim_md
ID: 35157667
I know I am asking a stupid question. pls check if you have data in your table :order_tab
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35157690
It's probably the ca_baseline table.  There is probably no rows for one of the projects in the cursor.
0
 
LVL 7

Assisted Solution

by:Piloute
Piloute earned 200 total points
ID: 35157697
Hi,

Your select statement returns no rows. Have you run your 'SELECT...' outside your PL code ?

BY the way, in order to avoid ugly error messages, you can add an 'EXCEPTION' catching in your code before the 'END statement:

EXCEPTION
      WHEN NO_DATA_FOUND THEN
         <you can do do smething here, like displaying a message, etc...>;
      WHEN OTHERS THEN
         RAISE;


Cheers,
P
0
 
LVL 11

Accepted Solution

by:
Akenathon earned 300 total points
ID: 35157723
Nothing is wrong... your SELECT inside the LOOP is not finding any matching data and therefore raises the 1403 exception. You can enclose it into a block with an exception handler:

   LOOP
       BEGIN
      FETCH a_cur INTO a_rec;

      EXIT WHEN a_cur%NOTFOUND;

      BEGIN
        SELECT   FFS_BASELINE_START_DT_USER,
                 FFS_BASELINE_END_DT_USER,
                 RX_BASELINE_START_DT_USER,
                 RX_BASELINE_END_DT_USER
          INTO   f1,
                 f2,
                 r1,
                 r2
          FROM   myschema_reports.ca_baseline
         WHERE   project_number = a_rec.project_number;
     EXCEPTION WHEN NO_DATA_FOUND
      -- Do something about it, for instance set f1 f2 f3 to NULL

      END;


      UPDATE   myschema.order_tab
         SET   FFS_BASELINE_START_DATE = f1,
               FFS_baseline_end_date = f2,
               RX_BASELINE_START_DATE = r1,
               RX_BASELINE_END_DATE = r2
       WHERE   project_number = a_rec.project_number;
    END;
   END LOOP;
0
 
LVL 11

Author Closing Comment

by:it-rex
ID: 35157890
Thanks all; very good
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question