Solved

ORA-01403: no data found

Posted on 2011-03-17
5
865 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 76

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

785 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