Skip to content

Don’t (always) call v()

February 25, 2014

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)

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;
About these ads

From → APEX

5 Comments
  1. Tom permalink

    Another one I like is apex_application_global.g_image_prefix !

  2. You really should not rely too much on these globals. We changed them in the past and will continue to do that in the future. That being said, some that are already widely used are quasi standard. If a web search for a global returns many results, it is less likely that we remove it and cause regressions in existing applications. On the other hand, the more people use globals or undocumented APIs, the harder it is for us to make architectural changes to APEX itself.

    Regarding v(), your assumption is not far off. One minor thing that I would like to add is that access to session state is optimized. We load data from the session state table once at the beginning of request processing. The v() function just returns results of a PL/SQL table. Changes to session state cause updates to the base table (and that nasty commit) and modify the PL/SQL table.

    • Hi Chris,

      Thank you very much for clarifying that – I’d read somewhere that v() may issue a query and assumed that was the case, not that I’d done any tracing to determine that.

      It sounds like best practice in the case of things like APP_ID, etc. are to use v() then, instead of the globals (except, perhaps, in a query in which we want to avoid a function call overhead).

      Of course I assume that the actual implementation of v() is somewhat of a trade secret, and more importantly, may change from release to release – but can I press you further for details – e.g. does v() use an array indexed by the variable name for fast access, or does it do a loop through an array loaded with a bulk select – or both? Does it always load the values of all items in a session, or just for the current page? Is there any scenario where a call to v() will cause it to issue SQL?

  3. Hi Jeffrey,

    no problem. Regarding performance, in 5.0 we provide the sys_context APEX$SESSION. It contains attributes for WORKSPACE_ID, APP_SESSION and APP_USER, which can be used in performance critical queries.

    Right, the implementation of v() changes over time. Currently, it loops through a PL/SQL table for session state. In 4.2, I considered changing that table to have the item name as key, but internally we sometimes reference session state by item id instead of name. It is during session setup when we bulk load this PL/SQL table with all saved session state, not in v(). The select statement does not restrict by page, but by application (since apps may share a session). The seldom used p_flow parameter of v() can be used to load state of another application that shares the session. This would use SQL. Same with some built-in names like SYSTEM_HELP_URL, which give you the values of instance parameters.

  4. wesleyscott79 permalink

    I tend to avoid calls to v() & nv() as much as possible, parameterising from bidn variables where possible.

    Sometimes I’ve found great benefits in using scalar subqueries when I have to use it in SQL.
    eg:
    my_column = (select v(‘MY_VAL’) from dual)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 202 other followers

%d bloggers like this: