Thursday, 30 October 2014

Debugging parameterised views outside of apex

Recently I've been working on a project that had some views that needed to reference some session state information, which uses the ever too familiar v function:

select *
from some_table
where some_item = v('P1_SOME_ITEM')

Since I work extensively in SQL Developer, when I'm debugging, it becomes a bit more difficult, because we are outside the context of your apex session, our views data comes back empty.

One solution I've come up with to help with this is using some un-documented procedures to create an apex session outside the apex scope. Actually, I can't take the credit, I stole the code from @martindsouza's blog with a few minor adjustments.

create or replace package apex_session_utl

    procedure re_init_session(
        p_session_id in apex_workspace_sessions.apex_session_id%type);
    function get_session_username(
        p_session_id in apex_workspace_sessions.apex_session_id%type) 
    return apex_workspace_sessions.user_name%type;
    function get_session_application(
       p_session_id in apex_workspace_sessions.apex_session_id%type) 
    return apex_workspace_activity_log.application_id%type;   

end apex_session_utl;

create or replace PACKAGE BODY apex_session_utl AS

        Example used from:
    procedure re_init_session(
        p_session_id in apex_workspace_sessions.apex_session_id%type)
        l_workspace_id apex_applications.workspace_id%type;
        l_cgivar_name owa.vc_arr;
        l_cgivar_val owa.vc_arr;
        l_app_id NUMBER;
        l_app_id := get_session_application(p_session_id);
        l_cgivar_name(1) := 'REQUEST_PROTOCOL';
        l_cgivar_val(1) := 'HTTP';
            num_params => 1
          , param_name => l_cgivar_name
          , param_val => l_cgivar_val);
        select workspace_id
        into l_workspace_id
        from apex_applications
        where application_id = l_app_id;
        wwv_flow_api.set_security_group_id (l_workspace_id);
        apex_application.g_instance := 1;
        apex_application.g_flow_id := l_app_id;
        apex_application.g_flow_step_id := 1;
            p_uname => get_session_username(p_session_id)
          , p_session_id => NULL
          , p_app_page => apex_application.g_flow_id || ':' || 1);
            p_session_id => p_session_id);
    end re_init_session;
    function get_session_username(
        p_session_id in apex_workspace_sessions.apex_session_id%type) return apex_workspace_sessions.user_name%type
        l_user_name apex_workspace_sessions.user_name%type;
        select user_name
        into l_user_name
        from apex_workspace_sessions
        where apex_session_id = p_session_id;
        return l_user_name;
    end get_session_username;
    function get_session_application(
        p_session_id in apex_workspace_sessions.apex_session_id%type) return apex_workspace_activity_log.application_id%type
        l_application_id apex_workspace_activity_log.application_id%type;
        distinct application_id into l_application_id
      from (
        , dense_Rank() over (order by view_date desc) ranked
        from apex_workspace_activity_log
        where apex_session_id = p_session_id
        and application_schema_owner not like 'APEX_%'
      where ranked=1;
      return l_application_id;
    end get_session_application;    

END apex_session_utl;

I've also place a copy of this code in a github gist for better readability:

So, with this package in place, you can grab your session identifier from your apex URL and issue that following command:



This will give you immediate access to items like APP_USER and APP_SESSION. You should be able to set other items with apex_util.set_session_state.


  apex_util.set_session_State('P1_SOME_ITEM', 2);

Any views that reference this item should now return data! One gotcha I just noticed, setting an item value in SQL Developer will reflect back into your web session, but setting an item value in your web session doesn't reflect back in SQL Developer.

There may be a better way to handle this, but hope this helps!

Thursday, 14 August 2014

Getting the instance URL

To get the instance URL of your APEX environment, it is not too uncommon to use OWA_UTIL.GET_CGI_ENV function calls, similar to the below block:

    l_protocol varchar2(5);
    l_host varchar2(150);
    l_script varchar2(15);
    l_instance_url varchar2(200);

    l_protocol      := owa_util.get_cgi_env('REQUEST_PROTOCOL');
    l_host          := owa_util.get_cgi_env('HTTP_HOST');
    l_script        := owa_util.get_cgi_env('SCRIPT_NAME');
    l_instance_url := l_protocol;
    l_instance_url := l_instance_url || '://';
    l_instance_url := l_instance_url || l_host;
    l_instance_url := l_instance_url || l_script;
    l_instance_url := l_instance_url || '/';

Something you might not know, is that there are a couple of helper functions in the APEX API that allow you to do exactly that.





Option 2 seems to work even without being in the context of a HTTP session, but as per the docs: "This function requires that the instance setting Application Express Instance URL for emails is set."

Wednesday, 6 August 2014


I recently saw a link to a presentation that made mention of APEX_ZIP for the upcoming release of APEX (Version 5). Currently in early adopter, you can have a play around. So just thought I'd go and have a bit more of a play to see the possiblities.

On the presentation, and specifically for extracting files the sample code is:

    l_zip_file  BLOB;
    l_unzipped_file     BLOB;
    l_files             apex_zip.t_files;

    select file_content
    into l_zip_file
    from my_zip_files
    where file_name = :P13_file_name;
    l_files := apex_zip.get_files(p_zipped_blob => l_zip_file);
    for i in 1..l_files.COUNT
        l_unzipped_file := apex_zip.get_file_content(p_zipped_blob      => l_zip_file,
                                                     p_file_name        => l_files(i));
        insert into my_files (file_name, file_content)
        values (l_files(i), l_unzipped_file);


With a bit of looking around, we can soon find that APEX_ZIP refers to WWV_FLOW_ZIP, and we can grab the package specification by querying all_source. It seems that the package is derived from that code originally shared on the OTN forums here:

Side note: If the zip file contains folders, you may need to get familiar with string functions to weed out only the folders you are interested in.

The other common usage would of course be creating a zip:

    l_output_zip BLOB;
    l_filename varchar2(200) := '';

    for file in (
        select filename, contents
        from unzipped_files
            p_zipped_blob       => l_output_zip
          , p_file_name         => file.filename
          , p_content           => file.contents
        p_zipped_blob           => l_output_zip
    insert into zipped_files (mime_type, filename, contents) 
        values ('application/zip', l_filename, l_output_zip);


If you wanted to put files in a specific folder, you just need to prepend the file name with the folder name and a forward slash: 'folder/' || file.filename

nb: it is essential to call apex_zip.finish once all files have been added

Thursday, 3 July 2014

Oracle vim config

This page gives a pretty good overview of tweaks you can do, so I just want to re-gurgitate what I have done (from that site).

Syntax Highlighting

Grab the file:, and save this in: ~/.vim/syntax/

Then edit your .vimrc file and add the following line (to auto detect the filetype based on the extension):

au BufNewFile,BufRead *.fun,*.pks,*.pkb,*.sql,*.pls,*.plsql    set filetype=plsql

Auto Indentation

Grab the file:, and save this in ~/.vim/indent/

Then edit your .vimrc file and add the following line

runtime! indent.vim


Though, I prefer just to use auto indent - there are some special situations where it wont calculate the indentation correctly using the indentation file above.

So this gives me the following in my .vimrc file:
au BufNewFile,BufRead *.fun,*.pks,*.pkb,*.sql,*.pls,*.plsql    set filetype=plsql
au BufNewFile,BufRead *.fun,*.pks,*.pkb,*.sql,*.pls,*.plsql    set nosmartindent

Wednesday, 2 July 2014

Accessing Google data, part 2


Back in Novemeber 2012, I blogged about accessing Google data from APEX - and provided some sample code to getting started. You can find this post here:

Over the past little while, I have been (slowly) working on a more modularised API, that can be easily built upon to add support for additional Google services. I have called it PL/GAPI (Google API). It can be found on github:

I hope the wiki on github is straightforward enough to follow in setting this up in your local environment:


I've started with the following services:
  • Drive
  • Calendar

Without going into too much detail (since it's all on the wiki linked earlier), you link to an authorization URL with: 


This tells the system where to return to and P0_SCOPE defines what scope you are requesting authorization for. Current scopes defined:

Once the user accepts, they are returned to the application in the parameters specified in the above redirect (p_return_app, p_return_page) along with the item to store the refresh token in.

Refresh token is a means to be able to get the access token without continually prompting the user for access. All requests require a valid access token to complete successfully. Because the returned refresh token contains the '/' character, it is escaped by APEX. Before storing it, you need to un-escape the string. To do this, I used the function: UTL_I18N.UNESCAPE_REFERENCE. 

Once we have the refresh token, we can easily get the access token with:


Read more about the implementation here:

Then for example, we can add a new folder to our drive:

            p_folder_name       => :P2_NEW_FOLDER_NAME
          , p_access_token      => :GOOGLE_ACCESS_TOKEN);

I've set up a few examples on the wiki, but really, at this stage, the sample application in the samples folder is the best tool to refer to for sample usage


Any feedback, criticisms, suggestions about the implementation are more than welcomed.

note: It's a long way from being complete, but just thought it was time to share.

Saturday, 3 May 2014

Triggering dynamic actions from a dialog button

A typical usage scenario is you create a region, give it a static ID, and set the default display to be hidden - the latter is handled if you select your region template as modal region.

A typical JavaScript dynamic action to display the dialog, would be:

    width: 500,
    title: 'My Dialog',
    buttons: [
            text: 'Submit',
            click: function() { 
} }, { text: 'Cancel', click: function() { $(this).dialog("close"); } } ] })

