Tag: tips-&-tricks

APEX: Save a user’s checkbox selection on local PC

You want a checkbox item on a page which is a preference, you want it to be remembered for the user across login sessions, but you don’t want the overhead of storing it in a database table. You might choose to store the value in a cookie instead. It may be lost (e.g. when the user clears their cookies or changes to a different browser or another computer), but we don’t mind – it’s just a preference.


1. Create checkbox item, e.g. P1_STAY_ON_PAGE

Display As = Checkbox
Template = No Label
List of values definition = STATIC2:Stay on page;Y

2. Add dynamic action to the checkbox item to save it when it’s changed

Event = Change
Selection Type = Item(s)
Item(s) = P1_STAY_ON_PAGE
Condition = (none)
True Action = Execute JavaScript Code
Fire On Page Load = (No)
Code = SetCookie("APEX_P1_STAY_ON_PAGE",$v("P1_STAY_ON_PAGE"));

3. Add dynamic action on page load to load it

Event = Page Load
True Action = Execute JavaScript Code
Code = $s("P1_STAY_ON_PAGE",GetCookie("APEX_P1_STAY_ON_PAGE"));

Note that the cookie name (“APEX_P1_STAY_ON_PAGE” in this example) is up to you to choose. Probably best to try making it specific to your application so it doesn’t clash with anything else.

Don’t (always) call v()

Instead of calling a function, when you can get the same effect by accessing a documented PL/SQL variable, you should. For example:

v('REQUEST')     = APEX_APPLICATION.g_request
v('APP_ID')      = APEX_APPLICATION.g_flow_id
v('APP_PAGE_ID') = APEX_APPLICATION.g_flow_step_id
v('DEBUG')       = APEX_APPLICATION.g_debug

(Note – g_debug is a boolean, unlike the v() equivalent)

UPDATE: If you’re using Apex 5, you can now get the User and Session ID from the APEX$SESSION application context.

There’s more here: documentation for the APEX_APPLICATION package

I suspect that the implementation of v() is something like this [EDIT: read the comments for more commentary on this, and a more accurate picture of what v() actually does]:

  res VARCHAR2(4000);
  CASE p_name
    res := APEX_APPLICATION.g_flow_id;
    res := APEX_APPLICATION.g_user;
      res := 'YES';
      res := 'NO';
    END IF;
    res := APEX_APPLICATION.g_request;
  ... etc. ...
      SELECT s.item_value
      INTO res
      FROM wwv_<session-values-or-something> s
      WHERE s.item_name = p_name
      AND s.flow_id = APEX_APPLICATION.g_flow_id
      AND s.session_id = APEX_APPLICATION.g_instance;
        RETURN NULL;
  RETURN res;
END v;

In addition, instead of calling v('APP_SESSION') / v('SESSION'), you could call the undocumented function APEX_APPLICATION.get_session_id instead, which is probably faster, or refer to the global variable APEX_APPLICATION.g_instance instead. I would suspect that the function normally just returns g_instance anyway, but it’s possible there’s some more logic behind the function.

Disclaimer: use undocumented bits at your own risk.

Some other undocumented goodies that may be useful include (and a lot of these are not available at all via v()):

APEX_APPLICATION.g_flow_alias = application alias
APEX_APPLICATION.g_flow_name = application name
APEX_APPLICATION.g_flow_version = application version string
APEX_APPLICATION.g_flow_status = app availability status code, e.g. AVAILABLE_W_EDIT_LINK
APEX_APPLICATION.g_build_status = app build status code, e.g. RUN_AND_BUILD
APEX_APPLICATION.g_base_href = the base URL for the site, not including the f?p=... bit
APEX_APPLICATION.g_printer_friendly = TRUE if the page was requested with Printer Friendly flag
APEX_APPLICATION.g_excel_format = TRUE if the page’s report is being rendered in CSV format
APEX_APPLICATION.g_date_format = Application default date format
APEX_APPLICATION.g_date_time_format = Application date time format
APEX_APPLICATION.g_timestamp_format = Application default timestamp format
APEX_APPLICATION.g_timestamp_tz_format = Application default timestamp with time zone format

You can have a peek at all the globals in this package with this query (but be warned, any undocumented ones may change, and may not necessarily be set to any meaningful value when your code is running):

select owner, trim(text)
from dba_source
where name = 'WWV_FLOW'
and type = 'PACKAGE'
and ltrim(text) like 'g%'
order by owner desc, line;

