APEX_GLOBAL_ARRAYS
When you are working with global package arrays (apex_application.g_f01 ... apex_application.g_f50) in Oracle Application Express (APEX), the following package procedure apex_global_arrays can help in reducing development time especially time spent in the bugs and features department.
PACKAGE apex_global_arrays
IS
PROCEDURE print(maxlength NUMBER DEFAULT 20);
END;
The procedure print prints a HTML table with the contents of the arrays only if the page is run in debug mode. The parameter maxlength truncates the value of each table cell at the given number. You can call the procedure in a separate On Submit application process. In this way the table is rendered even when succeeding On Submit processes fail. Define once, execute everywhere. And of course, you can call it in page process as well.
A picture says more than a thousand words. Below is an example of an updateable report based on the emp table.
After pressing submit, the following output is shown. Compare the g_f01 array with the checkboxes above. The g_f01 array is not sparse like the g_f08 (commission) array. Also, when an array value does not exists, no table cell is printed, i.e. g_f05(9); King does not have a manager.
The source can be found here.
Notes
Due to the name change of HTMLDB into APEX the public synonyms
apex_applicationandhtmldb_applicationpoint both to thewwv_flowpackage where the associative arrays are declared. The global package arrays are also known as referencing arrays in APEX documentation or associative arrays in the PL/SQL language.On apex.oracle.com you have to enter the debug mode after the page is submitted in order to render the table. On Oracle XE, you have to enter debug mode before submitting the page.
- You can install APEX_GLOBAL_ARRAYS in the parsing schema or in a separate schema. You can find the parsing schema of your application on application definition page. When you choose for a separate schema you have to grant execute right to the parsing schema and create a (public) synonym for APEX_GLOBAL_ARRAYS.
11 comments:
Jornica,
very interesting and useful tip!
Putting that into an application level "On Submit" process with a condition "item IS NOT NULL" for DEBUG would easily enabled it globally for an application.
Great work!
Patrick
Patrick,
Thanks for the idea of calling it from an application process. However the condition is not necessary. The procedure only prints when run in debug mode, otherwise only one IF statement is executed.
With kind regards,
Jornica
The advantage of using the declarative condition is that it can be handled "hard wired" by the APEX engine.
If you don't use this condition, the APEX engine always has to create a dynamic PL/SQL call for your procedure, the procedure is called, the if for debug is checked...
Much more processing than the declarative check. PL/SQL in APEX cost always more and the declarative check.
Greetings
Patrick
Man, I'm feeling stupid today, but I can't seem to get this to work.
I put the following into a new page process:
BEGIN
apex_global_arrays.print();
END;
and Apex replies with the error:
ORA-06550: line 3, column 5: PLS-00201: identifier 'APEX_GLOBAL_ARRAYS.PRINT' must be declared
It's a sad statement of my level of Apex sophistication that I'm hoping this is an Apex installation error on the DBA's part, not that I can't use something so basic properly! :-/
Thanks for any help you choose to give (after you finish laughing).
- Stew
Stew,
You can install APEX_GLOBAL_ARRAYS in the parsing schema or in a separate schema.
Did you install the package in the parsing schema? You can find the parsing schema of your application on application definition page.
When you choose for a separate schema you have to grant execute right to the parsing schema and create a (public) synonym for APEX_GLOBAL_ARRAYS (or prefix the call with schema owner but prefixing is not considered good practice).
I don't think you can blame your DBA, but you can try anyway ;-). Your comment also reminds me to add a note about the installation.
With kind regards,
Jornica
Jornica,
Yeah I was just being dumb. I mistakenly assumed that you were talking about a standard Apex package, not one you'd (kindly) provided.
Before I read your patient response, I noticed there was a download link, so I installed it in the right schema and created an On Submit (before validation and calculation process).
When I click the Developer Debug link, it refreshes the page and shows all the debug details. I'm surprised that it shows the global arrays here, since I set its condition for REQUEST IN SUBMIT.
And if I change a data value and click Submit, it doesn't print the arrays before it starts processing.
Did I totally misunderstand the point here? I thought it would show you the latest changes before your On Submit post-processing runs. Not so much???
Thanks,
Stew
p.s. Hey, I may be slow, but at least I'm willing to admit it! :-/
Stew,
That the way APEX works:
When you press the submit button a request is sent to the APEX engine, which generates a new page. Any debug information will be on this new page.
With kind regards,
Jorrit
Jorrit,
Thanks for the reply. Unfortunately, I'm not seeing the array show up after Submit. In fact, I'm not seeing it at all anymore. But I think just re-reading your post helped me realize that some arrays are sparse, which I wasn't taking into account. Thanks for that tip!
Maybe I need to disable my processes that run after Submit?
Jornica,
should it work for manual tabular form based on apex_item calls?
Thanks.
Edward
Edward,
Yes, it should work. The first argument of apex_item refers to the number of the global array.
With kind regards,
Jornica
Edward,
Yes, it should work. The first argument of apex_item refers to the number of the global array.
With kind regards,
Jornica
Post a Comment