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('APP_USER')    = APEX_APPLICATION.g_user
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]:

FUNCTION v (p_name IN VARCHAR2) RETURN VARCHAR2 IS
  res VARCHAR2(4000);
BEGIN
  CASE p_name
  WHEN 'APP_ID' THEN
    res := APEX_APPLICATION.g_flow_id;
  WHEN 'APP_USER' THEN
    res := APEX_APPLICATION.g_user;
  WHEN 'DEBUG' THEN
    IF APEX_APPLICATION.g_debug THEN
      res := 'YES';
    ELSE
      res := 'NO';
    END IF;
  WHEN 'REQUEST' THEN
    res := APEX_APPLICATION.g_request;
  ... etc. ...
  ELSE
    BEGIN
      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;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN NULL;
    END;
  END CASE;
  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

NOTE: update for APEX 5 (see below)

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
PRAGMA AUTONOMOUS_TRANSACTION;
begin
  APEX_UTIL.set_session_state
    (p_name => p_name
    ,p_value => p_value);
  COMMIT;
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.

ADDENDUM:

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);
BEGIN
  p1_name := v('P1_NAME');
  
  sv('P1_NAME', p1_name);
END;

UPDATE for APEX 5

As of Oracle APEX 5.0, APEX_UTIL.set_session_state supports a new optional parameter, p_commit (documentation). It is defaulted to true which preserves the old behaviour (i.e. it might or might not commit).

If you set p_commit to false, the procedure will not issue any commit. This removes the need for the autonomous transaction, and leaves the responsibility for committing to the developer; if it’s called from an APEX page process, it will be committed automatically.


APEX_UTIL.set_session_state may or may not commit

Updated for APEX 5 – refer below

When should you commit or rollback a transaction? As late as possible, I would have thought, based on most of the advice in the Oracle world. You certainly want this to be predictable and consistent, at least.

Unfortunately, if you use APEX_UTIL.set_session_state in your PL/SQL process, the result is not so predictable.

Thanks to Martin D’Souza who alerted me to this. I love learning new things, but occasionally you get a bad surprise like this and it’s not so pleasant.

Test case set up – create a table with a single row, and create a simple Apex application with one page, with one region, with an item (P1_N) and a Submit button.

CREATE TABLE test (N NUMBER);
INSERT INTO test VALUES (1);
COMMIT;

TEST CASE #1

Add an On Submit process to the page which fires when the Submit button is clicked, which executes the following:

BEGIN
  UPDATE test SET n = 3;
  COMMIT;
  APEX_UTIL.set_session_state('P1_N', 1);
  UPDATE test SET n = 2;
  APEX_UTIL.set_session_state('P1_N', 1);
  ROLLBACK;
END;

What value would you expect to see in the database table now? I would have expected that the table would hold the value 3 – and indeed, it does.

TEST CASE #2

Modify the process slightly – after the second update, set the item to something different:

BEGIN
  UPDATE test SET n = 3;
  COMMIT;
  APEX_UTIL.set_session_state('P1_N', 1);
  UPDATE test SET n = 2;
  APEX_UTIL.set_session_state('P1_N', 4); --changed here
  ROLLBACK;
END;

This time, the second update to the table has been committed before we issued our ROLLBACK. The new value 2 has been saved to the database. Why?

It’s because APEX_UTIL.set_session_state will issue a COMMIT – but only if the value of the item is changed. If you happen to call set_session_state with the value that the item already has, it does nothing, and does not COMMIT. I understand why a COMMIT is ultimately necessary (Apex session state is stored in a table) – but I disagree that it’s necessary for it to commit my (potentially partial) transaction along with it.

This means that if an exception is raised somewhere in my process, the resulting rollback may or may not rollback the entire transaction, depending on whether any prior calls to set_session_state happened to COMMIT or not. This is difficult to predict and therefore makes debugging harder. Not to mention the fact that it violates the general principle of “either the whole transaction succeeds and is COMMITted, or it fails and the whole transaction is rolled back”. I’m sorry, Apex, but you should not arbitrarily commit part of my transaction without at least telling me.

Mitigations for this? I’m not sure yet. One suggestion from this forum thread was to make the procedure use an autonomous transaction. This would align it more closely to what most developers would expect, I think. Unfortunately it appears the suggestion was rejected (or put on hold indefinitely).

