Thursday, 17 April 2014

Times at specific time zones

These past couple of weeks, I have been doing some work with some external APIs, so have had to do some time stamp manipulation. Here are some tips I've learnt along the way.

A quick way to get UTC time, is with the function: sys_extract_utc. With that, we can quickly get the UTC timestamp. Here is an example to return the UTC time in RFC3399/ISO8601 format:

  , 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'

To return that back into local time, you want to declare a variable with time zone support. So just say the string you have is: 2014-04-17T02:46:16.607Z, you'll want to declare it with a time zone attribute.

l_time timestamp with time zone;

Since Z refers +00:00, you need to set the time zone. If you call to_timezone or to_timezone_tz, without specifying the timezone, it will create the time in the local time zone, so we need to specifically set it with from_tz.


l_time := to_timestamp_tz('2014-04-17T02:46:16.607Z' || '+00:00', 'yyyy-mm-dd"T""Z"tzh:tzm');

(Appending the date time string with the time zone info)


l_time := to_timestamp('2014-04-17T02:46:16.607Z', 'yyyy-mm-dd"T""Z"');
l_time := from_tz(l_time, '+00:00');

(Setting the time zone after the fact)

Finally, to get the time stamp outputting in your local time zone, you can do:

l_time := l_time at local;

(Converting it to the time zone of the database)

Or, specifically set what time zone to convert it to:

l_time := l_time at time zone '+10:00';
l_time := l_time at time zone sessiontimezone;
l_time := l_time at time zone 'Australia/Sydney';

Where the time zone string can be a valid time zone name as from the v$timezone_names view; or the GMT offset, as per the example above.

If all you want to do is get it in the local time zone of the database, you can declare the time stamp with local time zone, and this will automatically output the time in the databases local time zone.

  l_timestamp timestamp with local time zone;

  l_timestamp := to_timestamp('2014-04-17T02:46:16.607Z', 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"');
  l_timestamp := from_tz(l_timestamp, '+00:00');

  dbms_output.put_line(l_timestamp );


This seems like as good a time as any to spruik the fact that Application Express comes with support for local time stamps since version 4. You can read more about that here:


Thursday, 3 April 2014

Getting around large exports with RTF report templates

If you are on a slow connection like me, you may find how much of a pain it is deploying apps that house some BI publisher report templates. It only takes a few templates before your app export becomes 10MB+ in size.

Perhaps in a future release, report templates will be treated in the same way as application images, in that you can add them as a script to supporting objects so that you don't need to export the templates each and every deployment.

If you haven't seen, apex_util has a procedure to download report queries, so by using that procedure coupled with storing your files in your own maintained table, you can avoid the issue of massive application exports.

First create a table to store the templates:

create table rtf_template(
    code varchar2(25) PRIMARY KEY
  , layout BLOB);

Then, add your template file to the table.

We will need two application items (or 1 will do if you identify the layout with exactly the same name as the query). So I've created: REPORT_NAME and REPORT_LAYOUT

Then create an on demand process, which I've named mine as: PRINT_DOC. The code looks like this:


    l_file_as_clob CLOB ;
    l_layout BLOB;
    l_asdsad CLOB;

    l_dest_offset integer := 1;
    l_src_offset integer := 1;
    l_lang_context integer := dbms_lob.default_lang_ctx;
    l_warning integer;

    select layout into l_layout
    from rtf_template
    where code = :REPORT_LAYOUT;
    dbms_lob.createtemporary(lob_loc => l_file_as_clob, cache => false);
        dest_lob => l_file_as_clob
      , src_blob => l_layout
      , amount => dbms_lob.lobmaxsize
      , dest_offset => l_dest_offset
      , src_offset => l_src_offset
      , blob_csid => dbms_lob.default_csid
      , lang_context => l_lang_context
      , warning => l_warning);
    --signature 2
    apex_util.download_print_document (
        p_file_name => :REPORT_NAME
      , p_content_disposition => 'ATTACHMENT'
      , p_application_id => :APP_ID
      , p_report_query_name => :REPORT_NAME
      , p_report_layout => l_file_as_clob
      , p_report_layout_type => 'rtf'
      , p_document_format => 'pdf'

Now, when ever you want a button to download a layout, you can just past the request parameter of: APPLICATION_PROCESS=PRINT_DOC, then set the two (or one) page items accordingly. A little like this:

It is a little extra work, but if it saves some headaches with deployments, why not ;-)

Monday, 31 March 2014

Database connectivity with Google Sheets


I happened to be watching some Google I/O videos from 2013 last week and was watching some on apps-script, which I had never really used up until now. They mentioned about having JDBC connector so that you could connect to external data sources (Oracle, MySQL, SQL Server, etc).

It's worth noting, there are 2 types scripts that can be container bound to a spreadsheet:

1. Add ons
2. Script gallery

Script gallery is not supported in the new version of sheets, in favour of add-ons. There is a market place where you can search for and install add-ons, and if your organisation is using google apps, you can even restrict your add-on to your organisation.

