7 Replies Latest reply: Mar 4, 2014 8:39 AM by 3a473b44-9cd5-4464-b36e-1edf9b5c2943 RSS

    ORA-01403: no data found

    3a473b44-9cd5-4464-b36e-1edf9b5c2943

      Hello,

       

      I'm discovering APEX and I have a problem.

       

      I've declared a page :

       

      select art_id, PIL_NB_POA, apex_item.hidden(99,poc_id) || APEX_ITEM.CHECKBOX(2,PIL_CO_CRITIQUE_CLIENT,DECODE(PIL_CO_CRITIQUE_CLIENT,1,'CHECKED',NULL)) PIL_CO_CRITIQUE_CLIENT, APEX_ITEM.TEXTAREA(3,PIL_LB_COMMENTAIRES,2,70) PIL_LB_COMMENTAIRES from from TEPOA_PILOTAGE@dkey

       

       

      I see my data, but when I want to update the table with the checkbox and the text area, i have an error ORA-01403

       

      I have a process :

       

      BEGIN

      FOR i in 1..APEX_APPLICATION.G_F41.COUNT

      LOOP

         UPDATE TEPOA_PILOTAGE@dkey set PIL_CO_CRITIQUE_CLIENT = APEX_APPLICATION.G_F02(i) where POC_ID = APEX_APPLICATION.G_F99(i);

         UPDATE TEPOA_PILOTAGE@dkey set PIL_LB_COMMENTAIRES = APEX_APPLICATION.G_F03 where POC_ID = APEX_APPLICATION.G_F99(i);

      END LOOP;

      COMMIT;

      END;

       

      If i only keep the text area it works, maybe I don't use the checkbox properly.

        • 1. Re: ORA-01403: no data found
          Denes Kubicek

          Are you sure your checkbox has the id 44? Use firebug to make sure this is the case. See this posting:

           

          Denes Kubicek ApEx BLOG: How to Debug Ajax and On Demand Processes

           

          Denes Kubicek

           

          -------------------------------------------------------------------

           

           

          -------------------------------------------------------------------

          • 2. Re: ORA-01403: no data found
            Nicolette

            3a473b44-9cd5-4464-b36e-1edf9b5c2943

             

            Are you sure this code works when you're only updating the text area.

            Because there doesn't exists an apex_application.g_f99.

            The apex_application.g_fNN range from apex_application.g_f01 till apex_application.g_f50.

             

            Which item reference to apex_application.g_f41. The item is mentioned in the update code but not in your sql code.

             

            For checkboxes only the checked values are saved in the array.

            For example if you have 3 rows where the checkboxes have the following values

            row 1 10

            row 2 20

            row 3 30

            Now if you check row 1 and 3

            The first element of the corresponding array has the value 10 and the second the value 30.

             

            All the other items have all the rows in the g_FNN array. So assuming that apex_application.g_f41 isn't a checkbox.

            And the checkbox is apex_application.g_f02 with the values and rows checked as in the previous example.

            The first row will get updated with PIL_CO_CRITIQUE_CLIENT = 10.

            The second row will get updated with PIL_CO_CRITIQUE_CLIENT = 30.

            When updating the third row you get a NO DATA FOUND error because apex_application.g_f02(3) doesn't exists.

             

            Could to tell what you're trying to do?

            For example what should happen when the checkbox is checked.

            And what should happen when it isn't checked.

            Please explain with before values, the rows that are checked and the after update values.

             

            Nicolette

            • 3. Re: ORA-01403: no data found
              3a473b44-9cd5-4464-b36e-1edf9b5c2943

              I've made à mistake,

               

              In the page , i've got :

               

              select art_id, PIL_NB_POA, apex_item.hidden(1,poc_id) || APEX_ITEM.CHECKBOX(2,PIL_CO_CRITIQUE_CLIENT,DECODE(PIL_CO_CRITIQUE_CLIENT,1,'CHECKED',NULL)) PIL_CO_CRITIQUE_CLIENT, APEX_ITEM.TEXTAREA(3,PIL_LB_COMMENTAIRES,2,70) PIL_LB_COMMENTAIRES from from TEPOA_PILOTAGE@dkey

               

              the code is :

               

              BEGIN

              FOR i in 1..APEX_APPLICATION.G_F01.COUNT

              LOOP

                 UPDATE TEPOA_PILOTAGE@dkey set PIL_CO_CRITIQUE_CLIENT = APEX_APPLICATION.G_F02(i) where POC_ID = APEX_APPLICATION.G_F01(i);

                 UPDATE TEPOA_PILOTAGE@dkey set PIL_LB_COMMENTAIRES = APEX_APPLICATION.G_F03 where POC_ID = APEX_APPLICATION.G_F01(i);

              END LOOP;

              COMMIT;

              END;

              • 4. Re: ORA-01403: no data found
                Nicolette

                3a473b44-9cd5-4464-b36e-1edf9b5c2943

                3a473b44-9cd5-4464-b36e-1edf9b5c2943 wrote:

                 

                I've made à mistake,

                 

                In the page , i've got :

                 

                select art_id, PIL_NB_POA, apex_item.hidden(1,poc_id) || APEX_ITEM.CHECKBOX(2,PIL_CO_CRITIQUE_CLIENT,DECODE(PIL_CO_CRITIQUE_CLIENT,1,'CHECKED',NULL)) PIL_CO_CRITIQUE_CLIENT, APEX_ITEM.TEXTAREA(3,PIL_LB_COMMENTAIRES,2,70) PIL_LB_COMMENTAIRES from from TEPOA_PILOTAGE@dkey

                 

                the code is :

                 

                BEGIN

                FOR i in 1..APEX_APPLICATION.G_F01.COUNT

                LOOP

                   UPDATE TEPOA_PILOTAGE@dkey set PIL_CO_CRITIQUE_CLIENT = APEX_APPLICATION.G_F02(i) where POC_ID = APEX_APPLICATION.G_F01(i);

                   UPDATE TEPOA_PILOTAGE@dkey set PIL_LB_COMMENTAIRES = APEX_APPLICATION.G_F03 where POC_ID = APEX_APPLICATION.G_F01(i);

                END LOOP;

                COMMIT;

                END;

                Wel that answers my first two remarks.

                 

                But do you understand the following?

                For checkboxes only the checked values are saved in the array.

                For example if you have 3 rows where the checkboxes have the following values

                row 1 10

                row 2 20

                row 3 30

                Now if you check row 1 and 3

                The first element of the corresponding array has the value 10 and the second the value 30.

                 

                All the other items have all the rows in the g_FNN array. So assuming that apex_application.g_f41 isn't a checkbox.

                And the checkbox is apex_application.g_f02 with the values and rows checked as in the previous example.

                The first row will get updated with PIL_CO_CRITIQUE_CLIENT = 10.

                The second row will get updated with PIL_CO_CRITIQUE_CLIENT = 30.

                When updating the third row you get a NO DATA FOUND error because apex_application.g_f02(3) doesn't exists

                 

                Please tell us what you're trying to do.

                What should happen when the checkbox is checked?

                And what should happen when it isn't checked?

                Please explain with the values before the update, the rows that are checked and the values after the update.

                 

                Nicolette

                • 5. Re: ORA-01403: no data found
                  3a473b44-9cd5-4464-b36e-1edf9b5c2943

                  In my tab, i've got multiples lines.

                  art_id        /     pil_nb_poa       /      pil_co_critique      /      pil_lb_comments

                   

                  art_id and pil_nb_poa are numbers , pil_co_critique is linked to my checkbox (if pil_co_critique = 1, then the checkbox is checked, else the checkbox is unchecked) pil_lb_comments is a string

                   

                  Then in my tab, when I submit my update, i want to update in my database the fiels PIL_CO_CRITIQUE of the table TEPOA_PILOTAGE (in this table, I have the field ART_ID, PIL_NB_POA, PIL_CO_CRITIQUE, PIL_LB_COMMENTS, POC_ID).

                  If  the checkbox is checked, then PIL_CO_CRITIQUE = 1 else PIL_CO_CRITIQUE = 0

                   

                   

                  For example, I have 3 lines in my tabs

                  art_id     /    pil_nb_poa     / pil_co_critique                          / pil_lb_comments              /  POC_id (hidden field apex_item.hidden(1,poc_id))

                  1          /          5              /   0 (checkbox unchecked)         / 'test'                                /   1

                  2          /          10            /   1 (checkbox checked)             / 'test 2'                             /   2

                  3          /          10            /   0 (checkbox unchecked)         / 'test 3'                             /   3

                   

                  If I uncheck line 2 and i check line 3,

                  art_id     /    pil_nb_poa     / pil_co_critique                          / pil_lb_comments              /  POC_id (hidden field apex_item.hidden(1,poc_id))

                  1          /          5              /   0 (checkbox unchecked)         / 'test'                                /   1

                  2          /          10            /   0 (checkbox unchecked)         / 'test 2'                             /   2

                  3          /          10            /   1 (checkbox checked)            / 'test 3'                             /   3

                   

                  I want to update my database with this new value

                  • 6. Re: ORA-01403: no data found
                    Nicolette

                    3a473b44-9cd5-4464-b36e-1edf9b5c2943

                     

                    Change the select statement to:

                    select art_id
                    ,      PIL_NB_POA
                    ,      apex_item.hidden(1,poc_id)
                           || APEX_ITEM.CHECKBOX(2,poc_id,DECODE(PIL_CO_CRITIQUE_CLIENT,1,'CHECKED',NULL)) PIL_CO_CRITIQUE_CLIENT
                    ,      APEX_ITEM.TEXTAREA(3,PIL_LB_COMMENTAIRES,2,70) PIL_LB_COMMENTAIRES
                    from TEPOA_PILOTAGE
                    

                    Notice the change in APEX_ITEM.CHECKBOX(2,poc_id,DECODE(PIL_CO_CRITIQUE_CLIENT,1,'CHECKED',NULL)).

                    This means that the id gets stored in apex_application.g_f02 if the row is checked.

                     

                    And for the process

                    declare
                      v_found  boolean;
                    begin
                      for n in 1..apex_application.g_f01.count
                      loop
                        v_found := false;
                        /*Now check if the id of the current row is checked*/
                        for k in 1..apex_application.g_f02.count
                        loop
                          if apex_application.g_f01(n) = apex_application.g_f02(k)
                          then
                            /*The current row is checked*/
                            v_found := true;
                            update TEPOA_PILOTAGE
                            set PIL_CO_CRITIQUE_CLIENT = 1
                            ,   PIL_LB_COMMENTAIRES    = apex_application.g_f03(n)
                            where POC_ID = apex_application.g_f01(n)
                            ;
                            /*Exit the apex_application.g_f02 loop because a row can only be checked once*/
                            exit;
                          else
                            /*This id of the checked row is not the current one just keep on checking*/
                            null;
                          end if;   
                        end loop;
                        if not v_found
                        then
                          /*The current row wasn't found in apex_application.g_f02 meaning the row wasn't checked*/
                          update TEPOA_PILOTAGE
                          set PIL_CO_CRITIQUE_CLIENT = 0
                          ,   PIL_LB_COMMENTAIRES    = apex_application.g_f03(n)
                          where POC_ID = apex_application.g_f01(n)
                          ;
                        end if;
                      end loop;
                    end;
                    

                    As you see in the code, with the checkbox you need to use a second loop inside the main loop to see if the row processed by the main loop is checked or not.

                    To make that comparison possible the value of the checkbox needs to be unique in every row and available in another item.

                    The id or primary key is ideal to use as value for the checkbox.

                     

                    Notice that in this example there is no check done for "lost update", actual change made by the user or the record being locked by another user.

                     

                    Nicolette

                    • 7. Re: ORA-01403: no data found
                      3a473b44-9cd5-4464-b36e-1edf9b5c2943

                      Thank you very much, it works.