2 Replies Latest reply on Mar 5, 2014 2:51 PM by user7351276

    Loop only selected checkbox to insert data into block - Oracle forms




      I have a multiple row block. Each row has a check box - nondatabase item.

      when checkbox is checked the value is set to Y and when not it is N. when I click insert button in my form, it loops through all the records and inserts data where checkbox is Y. this works fine but when the block has huge no of rows say 1000 and I have checked only one or few rows, then performance is bad as it loops through each row lookin for Y and then insert and this takes longer time.


      Is there any better way to optimize the insert? is there any way to loop through only selected checkbox something like using array to make the insert faster. if yes a code sample is appreciated.


      thanks in advance.

        • 1. Re: Loop only selected checkbox to insert data into block - Oracle forms

          There are a couple of ways you could speed up this process.


          Option 1, you could use a hidden data block and when your user selects the CheckBox it copies the record to your hidden block.  Then, during insert, you loop through the hidden block instead of your main data block.  I don't really like this option because you have to take into account if the user Checks and then Un-Checks the CheckBox and add/remove record(s) the hidden block accordingly.


          Option 2, use some type of array structure to keep track of only those records that have been selected.  I actually prefer this method as it doesn't require any extra coding in the When-Checkbox-Checked trigger to copy/synchronize datablocks.  Please take a look at article: How to Select Multiple Records in a block using Record Groups.  This article demonstrates how to use an Forms Record Group to keep track of records selected using a Checkbox.  During your INSERT processing, you can easily modify the When-Button-Pressed code in Item 24 to navigate to each selected record and insert the record into your table.  While this method still navigates to each row to perform the insert, you already know which record you want through the return value of the "REC_GROUP.Get_Number()" function so you can navigate directly to the record using the GO_RECORD() built-in.


          Hope this helps.


          • 2. Re: Loop only selected checkbox to insert data into block - Oracle forms

            Hi Craig,

            thanks for your suggestions.

            this works. thank you. but I also have a ALL and None checkbox, which will select and deselect all the checkboxes at one shot respectively.

            For this I can right a program unit and place the logic of when checkbox changed in the program unit and call, but consider a scenario where I click ALL checkbox (which selects say 100 checkboxes)  and then unselect some few checkboxes from my block then the record group logic will not work I suppose.


            Any suggestions here?