10 Replies Latest reply: Feb 12, 2014 6:39 AM by Bas de Klerk RSS

    Wiered Problem in For Loop in a PLSQL Process

    KRISH1979

      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

      loop

         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
            KRISH1979

            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

            loop

               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

              Hi,

               

              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.

               

              Cheers

                Bas

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

                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

                Cheers

                Krish

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

                  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.

                  Cheers,

                  Krish.

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

                    Hi,

                     

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

                     

                    Cheers

                      Bas

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

                      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.

                      Cheers

                      Krish,

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

                        Hi,

                         

                        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.

                         

                        Cheers

                          Bas

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

                          Bas,

                          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!

                          Cheers

                          Krish

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

                            Hi,

                             

                            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 ) :

                            http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/bldr_pg_procs.htm

                             

                             

                            Bye!