1 2 Назад Вперед 16 Ответы Последний ответ: 17.08.2010 14:35, автор: 791940

    ORA-01403 - no data found

    782404
      Hi,

      I tried to execute a pl/sql but got this error..

      ORA-01403: no data found
      ORA-06512: at "TRY.CHECK_REMINDER", line 49
      ORA-06512: at line 2

      However, when i execute the select statement as in the pl/sql..there are many rows returned ..

      What else should i do?


      Thank you
        • 1. Re: ORA-01403 - no data found
          Karthick2003
          May be you are looking at the wrong query. Can you post your code?
          • 2. Re: ORA-01403 - no data found
            782404
            select c.KM_KPI into kpi from trail_file c where c.KM_ID = emp_rec.pks_km_id;

            emp_rec is this select statement:
            select * from movement_file where TASK_FLAG=7 and TASK_STATUS='InProgress';


            This statement is the sql i executed, identical as per the pl/sql and returned 4 rows

            select * from movement_file a, trail_file b where a.TASK_FLAG=7 and a.TASK_STATUS='InProgress'
            and a.receive_date is null
            and a.pks_km_id = b.km_id


            Thank you.
            • 3. Re: ORA-01403 - no data found
              Peter Gjelstrup
              Hi,

              You did not post your code as Karthick asked you to, so we still do not know what's in line 49.

              NO_DATA_FOUND is not necessarily related to a query.

              You may wanna look into http://soft.buaa.edu.cn/oracle/bookshelf/Oreilly/prog2/ch08_08.htm

              Regards
              Peter
              • 4. Re: ORA-01403 - no data found
                782404
                CREATE OR REPLACE PROCEDURE TRY.check_reminder AS
                NO number;
                CURSOR emp_cur is
                select * from movement_file where TASK_FLAG=7 and TASK_STATUS='InProgress';
                emp_rec emp_cur%rowtype;
                email_to varchar2(200);
                email_to1 varchar2(200);
                default_email varchar2(200);
                mesg varchar2(4000);
                no_kes varchar2(100);
                subj varchar2(4000);
                kpi number;
                crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
                BEGIN
                default_email:='@testserver.com';

                FOR emp_rec in emp_cur
                LOOP
                if emp_rec.receive_date is null then
                dbms_output.put_line('count day ' || round(sysdate - to_date(emp_rec.pks_tkh_hantar)) || crlf || crlf);
                if round(sysdate - to_date(emp_rec.pks_tkh_hantar)) >3 then
                email_to:=emp_rec.pks_penghantar_id||default_email;
                select b.KS_BIL_NO into no_kes from file b where b.KS_ID = emp_rec.pks_ks_id;
                subj:='Reminder :Test -'||no_kes;
                mesg:='Message Test -'||no_kes;
                dbms_output.put_line('Sending email to ' || email_to || ' subject: ' || subj);

                dbms_output.put_line('update old movement_file pks_id : '||emp_rec.pks_id);
                update movement_file set task_status='Done' where pks_id=emp_rec.pks_id;
                dbms_output.put_line('insert new movement_file : ');
                insert into movement_file(pks_id,pks_ks_id,pks_km_id,pks_penghantar_id,pks_tkh_hantar,
                pks_penerima_id,pks_tkh_terima,task_name,task_owner,task_status,task_flag,fb_id)
                values(pks_id_seq.nextval,emp_rec.pks_ks_id,emp_rec.pks_km_id,
                emp_rec.pks_penghantar_id,sysdate,
                null,
                null,emp_rec.task_name,
                emp_rec.pks_penghantar_id,
                'InProgress',6,emp_rec.fb_id);
                commit;
                e_mail_message(email_to,email_to,subj,mesg);

                end if;
                end if;

                if emp_rec.receive_date is not null then

                dbms_output.put_line('emp_rec.pks_km_id ' || emp_rec.pks_km_id || crlf);

                select c.KM_KPI into kpi from trail_file c where c.KM_ID = emp_rec.pks_km_id;
                no := round(sysdate - to_date(emp_rec.pks_tkh_terima))-kpi;
                dbms_output.put_line('count day - kpi' || no || crlf);
                dbms_output.put_line('kpi ' || kpi || crlf);

                if no = 1 then
                email_to:=emp_rec.pks_penghantar_id||default_email;
                select b.KS_BIL_NO into no_kes from file b where b.KS_ID = emp_rec.pks_ks_id;
                subj:='Reminder Test'||no_kes ||emp_rec.task_owner;
                mesg:='Message Test '||no_kes || 'End';
                dbms_output.put_line('Sending email to ' || email_to || ' subject: ' || subj);
                e_mail_message(email_to,email_to,subj,mesg);

                email_to:=emp_rec.task_owner||default_email;
                subj:='Reminder Test'||no_kes;
                mesg:='Message Test '||no_kes|| 'End';
                dbms_output.put_line('Sending email to ' || email_to || ' subject: ' || subj);

                e_mail_message(email_to,email_to,subj,mesg);
                end if;

                if no = 3 then
                select b.KS_BIL_NO into no_kes from file b where b.KS_ID = emp_rec.pks_ks_id;
                email_to:=emp_rec.task_owner||default_email;
                subj:='Reminder Test'||no_kes;
                mesg:='Message Test '||no_kes ||'End';
                dbms_output.put_line('Sending email to ' || email_to || ' subject: ' || subj);
                e_mail_message(email_to,email_to,subj,mesg);
                end if;

                end if;

                END LOOP;

                END;
                /
                • 5. Re: ORA-01403 - no data found
                  ZahKar
                  it seems the following query just failed.

                  select b.KS_BIL_NO into no_kes from file b where b.KS_ID = emp_rec.pks_ks_id;

                  do a dbms_output.put_line(emp_rec.pks_ks_id) to see if it holds a value tat should return records.

                  Moreover, to make sure which select statement is failing, a quick way is to put a dbms_output.put_line under every select statement and then run the program. and you will know which message was not printed and which select statement failed.
                  • 6. Re: ORA-01403 - no data found
                    791940
                    Hey can anyone help me out here, i wrote a program to connect to a database and all sql statements are correct but i keep getting a runtime error saying NO DATA FOUND.
                    do u know of any solution??
                    • 7. Re: ORA-01403 - no data found
                      Hitesh Nirkhey
                      hii dear

                      First open the cursor.then put some data in in some bulk collect and then run for loop


                      regards
                      Hitesh
                      • 8. Re: ORA-01403 - no data found
                        BluShadow
                        788937 wrote:
                        Hey can anyone help me out here, i wrote a program to connect to a database and all sql statements are correct but i keep getting a runtime error saying NO DATA FOUND.
                        do u know of any solution??
                        With such little information about what you're doing..... erm.... No.

                        If you want help you have to help us by posting information.
                        • 9. Re: ORA-01403 - no data found
                          791940
                          hey can u please tell me why i get a NO DATA FOUND runtime exception with this code?

                          try{
                          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                          Connection con = DriverManager.getConnection("jdbc:odbc:grace");
                          Statement smt = con.createStatement();
                          String nice = "INSERT INTO person(fname, lname, address, pcode, lognum, email, phone, gender, bday, status)" + "VALUES('" + jTextField1.getText() + "', '" + jTextField2.getText() + "', '" + jTextField3.getText() + "','" + jTextField4.getText() + "', '" + jTextField13.getText() + "', '" + jTextField5.getText() + "', '" + jTextField6.getText() + "', '" + jTextField17.getText() + "', '" + jTextField18.getText() + "', '" + jTextField19.getText() + "')";
                          smt.executeUpdate(nice);
                          return;

                          }

                          thanks
                          • 10. Re: ORA-01403 - no data found
                            Warren Tolentino
                            INSERT statement as far as i know does not go into NO_DATA_FOUND exception.
                            SQL> set serveroutput on;
                            SQL> begin
                              2    insert into employee
                              3    select * from employee
                              4     where fname = 'Kelly';
                              5  exception
                              6    when no_data_found then
                              7      dbms_output.put_line('error');
                              8  end;
                              9  /
                            
                            PL/SQL procedure successfully completed
                            it's somewhere on your code that has the SELECT INTO which goes into the NO_DATA_FOUND exception.
                            • 11. Re: ORA-01403 - no data found
                              Warren Tolentino
                              try this two query and see what are difference.
                              select -- c.KM_KPI into kpi 
                                     distinct c.km_id
                                from trail_file c 
                               where -- c.KM_ID = emp_rec.pks_km_id;
                                     c.km_id in (select pks_km_id from  movement_file 
                                                  where TASK_FLAG = 7 
                                                    and TASK_STATUS = 'InProgress');
                              
                              select distinct pks_km_id 
                                from movement_file 
                               where TASK_FLAG   = 7 
                                 and TASK_STATUS = 'InProgress';
                              • 12. Re: ORA-01403 - no data found
                                791940
                                Thanks warren, but thats why i'm confused, there is no SELECT statement in my code yet.
                                Just the INSERT to input data to the database :(
                                • 13. Re: ORA-01403 - no data found
                                  Warren Tolentino
                                  there might be a trigger on the table PERSON. can you post the exact error message that you are getting?
                                  • 14. Re: ORA-01403 - no data found
                                    791940
                                    Ok here is the error message,


                                    java.sql.SQLException: No data found
                                    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7138)
                                    at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3073)
                                    at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
                                    at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
                                    at java.sql.DriverManager.getConnection(DriverManager.java:582)
                                    at java.sql.DriverManager.getConnection(DriverManager.java:207)
                                    at hotel.jButton1ActionPerformed(hotel.java:437)
                                    at hotel.access$000(hotel.java:13)
                                    at hotel$1.actionPerformed(hotel.java:146)
                                    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
                                    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
                                    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
                                    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
                                    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
                                    at java.awt.Component.processMouseEvent(Component.java:6038)
                                    at javax.swing.JComponent.processMouseEvent(JComponent.java:3260)
                                    at java.awt.Component.processEvent(Component.java:5803)
                                    at java.awt.Container.processEvent(Container.java:2058)
                                    at java.awt.Component.dispatchEventImpl(Component.java:4410)
                                    at java.awt.Container.dispatchEventImpl(Container.java:2116)
                                    at java.awt.Component.dispatchEvent(Component.java:4240)
                                    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
                                    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
                                    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916)
                                    at java.awt.Container.dispatchEventImpl(Container.java:2102)
                                    at java.awt.Window.dispatchEventImpl(Window.java:2429)
                                    at java.awt.Component.dispatchEvent(Component.java:4240)
                                    at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
                                    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
                                    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
                                    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
                                    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
                                    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
                                    at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)
                                    BUILD SUCCESSFUL (total time: 19 seconds)
                                    1 2 Назад Вперед