Solved

ORA-01403: no data found

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql for Oracle views 8 46
How to return an OUT parameter from and ORACLE 3 55
Oracle and DateTime math 6 25
Wrong number of values in the INTO list of a FETCH statement 16 42
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now