I’m planning on refactoring my code to shift all calls to set_session_state to as late in the process as possible; in addition, I’m thinking that I would put an explicit COMMIT prior to these calls so that my code would have more predictable behaviour. But the idea of wrapping set_session_state in a wrapper procedure with an autonomous transaction seems good to try out as well.

UPDATE for APEX 5

As of Oracle APEX 5.0, APEX_UTIL.set_session_state supports a new optional parameter, p_commit (APEX_UTIL documentation). It is defaulted to true which preserves the old behaviour (i.e. it might or might not commit).

If you set p_commit to false, the procedure will not issue any commit. This removes the need for the autonomous transaction, and leaves the responsibility for committing to the developer; if it’s called from an APEX page process, it will be committed automatically.


Build your APEX application better – do less in APEX

I recently saw this approach used in a complex Apex application built for my current client, and I liked what I saw – so I used a similar one in another project of mine, with good results.

  1. Pages load and process faster
  2. Less PL/SQL compilation at runtime
  3. Code is more maintainable and reusable
  4. Database object dependency analysis is much more reliable
  5. Apex application export files are smaller – faster to deploy
  6. Apex pages can be copied and adapted (e.g. for different interfaces) easier

ratsnest-app
How did all this happen? Nothing earth-shattering or terribly original. I made the following simple changes – and they only took about a week for a moderately complex 100-page application (that had been built haphazardly over the period of a few years):

  1. All PL/SQL Process actions moved to database packages
  2. Each page only has a single Before Header Process, which calls a procedure (e.g. CTRL_PKG.p1_load;)
  3. Each page only has a single Processing Process, which calls a procedure (e.g. CTRL_PKG.p1_process;)
  4. Computations are all removed, they are now done in the database package

The only changes I needed to make to the PL/SQL to make it work in a database package were that bind variable references (e.g. :P1_CUSTOMER_NAME) needed to be changed to use the V() (for strings and dates) or NV() (for numbers) functions; and I had to convert the Conditions on the Processes into the equivalent logic in PL/SQL. Generally, I would retrieve the values of page items into a local variable before using it in a query.

My “p1_load” procedure typically looked something like this:

PROCEDURE p1_load IS
BEGIN
  msg('p1_load');

  member_load;

  msg('p1_load Finished');
END p1_load;

My “p1_process” procedure typically looked something like this:

PROCEDURE p1_process IS
  request VARCHAR2(100) := APEX_APPLICATION.g_request;
BEGIN
  msg('p1_process ' || request);

  CASE request
  WHEN 'CREATE' THEN

    member_insert;

  WHEN 'SUBMIT' THEN

    member_update;

  WHEN 'DELETE' THEN

    member_delete;

    APEX_UTIL.clear_page_cache
      (APEX_APPLICATION.g_flow_step_id);

  WHEN 'COPY' THEN

    member_update;

    -- clear the member ID for a new record
    sv('P1_MEMBER_ID');

  ELSE
    NULL;
  END CASE;

  msg('p1_process Finished');
END p1_process;

I left Validations and Branches in the application. I will come back to the Validations later – this is made easier in Apex 4.1 which provides an API for error messages.

It wasn’t until I went through this exercise that I realised what a great volume of PL/SQL logic I had in my application – and that PL/SQL was being dynamically compiled every time a page was loaded or processed. Moving it to the database meant that it was compiled once; it meant that I could more easily see duplicated code (and therefore modularise it so that the same routine would now be called from multiple pages). I found a number of places where the Apex application was forced to re-evaluate a condition multiple times (as it had been copied to multiple Processes on the page) – now, all those processes could be put together into one IF .. END IF block.

Once all that code is compiled on the database, I can now make a change to a schema object (e.g. drop a column from a table, or modify a view definition) and see immediately what impact it will have across the application. No more time bombs waiting to go off in the middle of a customer demo. I can also query ALL_DEPENDENCIES to see where an object is being used.

I then wanted to make a Mobile version of a set of seven pages. This was made much easier now – all I had to do was copy the pages, set their interface to Mobile, and then on the database, call the same procedures. Note that when you do a page copy, that Apex automatically updates all references to use the new page ID – e.g. if you copy Page 1 to Page 2, a Process that calls “CTRL_PKG.p1_load;” will be changed to call “CTRL_PKG.p2_load;” in the new page. This required no further work since my p1_load and p1_process procedures merely had a one-line call to another procedure, which used the APEX_APPLICATION.g_flow_step_id global to determine the page number when using page items. For example:

