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.

The Setup

I will use a very simple table called "TEST_CHILDREN" that looks like this:

  CREATE TABLE  "TEST_CHILDREN" 
   ("CHILD_ID" NUMBER, 
"CHILD_NAME" VARCHAR2(120), 
"GENDER" VARCHAR2(12), 
"SCHOOL" VARCHAR2(120), 
"AGE" NUMBER
   )
/

I will also create a sequence and trigger to make our lives easier.

   CREATE SEQUENCE  "TEST_CHILDREN_SEQ"  
   MINVALUE 1 
        MAXVALUE 99999999 
        INCREMENT BY 1
        START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  GLOBAL;

  /

  CREATE OR REPLACE TRIGGER  "TEST_CHILDREN_BI_TRIGGER" 
BEFORE insert on "TEST_CHILDREN"
for each row
 WHEN (NEW.CHILD_ID IS NULL) 
   begin
:NEW.CHILD_ID := TEST_CHILDREN_SEQ.NEXTVAL;
  end;

/
 ALTER TRIGGER  "TEST_CHILDREN_BI_TRIGGER" ENABLE
/

The Page

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:

SELECT LEVEL AS item_no,
APEX_ITEM.TEXT(p_idx => 1, p_attributes => NULL) AS Name,
APEX_ITEM.text( p_idx=> 2 , p_value=> null , p_attributes=> 'class="number"') as Age,
APEX_ITEM.SELECT_LIST(3,'MALE','MALE;MALE,FEMALE;FEMALE') Gender,
APEX_ITEM.TEXT(p_idx => 4, p_attributes => NULL) AS School
FROM dual
CONNECT BY LEVEL <= TO_NUMBER (:P9_HOW_MANY)
;

Now it's time to break-down the above query:

• APEX_ITEM.TEXT will create a text box item, and you can add attributes by passing them to p_attributes parameter, but the most important part here is the p_idx parameter, as this is the most powerful part of APEX_ITEM. This parameter decides that the APEX_APPLICATION global array will be used. The valid range of values is 1 to 50. So passing 1 creates F01 and 2 creates F02. This means that all of the data that will be entered in each and every "Name" text box will be stored in the APEX_APPLICATION global array F01. This will be very important later.
• The same applies to the second APEX_ITEM.TEXT called Age. The only difference is that we used the p_attributes to make sure that this is a number field. Also notice that this time, we are using the APEX_APPLICATION global array FO2.
• The third item is a LOV or list of values: "'MALE', 'MALE;MALE, FEMALE;FEMALE'". In our example, we have a short list that contains 2 choices only "MALE" or "FEMALE." We pass a default value first; here, it's "MALE." Then, we enter each choice as a pair of display values and the real value that will be stored in the database. Of course, we use another APEX_APPLICATION global array FO3.
• Finally, the last item is 'SCHOOL', and there is nothing new here.

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.

1. Saving to table:

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

if :P9_HOW_MANY is NOT NULL and :P9_HOW_MANY > 0 then
FOR i IN 1 .. apex_application.g_f01.COUNT LOOP
insert into TEST_CHILDREN (CHILD_ID, CHILD_NAME, GENDER, SCHOOL, AGE)
   values ( "TEST_CHILDREN_SEQ".nextval, apex_application.g_f01(i), apex_application.g_f03(i), apex_application.g_f04(i), apex_application.g_f02(i));
END LOOP;
end if;

Let's break it down:

• The IF statements make sure this will run only if there is something to insert
• The loop is utilizing the fact that APEX_APPLICATION.g_f01 is a collection and, therefore, has a member method 'COUNT ' to use, so we loop from 1 to the number of items stored in the collection. Inside the loop, we insert the data row-by-row.

2. Saving to APEX_COLLECTION:

"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:

IF :P9_HOW_MANY is NOT NULL and :P9_HOW_MANY > 0 then
IF NOT APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => 'CHILDREN') THEN
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
          p_collection_name => 'CHILDREN'); 
END IF;

APEX_COLLECTION.ADD_MEMBERS(
p_collection_name => 'CHILDREN',
p_c001 => apex_application.g_f01,
p_c002 => apex_application.g_f03,
p_c003 => apex_application.g_f04,
p_c004 => apex_application.g_f02 
);
End IF;

Let's go through it quickly ...

• First, we check if the collection "CHILDREN" already exists. If not, we create it.
• Then I used one of the methods to add data to a collection using a procedure called "ADD_MEMBERS", and this procedure will add an array or 'list' of values into the collection. As you can see, I pass my APEX Global variables ' apex_application.g_f01',' apex_application.g_f03',' apex_application.g_f04', and ' apex_application.g_f02' in the order I want to be stored inside the collection.

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.

Conclusion

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.