Unlike the script gallery where you could view the source, that is not the case for Add-ons (the code is not visible to end users). Excluding if you share the document with the development version of the code attached to the document.

Add-ons are currently in developer preview, so whilst you can develop add-ons, only approved developers can publish to the store.

For more information on apps-script in general, head over to

Database Connectivity

All the information is on their JDBC guide:, all the same, here's a little demo of connecting to an Oracle database and populating cells. I will be fetching some data from my app_users table.

Open the script editor by opening the sheet and navigating to tools --> script editor. The name you give the project is what will appear in the Add-ons menu, and then any menu entries you create will appear as a sub menu-item.

The first step is to add some menu entries:

function onOpen(e){

  .addItem("Fetch app_users", "loadAppUsers")
  .addItem("Clear sheet", "clearSheet")

function onInstall(e){

The onInstall function is there so the menu is added when the add-on is first installed, with onOpen being called whenever the sheet is opened.

Then a function to connect to the database and populate the spreadsheet:

function getConnection_(server, sid, port, username, password) {
  return Jdbc.getConnection('jdbc:oracle:thin:@//' + server + ':' + port + '/' + sid, username, password);

function loadAppUsers(){
  var conn = getConnection_(
  , '[sid]'
  , '[port]'
  , '[username]'
  , '[password]'
  var stmt = conn.createStatement();
  var rs = stmt.executeQuery('select id, user, first_name, last_name from app_users');
  var document = SpreadsheetApp.getActive();
  var cell = document.getRange("a1");
  var row = 0;
  var rsMetaData= rs.getMetaData();
    for(var col=0;col < rsMetaData.getColumnCount(); col++){
        cell.offset(row, col).setValue(rsMetaData.getColumnName(col+1)); 
      } else {
        cell.offset(row, col).setValue(rs.getString(col+1));


The clear function is nothing more than

function clearSheet(){

This gives us a sheet with the menu:

And populated spreadsheet:

Since the code is run from google servers, they need to be able to communicate with your database server, with the IP address range of the google servers being defined on the JDBC guide:

Tuesday, 18 February 2014

Finding components with a particular build option

Say for example you have a generic build status of 'Exclude' - so you can temporarily disable components without touching the conditions (it doesn't really seem in the nature of build options, but still can be done). There will undoubtedly come a time where you need to locate all said components to either remove them from the application, or remove the build option so they are re-included.

First we can query the apex_dictionary view to find any views that contain the build option column:

select *
from apex_dictionary
where column_name = 'BUILD_OPTION'

As at ApEx 4.2, this gives us the following list:


So, now it's just a matter of writing a query against all of these views to locate the build option with the specified name. Common amongst all views are workspace, application id and build option. Then we can just add another field with relevant info specific to that view and the component type to help us narrow it down.

This will give us the following query:

variable cond varchar2(25)
exec :cond := 'Exclude';

, application_id
, info
, component_type
, build_option
from (

  select workspace, application_id, 'Display name: ' || display_name info, 'UI Type' component_type,  build_option
  from apex_appl_user_interfaces
  union all
  select workspace, application_id, 'Entry label: ' || entry_label || '; For page: ' || defined_for_page info, 'Breadcrumb entries' component_type, build_option
  from apex_application_bc_entries
  union all
  select workspace, application_id, 'Computation item: ' || computation_item info, 'Application computations' component_type, build_option
  from apex_application_computations
  union all
  select workspace, application_id, 'Item name: ' || item_name info, 'Application items' component_type, build_option
  from apex_application_items
  union all
  select  workspace, application_id, 'List name: ' || list_name info, 'Lists' component_type, build_option
  from apex_application_lists
  union all
  select workspace, application_id, 'Entry text: ' || entry_text || '; Parent entry:' || parent_entry_text info, 'List entries' component_type, build_option
  from apex_application_list_entries
  union all
  select workspace, application_id, 'LOV Name: ' || list_of_values_name || '; Display value: ' || display_value info, 'Static LOV entries' component_type, build_option
  from apex_application_lov_entries
  union all
  select workspace, application_id, 'Label: ' || icon_subtext info, 'Nav bar entries' component_type, build_option
  from apex_application_nav_bar
  union all
  select workspace, application_id, 'Page ID: ' || page_id info, 'Page' component_type, build_option
  from apex_application_pages
  union all
  select workspace, application_id, 'Page ID: ' || page_id || '; Branch point: ' || branch_point || '; Branch sequence: ' || process_sequence || '; Branch name: ' || branch_name info, 'Page branch' component_type, build_option
  from apex_application_page_branches
  union all
  select workspace, application_id, 'Page ID: ' || page_id || '; Item: ' || item_name || '; Computation point: ' || computation_point info, 'Page computation' component_type, build_option 
  from apex_application_page_comp
  union all
  select workspace, application_id, 'Page ID: ' || page_id || '; Dynamic action name: ' || dynamic_action_name info, 'Dynamic action' component_type, build_option
  from apex_application_page_da
  union all
  select workspace, application_id, 'Page ID: ' || page_id || '; Process name: ' || process_name || '; Process point: ' || process_point_code info, 'Page process' component_type, build_option
  from apex_application_page_proc
  union all
  select workspace, application_id, 'Page ID: ' || page_id || '; Region name: ' || region_name info, 'Page region' component_type, build_option
  from apex_application_page_regions
  union all
  select workspace, application_id, 'Page ID: ' || page_id || '; Region name: ' ||  region_name || '; Series name: ' || series_name info, 'Flash chart series' component_type , build_option
  from apex_application_page_flash5_S
  union all
  select workspace, application_id, 'Page ID: ' || page_id || '; Validation name: ' || validation_name info, 'Page validation' component_type, build_option
  from apex_application_page_val
  union all
  select workspace, application_id, 'Page ID: ' || page_id || '; Button name: ' ||  button_name info, 'Page button' component_type, build_option
  from apex_application_page_buttons
  union all
  select workspace, application_id, 'Page ID: ' || page_id || '; Item name' || item_name info, 'Page item' component_type, build_option
  from apex_application_page_items
  union all
  select workspace, application_id, 'Tab set name: ' || tab_set || '; Tab name: ' ||  tab_name info, 'Parent tabs' component_type, build_option
  from apex_application_parent_tabs
  union all
  select workspace, application_id, 'Process name: ' || process_name info, 'Application process' component_Type, build_option
  from apex_application_processes
  union all
  select workspace, application_id, 'Shortcut name: ' || shortcut_name info, 'Application shortcut' component_type, build_option
  from apex_application_shortcuts
  union all
  select workspace, application_id, 'Tab set: ' || tab_set || '; Tab name: ' || tab_name || '; Tab label: ' || tab_label info, 'Tabs' component_type , build_option
  from apex_application_tabs

Which you can then filter by build option, and voilĂ !

I've added this to a github project so it can be propely version controlled -

Sunday, 16 February 2014

Thursday, 1 August 2013

Customising a charts legend label to reference a page item

I am generating a report (Graph) based on a date field. The requirement is for the legend label to display the data beside the legend label. This leads me on the AnyChart documentation.

First, you need to specify the legend attribute ignore_auto_item to True.

If you don't do this part, whilst the item will be found (below), the format won't be applied, despite matching successfully.

Then, you can define custom formats for each series. In my case, I have two series (Current Report and Previous Report). So, inside the legend node, add the following for each series:

    <item source="Series" series="CURRENT REPORT">
        <format><![CDATA[{%Icon} Current Report - &P22_CURRENT_REPORT.]]></format>
    <item source="Series" series="PREVIOUS REPORT">
        <format><![CDATA[{%Icon} Previous Report - &P22_PREVIOUS_REPORT.]]></format>

The result:

Monday, 22 July 2013

Accessing the last request value from a page submission

When you submit the page, you will typically have page processes that run. For me, I use these request values to determine which processes are run. This is rather straight forward for on submit processes as you can just specify the condition request = Expression 1

Or, if you have multiple requests that link to this process, Request is Contained within Expression 1, and Expression 1 would be a comma delimited set of values.

Request simply refers to the bind variable :REQUEST (or apex_application.g_request).

If we try and do the same on a page rendering process, we no longer have access to this value - which makes sense.

If you update the branch, to set the request portion of the URL, we will then be able to access the bind variable :REQUEST in all page rendering processing points.

This has the obvious downside that if the user wants to reload the page,  clicks in the address bar and hits enter (or clicks refresh), the processes on that condition will run again.

It also doesn't seem to give us access to the request in javascript which we may want to use in a dynamic action of some sort.

The accepted solution seems to be just to create your own hidden item to store the value in and use that. One important tip however is to clear the value after you've used it, so that the processes don't continually get called when refreshing the page.

So, have an on submit process that runs PL/SQL with something similar to:


Then in your on load dynamic action, after all the logic you implement, issue the following to clear the session state:

apex.server.process('', { pageItems: ['P1_HIDDEN_ITEM']}, {dataType: 'text'});

If you wanted a re-usable solution, you could do this.

Create a hidden region on global page, and add a hidden item (P0_SUBMITTED_REQUEST)

Create two application processes:

On Submit: After Page Submission - Before Computations and Validations, a with process text:


Specify sequence as 1 so it's the first process run when submitting the page.

And then another:

On Load: After Footer (page template footer)


Specify a high enough sequence that it will run after all other sequences. e.g. 1000 should be high enough.

Then in your page page rendering processes (well, any processes really), you can specify a condition that Value of Item / Column in Expression 1 = Expression 2:

Then in JavaScript (assuming you call it before the value is cleared) you can access the value with $v('P0_SUBMITTED_REQUEST'). This works with an on load dynamic action as the code is called before the page has completed i.e. calling the process which clears the value.

You can refrain from clearing it, however I prefer clearing it as if the user reloads the page, the value will still be lingering and all processes will be re-called.

For another useful solution when dealing with checksums, check out Recx's blog: