Super Dynamic APEX Forms Using APEX ITEM and Dynamic Actions
Working with forms in Oracle APEX is really easy and even fun thanks to the improved wizard that can create a form page on any table, view, or even on a web source in a few simple and clear steps. But sometimes you’ll need to enter multiple ‘rows’ at once. In most of these cases, you will not know how many items or rows the user will need to enter until run time, so the user may want to enter one row or 10 rows, and sometimes the user will enter zero rows of this specific type. So the question now is how can you, as a developer, build a ‘dynamic form’, a form that accepts multiple rows that can change in size according to the user’s wish?
The answer is APEX_ITEM package + APEX’s dynamic actions. Using this mix, you can build a very dynamic, powerful form on any page of your application. APEX_ITEM is a package that, according to Oracle’s formal definition, is used to “create form elements dynamically based on a SQL query instead of creating individual items page by page.” So basically, it gives the developer the ability to create items dynamically, and from these items, a form can be built. APEX_ITEM offers a list of item types: text box, text area, LOV (list of values), date popup, etc. For details, see Oracle’s reference.
In our example, I will build a page that has a dynamic form, and its size will be determined by a number that the user will enter. Using this form, I will enter multiple rows to a table and to an APEX Collection to demonstrate more than one way to handle the data entered through these dynamically generated items.
I will use a very simple table called “TEST_CHILDREN” that looks like this:
I will also create a sequence and trigger to make our lives easier.
Now let’s go to apex and create an empty page. This page will hold the dynamic form and will also have two report regions. One is built on the table “TEST_CHILDREN” and the other report is built on an APEX collection to display the current items of the APEX Collection called children. The only item is a number field; in my case, it’s called “P9_HOW_MANY.” This field is where the user will input the number of rows as desired.
Please notice that I will not discuss the steps needed for creating a new page or adding a report to the page. Instead, I will focus on creating the ‘dynamic form’ and the use of APEX_ITEM and dynamic actions. As for the APEX collection, I will soon publish an article covering some of the most advanced uses for APEX Collections.
Let’s do it!
First, I created a static content region, and then I added a number field item “P9_HOW_MANY”.
Next, I will add an interactive report region, choosing the type as SQL Query:
In order to be able to use the value in P9_HOW_MANY, I must make sure it’s stored in the session. This is why I have to choose it in the ‘Page Items to Submit’ option under the SQL Query box as shown above.
To build ouractual dynamic form, all we need to do is a query, just one query! This is the query that we will need to enter in the SQL Query box:
Now it’s time to break-down the above query:
So far so good, but if I try the page now by entering any number in P9_HOW_MANY, nothing will happen! Why? Well, remember what I said before? “APEX_ITEM package + APEX’s dynamic actions”, And until now, we only finished the first component of the recipe, and we need to finish the second part to bring our page to life.
Dynamic Actions, here we come.
We need 3 dynamic actions on our only item P9_HOW_MANY to make this work. The first dynamic action will show -unhide- the dynamic region and refresh it too, but it will do so only if the value entered is > 0,
The same dynamic action will also have a ‘Refresh’ action that will refresh the ‘Dynamic’ region in Order to see the changes according to the number inserted in P9_HOW_MANY.
But what if the user entered 5, for example, and then changed it to 0? Try it before you add the next two dynamic actions and you’ll notice that even with 0 as the value there will be 1 row displayed! So the easy fix is to have 2 more actions; one that will hide the dynamic region if the value entered is 0 and another one to do the same is it’s NULL.
Now when I enter 0 or leave the number field empty, the form will disappear completely and instantly.
** If your dynamic items are being displayed as code, you will need to uncheck the option called ‘Escape Special characters’ under each column properties.
How can we use the data from the dynamic form?
Now we have a dynamic form that looks good, but to be of any use, we must know how to collect the data entered in all of the items and store them somewhere, right? To do so, I will add 2 buttons to the dynamic form region. One will be called “INSERT_INTO_TABLE” and the other will be “Insert Into Collection.” Both of them are the ‘Submit Page’ button.
I can use dynamic actions or process to write and execute the code needed for saving the data into the table or the collection when any of the buttons is pressed. As a change, I will use a process, but remember a dynamic action will do it just fine and it even might be easier.
I created a new process and named it “Insert INTO TABLE.” As for the type, it’s PL/SQL code of course. Under the ‘Execution Options’ section, the point is ‘After Submit’ — this when this code should run — and under ‘Server-side Condition’ section, I must set the ‘When Button Pressed’ to “INSERT_INTO_TABLE” since this is the button that should save the data to the table “TEST_CHILDREN”.
Now I need to write the code that will save the data from our dynamic form to the table
Let’s break it down:
“APEX collections” is one of the most important features that come with Oracle APEX, and it can be used in many ways for different purposes, but in general, you can think of Apex collection as a temporary space to store complex data types — nonscalar — for instance, full row of data from a table or a view. This data can be manipulated in any way and then stored in table(s). Each Apex Collection can can has 50 varchar2 attributes (c001 -> c0050), 5 number attributes (n001->n005), 5 date attributes (d001->d005), 1 CLOB attribute (clob001), and 1 BLOB attribute (blob001). Each collection must have a unique name (As I mentioned before, I am working on a detailed article about advanced APEX collection, which will be available soon).
In some cases, you might need to store the data entered through the dynamic items you created in the session so you can use it or modify it in any needed way. So here, I will store the data from my dynamic form into a collection named ‘CHILDREN’.
Just like what we did before with the process for saving the data to table, I will create a new process and name it “Insert Into Collection.” As for the type, it’s PL/SQL code of course. Under the ‘Execution Options’ section, the point is ‘After Submit’ — this when this code should run — and under the ‘Server-side Condition’ section, I must set the ‘When Button Pressed’ to “Insert Into Collection” since this is the button that should save the data to the collection “CHILDREN”.
This is the code that should be entered in order to save the data to the collection:
Let’s go through it quickly …
Since we chose to create ‘Submit Page’ buttons, the page will refresh after the user presses the button. After the processing of the data is finished, the dynamic form will be emptied automatically.
APEX_ITEM is a very powerful and easy-to-use package that comes with APEX. When coupled with the dynamic actions, you’ll have a really nice toolset for creating fluid and dynamic pages inside your APEX application. Adding APEX collections to the mix will take you to another level, and this is what I’ll demonstrate in my next article where we will continue from this setup and see APEX collections in action.