Apex Collections, an example (part 1)
Some weeks ago I needed to store records temporarily and let a user decide later on if he/she would save the data permanently. This was a perfect time for me to start using ApEx Collections. Collections let you temporarily store data as records so you can manipulate them and save them later on. I found a whole bunch of information on the oracle forum but I couldn’t find the right information I needed, so therefore this post
My specific problem can be described as: we would like to let users add order lines to an order, update and delete them without saving anything in our database until the user confirms that the order is correct and wants to create it.
1.1 Delete Collection
Our first step when working with collections is to make sure that no collection with the same name exists. So create a process ‘on Load – Before Header’ that deletes a collection with the collection name you are using, in this case that would be ‘ORDERS’. We can use ‘wwv_flow_collection.delete_collection’ to delete a collection.
Now we are ready to create our collection, use ‘wwv_flow_collection.create_collection’ to create your collection. You might notice that there is already another thing I do, I already put one record in my collection. You are not obliged to do so but I do it here because now the user can already start filling the collection without pressing on a add row button.
I use ‘wwv_flow_collection.add_member’ to put a record in my collection. You must specify your collection name and then you must use p_c001 – p_c050 to specify your columns. In my first column I put a unique key that I will use when deleting a member from my collection. The third column specifies the amount and will be default 1.
1.3 Display Collection
Now we are ready to display our collection and update/add/delete members from it. Create a new page with a report where you will display the collection. Make sure you can navigate to it from the page where you create the collection (you can do it on the same page if needed). Add 2 buttons to your report, one to add a member and one to delete a member of your collection.
Some things explained:
- Our first column is a checkbox that will allow users to delete selected lines from the collection. The function ‘pck$genlib.f_get_next_row_id’ will return your row_id (0001,0002, …) so we can refer to the specific row in a correct way.
- The second column is the seq_id, automatically assigned to your collection members, we need this value in order to update the right member from the collection.
- The other columns must be displayed as text fields so we can put data into them.
Part 2 will include adding, deleting and updating members from your collections and will be posted very soon.