Solved

ORA-01403: no data found

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

679 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