10 Replies Latest reply on Feb 12, 2014 12:39 PM by Bas de Klerk

    Wiered Problem in For Loop in a PLSQL Process


      Apex version : 4.2.3

      Database: 11gR2

      Browser : chrome


      Hello Friends,

      One last thing which remains before I can conclude my first project in APEX. Need your help to crack this funny but annoying problem.


      I have a master detail form in my application. In Detail section, I am performing some calculations by calling a process as follows:

      Lets say there are 5 columns in my detail section. I am calculating value of 5th column based on Value of 1st and 3rd column. All this happens in loop. Code is as follows:


      for i in 1 ..apex_application.g_f12.count


         apex_application.g_f14(i) := apex_application.g_f10(i)*(apex_application.g_f12((i))/100)*apex_application.g_f13(i);

      end loop;


      If N = number of Rows in my table then this for loop seems to be working for N-1 rows. Which means :

      If I am starting fresh and I entered only 1 detail record, it would mean nothing will happen (N=1 and 1-1 = 0).

      If I now Add one more detail record then the code gets executed for 1st record that I had entered but nothing happens to the new row because now N=2 and N-1 = 1.

      It doesnt end here. If anytime I enter more than 1 record (2,3,4 .....) Everything works perfect for all the records!

      The problem really seem to be when I enter only 1 record in detail section either as first record or as subsequent update records. One final observcation is, if I appen an empty row after making my entries, things will work perfect.

      Has anyone faced this? Seems to be some silly mistake but unable to crack it.

        • 1. Re: Wiered Problem in For Loop in a PLSQL Process
          Joe Upshaw

          The apex array start at position 0 rather than 1. I think that is your entire issue.


          This is always safer (Will not break if interleaving positions in the array are empty):


          for i in apex_application.g_f12.first..apex_application.g_f12.last

          • 2. Re: Wiered Problem in For Loop in a PLSQL Process

            Thanks Joe for sharing this. I tried the recommendation however the behaviour is still same. Below is updated code where I am trying to set the Primary Key of Master form in Detail form rows.


            for i in apex_application.g_f03.first..apex_application.g_f03.last


               apex_application.g_f03(i) := :P35_TRN_ID;

            end loop;


            As explained earlier, this code doesnt work when there is single row for update. IF there are more than 1 row everything works perfect.

            I believe you have found the problem correctly but not sure how to resolve this! Thanks once again for helping out on this.

            • 3. Re: Wiered Problem in For Loop in a PLSQL Process
              Bas de Klerk



              your approach seems right.

              Things you might want to check/debug ( for example insert the values into a testtable ) is the value of apex_application.g_f12.count to see if this is actually 1 less then expected (BWT I dont think you should start at 0 but at 1 when counting as for i in 1 ..apex_application.g_f12.count).

              You might want to check if apex_application.g_f12.count is a checkbox ( if it is the value of apex_application.g_f12.count is only the number of checked checkboxes ), so make sure the apex_application.g_fXX.count is for a NON-checkbox field.

              If all else fails you might want to setup a test on apex.oracle.com so we can check to see why it does not work as expected.




              1 person found this helpful
              • 4. Re: Wiered Problem in For Loop in a PLSQL Process

                Hey Bas,

                The field I used which is apex_application.g_f03.last is a text box. However, its good to know that check box can cause problem. I will keep that in mind.

                I had also bebug to find out the counts are right. And in deed they are all correct. What I observed is, If I add 1 row to details table, the "complete" code gets executed once. If I add 2 rows to detail table, "complete" code gets executed twice. I am guessing that when this code is getting executed for the second time, only then it is updating the values even for first row.

                I guess if I set up things on apex.oracle.com, it would be easier for you to help me out, I will do it and get back soon. Till then if whatever I wrote rings bells let me know



                • 5. Re: Wiered Problem in For Loop in a PLSQL Process

                  Hello Bas,


                  I have setup the application at http://apex.oracle.com/pls/apex/f?p=17163::2906738421910


                  login id :demo

                  password: demo


                  Once you Login to application

                  1. Go To "Transactions" Tab - its last TAB
                  2. Click on Create button.
                  3. Select any Transaction date
                  4. Click on Add Row Button in details section
                  5. Enter Purity as 95
                  6. QTY as 10
                  7. GROSS WEIGHT as 100
                  8. NET WEIGHT as 100
                  9. PERCENTAGE as 4
                  10. GOLD RATE as 3000

                  dont worry about fields not mentioned here, they are either not mandatory or are populated automatically.

                  11. click on Create.


                  You will notice that Detail Record doesnt get populated. In Database, the record is getting saved but the Foriegn Key is not getting set.


                  Now just to make sure that things work with multiple record, you may repeat ADD ROW process once more with same values or similar values.


                  I have used the Process named: SetTrnId to set the value of Master's Primary Key in Details Section. This gets fired after ATS_Transaction_Master process which returns Trn_id (Primary Key) in the text box.

                  Let me know if you need any more information.



                  • 6. Re: Wiered Problem in For Loop in a PLSQL Process
                    Bas de Klerk



                    to check I need developer access to the workspace. So I need the workspace name and an account which can work as a dev




                    • 7. Re: Wiered Problem in For Loop in a PLSQL Process

                      Hi Bas,

                      Workspace name is : BMCDEMO

                      Application name is : GOLDMASTER

                      the user I shared has the developer access.

                      Thanks for looking in to this. Really appreciate it.



                      • 8. Re: Wiered Problem in For Loop in a PLSQL Process
                        Bas de Klerk



                        fixed the foreign key setting process. I think this proces was of a wrong type, I could not find the problem right away so I recreated a PL/SQL process (set_foreign_key) which does the same logic and iot worked right away. So you might compare with the process you created to see what was wrong.

                        I also changed the type of the field in the report, I'm not sure if the way you created the foreign key in the report creates an g_f0X array, but you can easily check if you revert to the orig type.




                        • 9. Re: Wiered Problem in For Loop in a PLSQL Process


                          First of all THANKS A TON!!! I cant express how happy I am to see my complete application working.

                          Now here is the story. When I compared the two process, the difference was, I had selected Tabular Form value also! However, in your case, you didnt select it. I replicated the process the way you created and it works PERFECT! May I know why it wont work when we select tabular form? I thought that was needed for the process to work on this form.

                          I am marking your replies as Helpful as well as Correct Answer. Thanks once again!



                          • 10. Re: Wiered Problem in For Loop in a PLSQL Process
                            Bas de Klerk



                            not sure what the exact difference is but if you need a multirow update/delete etc pocess you need to choose the page tabular process type.

                            Also I think for the PL/SQL process type you are able to choose to execute on a per row or per transaction basis (when of type tabular), the standard PL/SQL proc does not have this option.

                            Probably the difference is not that big, I think you can develop the same results in 2 differend ways.

                            Maybe this explains it better (compare the standard and tabular ) :