When I recently created an APEX application, I was confronted with a requirement that I couldn’t directly solve. But then colleague Maarten van Luijtelaar showed me the right direction: APEX Collections!
The requirement was in short this; a copier holds one or more meters to count how many copies have been printed in color, black&white, etc. a customer should be able to enter readings in an APEX application for these meters for their copiers. The number of copiers and the number of meters is dynamic. The entered meterreading should be validated against a number of given values per meter.
To solve the dynamic part of the requirement, I created a page based on a cursor for loop that selected all meters for any number of selected machines. The page is then built up by using the built-in Oracle htp package.
This is a small snippet that shows how a field is built up.
htp.tableData(htf.formText('f05', 10, 10, l_col_reading, 'style="text-align: right;color: #FF0000;font-weight: bold;border-color: #FF0000;"'));
The result of this is a table with data for each meter on every line.
After entering the data and pressing submit, the data is first validated by a procedure, by using the APEX_APPLICATION package.
FOR i in 1..APEX_APPLICATION.G_f01.COUNT LOOP if to_number(apex_application.g_f05(i)) >= to_number(apex_application.g_f11(i),'999G999G999G999') then l_error := '1'; end if; END LOOP;
This all worked fine, but the only problem was, that after the validation failed, the page was redrawn and the enterable fields were empty. A very big problem for customers, because if only one value failed after they have entered several dozen, they had to start over again.
Inside the validation procedure I fill a collection with the essential values (so I can identify the unique row) and the entered meterreading value of course.
To enhance the user experience I also kept a value to show which error exaclty caused the validation to fail.
apex_collection.add_member( 'COL_READINGS' , apex_application.g_f01(i) , apex_application.g_f02(i) , apex_application.g_f05(i) , l_error);
In the PL/SQL Region that shows the page I changed it to outer join the values from the APEX Collection with the cursor I mentioned earlier. So when the collection is empty, the field is empty, but when it was filled before validation, the that value is shown.
select c001 , c002 , c003 , c004 from apex_collections where collection_name = 'COL_READINGS'
And by using the error I could also pinpoint the cause of the problem and show it to the customer (in this case, the entered value is lower than the previous value in the database).Smarter APEX: using collections,