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.

1.2 Create 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.


3 thoughts on “

  1. Oliver,This an example of an lost update:Suppose you have two users, Scott and Adams editing the same order with one order line at the same time. Scott changes the description. Adams changes the price. If Adams saves his change before Scott does, Scott’s change has disappeared.In your problem definition you mention inserting an order. If order updates are allowed, the lost update can occur. I see in part 2 order lines are only inserted, there is no update and lost updates will not occur.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

About Olivier Dupont