Proposed wrapper for APEX_UTIL.set_session_state

I decided to try using a wrapper procedure to isolate calls to APEX_UTIL.set_session_state in an autonomous transaction. I’m currently using it in a project and seeing how it goes in terms of performance.

DISCLAIMER: Don’t just throw this into your mission-critical system without at least testing it thoroughly first.

Since I had Morten Braten’s Alexandria library handy, I simply modified his APEX_UTIL_PKG. If you’re not using this library you can create your own wrapper quite simply:

create or replace procedure sv
  (p_name  in varchar2
  ,p_value in varchar2 := NULL) as
    (p_name => p_name
    ,p_value => p_value);
end sv;

Since my system has many schemas (one for each application), I would compile this in a “common” schema and then grant execute on it to the schemas that need it, and create local synonyms in each one so that my applications just need to call sv.


As Joel Kallman rightly points out, putting set_session_state in an autonomous transaction means that the new value will not be visible to the rest of the calling code, so for example the call to v() will not return ‘Joe’ here:

sv('P1_NAME', 'Joe');
x := v('P1_NAME'); -- will not be 'Joe'

Therefore, it is intended that sv() be used as the final step in any procedure, e.g.:

PROCEDURE p1_controller IS
  p1_name VARCHAR2(100);
  p1_name := v('P1_NAME');
  <business logic that does something with/to p1_name>
  sv('P1_NAME', p1_name);

Change an item Label dynamically

Get it? “an item with many hats”… yeah ok.

Need to change the label of an item on-the-fly? When I run my Apex page it renders item labels like this:

<label for="P1_CONTACT_NUMBER">
  <span>Contact Number</span>

If the label needs to change based on another item, I could set the label with the value of another item, e.g. “&P1_CONTACT_NUMBER_LABEL.” and when the page is refreshed it would pick up the new label. But at runtime, if the label needs to change dynamically in response to changes in other items, we need to do something else.

Caveat: The need for changing the label should be very rare – it’s bad practice to overload one field with multiple meanings. But if you must, this is what you can do.

It’s easy with a Dynamic Action running some Javascript. This changes the label text for the P1_CONTACT_NUMBER item depending on the value chosen for P1_CONTACT_METHOD, which might be a radio group or select list. The method uses jquery to search for a “label” tag with the attribute “for” that associates it with the desired item; we then navigate down to the “span” element, and call the “text” function to change the label text:

