What is the most efficient way to loop thru a block?

[celispb]

Hi Board,

Quick question on looping.

I have a tabular form where the user can select multiple records by clicking a check box.

The user selects records that he wants to process. Once done selecting he presses a button to start a process. The process only affects the records that are checked.

The number of records retrieved in the initial query could be anywhere between 50 and 1000+ records.

Should I store the record numbers in a record group or pl/sql table as the user selects them OR should I just loop through the block?

I would think using the pl/sql table would be easier to program but are there advantages to using a record group?

If I do store the record numbers, is there a point where it is better to loop through the block. For example, if there are 1000 records and user selects all but one, is it better to store all 999 records in a record group or to have logic to determine that more than x% of the records are to be processed so loop through the block?

Thanks for any help.

 

[lewisp]

I prefer the PL/SQL table approach, as there wont be any unwanted side effects of triggers firing as you loop. Also, if the user only selected the top few records of a 1000 record block, a block loop would cause all records to be retrieved unnecessarily which could be quite slow.

The nice thing about PL/SQL tables is that they are a sparse list. That is, the array does not need to have consecutive record numbers in it. Therefore you can set the values of (say) record 1, 3 and 5 and it would only store 3 records at positions 1, 3 and 5. You can loop through the PL/SQL table using table_name.NEXT, doing whatever processing you want for the record you are working on.

Nowadays I use pl/sql tables to do pick lists, using the sparsity of the tables to create or remove the rows the user has selected. I can create the pl/sql tables in a database procedure and pass the whole pl/sql table to my (client) forms for processing, and vice versa. This method alone has speeded up our forms processing by a factor of 15.

 

[sem]

To populate pl/sql table we also need to fetch all the records. I agree, that eliminating side effects is a good reason, but maintaining CONSISTENCY may be a challenge: if the person DELETES or CLEARS or INSERTS a record, the changes should be made in pl/sql table accordingly. So, even with side effects I prefere looping through records, at least to show ALL selected records.

Regards, Dima

 

[lewisp]

I dont think you need to fetch all the records for a PL/SQL table. The point is its sparse, so you only need to create the records you actually want to process in the PL/SQL table as you go.

You might poulate a row in the table like this:

[tt]table(To_NumberSYSTEM.CURSOR_RECORD)).column := :block.field_value;[/tt]

...and it doesnt matter if you're on the 999th record, it will still only populate the value in a single row at position 999. You can then process only the rows that have been populated in the table, then delete it when you're done.

 

[sem]

My remark about fetching was about performing "SELECT ALL" or "SELECT BY FILTER" operations without (or with minor) navigation through block to avoid possible screen flashing or side effects of triggers: one may mark only records in pl/sql table or record group and mark them in a block only when record becomes visible. But at the end of story all the records should be fetched, at list to show them. As for cursor record - the number may change if clearing the record above, thus using recordgroup may be more preferable to avoid manual renumbering of tail records (shifting).

Regards, Dima

 

[lewisp]

Yes I agree with what you say if the form allows the user to insert and delete records from the block.

The technique works well for query and update only blocks though

 

[celispb]

Thanks for all the responses.

Using a pl/sql table was my initial thought. I just did not know if there was any advantages to using a pl/sql table over a record group.