Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ORA-01403: no data found

Posted on 2011-03-17
5
Medium Priority
?
890 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 800 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 1200 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

730 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