So the question is, how do we get some other dynamic action to fire when the button is clicked?

Option 1

On the button, give it an id property, such as:

    text: 'Submit',
    id: 'btn-submitDialog'
    click: function() { 


And then, on your dynamic action definition, you can specify a click event and on the selection type specify jQuery Selector, passing in #<id>. So given the above button, you would pass in #btn-submitDialog. Also, specify the event scope as dynamic.

Option 2

Trigger a custom event. This option has the benefit that if you want the same dynamic action to fire from multiple sources, just trigger the same event.

On the button click handler, fire apex.event.trigger(document, 'btn-submitDialogClicked'), such as:

    text: 'Submit',
    click: function() { 
        apex.event.trigger(document, 'btn-submitDialogClicked');


Then, create a dynamic action specifying the When as Custom (under the Custom Event heading). As from the triggered event above, specify the string: btn-submitDialogClicked. Specify Selection Type as DOM Object, and the DOM Object as document.

Option 3

Craft the display of the dialog based on a before page submit dynamic action adding a when condition such that it only appears when the field is empty.

E.g. You may want to display a dialog on certain conditions when the user submits the page.

In this case, the click handler would just submit the page

    text: 'Submit',
    click: function() { 


Then add a dynamic action with the event being Before Page Submit (under Framework Events heading). In the when condition, specify on what situations the dialog should appear. So in my sample I have a field P21_SAMPLE_FIELD1 that I want a value in before actually submitting the page, so I specify the when condition as JavaScript expression, and the value: $v('P21_SAMPLE_FIELD1') == '' && == 'SUBMIT'

Then you want to add two true actions:

1. An Execute JavaScript Code dynamic action that will render the dynamic action (as described at the start of the article)
2. A Cancel Event action (under the Miscellaneous heading) so the page doesn't get submitted. Note: you will want to have this sequenced after displaying the dialog, otherwise the dialog won't appear.

nb: Option 3 isn't exactly firing another dynamic action when clicking the button, but continues submitting the page when the action's condition is not met - I hope it gives you an idea of some options in regards to page processing and dialogs.

I have set up a basic demo with the 3 options in force on the following page:

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: