Running Custom Code for Tabular Forms (Part 1)

Note: An updated version of this article is now available which covers how to run custom code for a manual Tabular Form in APEX 5. There is also a new article which covers how to modify data from multiple tables in the same Tabular Form.

One of my biggest pet peeves with Tabular Forms in APEX is that it would only run basic (Insert, Update, Delete) DML functions against a table. This works really well for basic situations but more often than not data must be processed by a procedure to handle all the business logic. For this reason, I've avoided Tabular Forms for a very long time.

Last week at OGh APEX World, Dimitri Gielis showed how you can run your own procedure against a tabular form. Here's how to do it:

First, create a Tabular Form using the standard wizard. This will create the standard validations and processes for the page. Their will be two Page Processing processes as shown below.

These processes will automatically handle any of the data changes that a user makes in the Tabular Form. For now you can go ahead and remove each process as we'll use a custom procedure to process the data instead.

Next, create a new process. The important part comes when creating the process; be sure to select the Tabular Form option (as shown below).

In your PL/SQL block, you can now reference each of the columns using their column names (example :SAL and :ENAME). What's even better is that APEX will only run the code against rows that have changed which can save a lot of processing time. For example, if only two rows in a 15 row table were changed the code will be executed twice.

In the next article I'll show how to expand this functionality beyond a base table and use this new technique to modify any data set.

oracleapex.com (part 2)

Back in December I blogged about the new domain: oracleapex.com. I used the domain to host shortcuts to common links for APEX. One example that I use on a regular basis is api.oracleapex.com which points to the latest API APEX documentation.

Initially I used the blog post to host the list of available shortcuts. This wasn't the best solution but it allowed me to quickly inform everyone of the URLs. I have since created an application (built using APEX of course) to list all the redirect shortcuts on oracleapex.com.

Check it out and let me know what you think. Of course if you'd like a specific URL please let me know either by leaving a comment or tweeting me at @martindsouza

APEX 5.0 and KScope 14

A few weeks ago the Oracle APEX team released APEX 5.0 EA1 (Early Adopter). You can try the new version of APEX at ea.oracleapex.com.

I expect that the APEX team will release an EA2 and possible EA3 version before officially launching APEX 5.0 with the final release coming around June. Just to be clear, these are my personal estimates not any inside information.

Releasing APEX 5.0 in June would be perfect timing as it coincides with Kscope 14. The conference has a lot of talks specifically focused on APEX 5.0 from both the Oracle development team and some of the worlds leading experts on all things APEX. I think that this conference will be a great opportunity for people (both developers and managers) to learn about all the new features and get hands on experience with this latest version.

One of the unique aspects of Kscope is that you'll be able to easily talk to the Oracle APEX team, Oracle ACE and ACE Directors, and other experts in the community. These are the people that write the blogs, read the books, and do the webinars that you may have read/seen. You can sit down with them and get their advice on how to integrate 5.0 and leverage it to enhance your current applications and increase ROI.

I'll be giving a talk at Kscope 14 called "APEX 5.0 - New Features Highlights". It will cover some of the new aspects of APEX that will help both developers and the business. You can see all the Kscope 14 APEX abstracts here. If you're thinking of coming to the conference you should register soon as Early Bird rates end on March 25th.

Hope to see you at Kscope 14!

Referencing USER in APEX

It’s not uncommon to reference the current user as USER in your pl/sql code. A simple use case may be to determine the client or environment that you’re running in (ex: dev, test, prod).

Referencing USER will have some slight side effects when running the code in APEX as the current USER is actually APEX_PUBLIC_USER (or what ever user you configured). This can cause issues in your application. To resolve it, simply reference sys_context('userenv','current_schema’) instead.

Example:

-- Via SQL*Plus
select user, sys_context('userenv','current_schema')
from dual;

USER        SYS_CONTEXT('USERENV
---------- --------------------
GIFFY        GIFFY

If you run the same query in APEX the output is shown below. You'll notice that referencing USER it does not display my current schema (GIFFY in this case).

The same applies to compiled code executed from APEX. For example if you have a procedure that references USER and that procedure is run from APEX then USER will be APEX_PUBLIC_USER.

This can be really tough to detect in automated tests as when testing via SQL*Plus, USER will return the current schema name.