PROCEDURE member_load IS
  p VARCHAR2(10) := 'P' || APEX_APPLICATION.g_flow_step_id;
  member members%ROWTYPE;
BEGIN
  msg('member_load ' || p);  

  member.member_id := nv(p || '_MEMBER_ID');  
  msg('member_id=' || member.member_id);  

  IF member.member_id IS NOT NULL THEN    

    SELECT *
    INTO   member_page_load.member
    FROM   members m
    WHERE  m.member_id = member_load.member.member_id;    

    sv(p || '_GIVEN_NAME',        member.given_name);
    sv(p || '_SURNAME',           member.surname);
    sv(p || '_SEX',               member.sex);
    sv(p || '_ADDRESS_LINE',      member.address_line);
    sv(p || '_STATE',             member.state);
    sv(p || '_SUBURB',            member.suburb);
    sv(p || '_POSTCODE',          member.postcode);
    sv(p || '_HOME_PHONE',        member.home_phone);
    sv(p || '_MOBILE_PHONE',      member.mobile_phone);
    sv(p || '_EMAIL_ADDRESS',     member.email_address);
    sv(p || '_VERSION_ID',        member.version_id);

  END IF; 

  msg('member_load Finished');
END member_load;

Aside: Note here the use of SELECT * INTO [rowtype-variable]. This is IMO the one exception to the “never SELECT *” rule of thumb. The compromise here is that the procedure will query the entire record every time, even if it doesn’t use some of the columns; however, this pattern makes the code leaner and more easily understood; also, I usually need almost all the columns anyway.

In my database package, I included the following helper functions at the top, and used them throughout the package:

DATE_FORMAT CONSTANT VARCHAR2(30) := 'DD-Mon-YYYY';

PROCEDURE msg (i_msg IN VARCHAR2) IS
BEGIN
  APEX_DEBUG_MESSAGE.LOG_MESSAGE
    ($$PLSQL_UNIT || ': ' || i_msg);
END msg;

-- get date value
FUNCTION dv
  (i_name IN VARCHAR2
  ,i_fmt IN VARCHAR2 := DATE_FORMAT
  ) RETURN DATE IS
BEGIN
  RETURN TO_DATE(v(i_name), i_fmt);
END dv;

-- set value
PROCEDURE sv
  (i_name IN VARCHAR2
  ,i_value IN VARCHAR2 := NULL
  ) IS
BEGIN
  APEX_UTIL.set_session_state(i_name, i_value);
END sv;

-- set date
PROCEDURE sd
  (i_name IN VARCHAR2
  ,i_value IN DATE := NULL
  ,i_fmt IN VARCHAR2 := DATE_FORMAT
  ) IS
BEGIN
  APEX_UTIL.set_session_state
    (i_name, TO_CHAR(i_value, i_fmt));
END sd;

PROCEDURE success (i_msg IN VARCHAR2) IS
BEGIN
  msg('success: ' || i_msg);
  IF apex_application.g_print_success_message IS NOT NULL THEN
    apex_application.g_print_success_message :=
      := apex_application.g_print_success_message || '<br>';
  END IF;
  apex_application.g_print_success_message
    := apex_application.g_print_success_message || i_msg;
END success;

Another change I made was to move most of the logic embedded in report queries into views on the database. This led to more efficiencies as logic used in a few pages here and there could now be consolidated in a single view.

The challenges remaining were record view/edit pages generated by the Apex wizard – these used DML processes to load and insert/update/delete records. In most cases these were on simple pages with no other processing added; so I left them alone for now.

On a particularly complex page, I removed the DML processes and replaced them with my own package procedure which did the query, insert, update and delete. This greatly simplified things because I now had better control over exactly how these operations are done. The only downside to this approach is that I lose the built-in Apex lost update protection mechanism, which detects changes to a record done by multiple concurrent sessions. I had to ensure I built that logic into my package myself – I did this with a simple VERSION_ID column on the table (c.f. Version Compare in “Avoiding Lost Updates”).

The only downsides with this approach I’ve noted so far are:

  1. a little extra work when initially creating a page
  2. page item references are now strings (e.g. “v('P1_RECORD_ID')“)  instead of bind variables – so a typo here and there can result in somewhat harder-to-find bugs

However, my application is now faster, more efficient, and on the whole easier to debug and maintain – so the benefits seem to outweigh the downsides.