if ($v("P1_CONTACT_METHOD")=='SMS') {
    $("label[for=P1_CONTACT_NUMBER]>span").text("Contact Mobile")
} else if ($v("P1_CONTACT_METHOD")=='EMAIL') {
    $("label[for=P1_CONTACT_NUMBER]>span").text("Contact Email")
} else {
    $("label[for=P1_CONTACT_NUMBER]>span").text("Contact Number")

The Dynamic Action is set up as follows:

Event = Change
Selection Type = Item(s)
Condition = (no condition)

True Action = Execute JavaScript Code
Fire On Page Load = Yes
Selection Type = (blank)
Code = (the javascript shown above)

Parallel Development in Apex

Source: http://paulhammant.com/files/multi-branch.jpgMy current client has a large number of Apex applications, one of which is a doozy. It is a mission-critical and complex application in Apex 4.0.2 used throughout the business, with an impressively long list of features, with an equally impressively long list of enhancement requests in the queue.

They always have a number of projects on the go with it, and they wanted us to develop two major revisions to it in parallel. In other words, we’d have v1.0 (so to speak) in Production, which still needed support and urgent defect fixing, v1.1 in Dev1 for project A, and v1.2 in Dev2 for project B. Oh, and we don’t know if Project A will go live before Project B, or vice versa. We have source control, so we should be able to branch the application and have separate teams working on each branch, right?

We said, “no way”. Trying to merge changes from a branch of an Apex app into an existing Apex app is not going to work, practically speaking. The merged script would most likely fail to run at all, or if it somehow magically runs, it’d probably break something.

So we pushed back a bit, and the terms of the project were changed so that development of project A would be done first, and the development of project B would follow straight after. So at least now we know that v1.2 can be built on top of v1.1 with no merge required. However, we still had the problem that production defect fixes would still need to be done on a separate version of the application in dev, and that they needed to continue being deployed to sit/uat/prod without carrying any changes from our projects.

The solution we have used is to have two copies of dev, each with its own schema, apex application and version control folder: I’ll call them APP and APP2. We took an export of APP and created APP2, and instructed the developer who was tasked with production defect fixes to manually duplicate his changes in both APP and APP2. That way the defect fixes were “merged” in a manual fashion as we went along – also, it meant that the project development would gain the benefit of the defect fixes straight away. The downside was that everything worked and acted as if they were two completely different and separate applications, which made things tricky for integration.

Next, for developing project A and project B, we needed to be able to make changes for both projects in parallel, but we needed to be able to deploy just Project A to SIT/UAT/PROD without carrying the changes from project B with it. The solution was to use Apex’s Build Option feature (which has been around for donkey’s years but I never had a use for it until now), in combination with Conditional Compilation on the database schema.

I created a build option called (e.g.) “Project B”. I set Status = “Include”, and Default on Export = “Exclude”. What this means is that in dev, my Project B changes will be enabled, but when the app is exported for deployment to SIT etc the build option’s status will be set to “Exclude”. In fact, my changes will be included in the export script, but they just won’t be rendered in the target environments.

When we created a new page, region, item, process, condition, or dynamic action for project B, we would mark it with our build option “Project B”. If an existing element was to be removed or replaced by Project B, we would mark it as “{NOT} Project B”.

Any code on the database side that was only for project B would be switched on with conditional compilation, e.g.:

$IF $$projectB $THEN
  PROCEDURE my_proc (new_param IN ...) IS...
  PROCEDURE my_proc IS...

When the code is compiled, if the projectB flag has been set (e.g. with ALTER SESSION SET PLSQL_CCFLAGS='projectB:TRUE';), the new code will be compiled.

Build Options can be applied to:

  • Pages & Regions
  • Items & Buttons
  • Branches, Computations & Processes
  • Lists & List Entries
  • LOV Entries
  • Navigation Bar & Breadcrumb Entries
  • Shortcuts
  • Tabs & Parent Tabs

This works quite well for 90% of the changes required. Unfortunately it doesn’t handle the following scenarios:

1. Changed attributes for existing Apex components – e.g. some layout changes that would re-order the items in a form cannot be isolated to a build option.

2. Templates and Authorization Schemes cannot be marked with a build option.

On the database side, it is possible to detect at runtime if a build option has been enabled or not. In our case, a lot of our code was dependent on schema structural changes (e.g. new table columns) which would not compile in the target environments anyway – so conditional compilation was a better solution.

Apart from these caveats, the use of Build Options and Conditional Compilation have made the parallel development of these two projects feasible. Not perfect, mind you – but feasible. The best part? There’s a feature in Apex that allows you to view a list of all the components that have been marked with a Build Option – this is accessible from Shared Components -> Build Options -> Utilization (tab).

Enhancement Requests:

1. If Build Options could be improved to allow the scenarios listed above, I’d be glad. In a perfect world, I should be able to go into Apex, select “Project B”, and all my changes (adding/modifying/removing items, regions, pages, LOVs, auth schemes, etc) would be marked for Project B. I could switch to “Project A”, and my changes for Project B would be hidden. I think this would require the Apex engine to be able to have multiple definitions of each item, region or page, one for each build option. Merging changes between build options would need to be made possible, somehow – I don’t hold any illusions that this would be a simple feature for the Apex team to deliver.

2. Make the items/regions/pages listed in the Utilization tab clickable, so I can easily click through and change properties on them.

3. Another thing I’d like to see from the Apex team is builtin GUI support for exporting applications as a collection of individual scripts, each independently runnable – one for each page and shared component. I’m aware there is a Java tool for this purpose, but the individual scripts it generates cannot be run on their own. For example, if I export a page, I should be able to import that page into another copy of the same application (but with a different application ID) to replace the existing version of that page. I should be able to check in a change to an authorization scheme or an LOV or a template, and deploy just the script for that component to other applications, even in other workspaces. The export feature for all this should be available and supported using a PL/SQL API so that we can automate the whole thing and integrate it with our version control and deployment software.

4. What would be really cool, would be if the export scripts from Apex were structured in such a way that existing source code merge tools could merge different versions of the same Apex script and result in a usable Apex script. This already works quite well for our schema scripts (table scripts, views, packages, etc), so why not?

Further Reading: