Monday, 9 February 2015

Reset an Interactive Report (IR)

To reset an IR back to the default settings, you may know you can go to the actions menu, and hit reset:

If you inspect the apply button you will see it's calling gReport.reset()

And there are a bunch of examples using this gReport object both for resetting the report and other IR functions.

The problem? This is not documented, and with APEX 5 supporting multiple IRs, this will no longer work. In your console, if you enter gReport, you will see that object no longer exists.

The other technique you can use is the clear cache portion of the URL. According to the docs:

To reset an interactive report in a link, use the string "RIR" in the Clear-Cache section of a URL. This is equivalent to the end user choosing the Reset option from the interactive report actions menu on the target page. The report is returned to the default report settings specified by the developer or saved by the user.

Just testing this on a 4.2 instance, this this only resets the PRIMARY report, and takes you back to the PRIMARY report i.e. any filters added on saved reports will remain. The reset button in the actions menu on the other hand just clears the filters added on the saved report, and you remain on that saved report.

So, the best way to programatically reset an IR?

First, I suggest giving your IR a region static ID so you can easily query the apex dictionary.

Then if we look at the APEX_IR API, we can see it has the procedure (2) RESET_REPORT, which accepts 3 parameters - page id, region_id and report_id. It is acceptable to pass in NULL to p_report_id, and it will just use the last viewed report for that region - otherwise, you'd need to make use of APEX_IR.GET_LAST_VIEWED_REPORT_ID.

Then, we can have a dynamic action with 2 true actions: Execute PL/SQL code and Refresh region, with the PL/SQL code looking something like this:

    l_region_id apex_application_page_regions.region_id%type;

    select region_id into l_region_id
    from apex_application_page_regions
    where application_id = :APP_ID
    and page_id = :APP_PAGE_ID
    and static_id = 'region-static-id';--update this accordingly
        p_page_id => :APP_PAGE_ID
      , p_region_id => l_region_id
      , p_report_id => NULL

This re-iterates the importance of only using documented functions.

APEX 5 API changes

Based on the current beta API docs:, here is what's changed.

  • Function GET_AUTO_INSTALL_SUP_OBJ added 
  • Procedure SET_AUTO_INSTALL_SUP_OBJ added

  • LOGOUT procedure deprecated

  • Function JSON added
  • Function REGEXP added

  • Procedure FREE_WORKSPACE_APP_IDS added
  • Procedure RESTRICT_SCHEMA added
  • Procedure UNRESTRICT_SCHEMA added

  • Procedure CHANGE_REPORT_OWNER added

  • Function SEARCH added

  • Function GET_ATTRIBUTE_AS_NUMBER added

  • Procedure CLOSE_OPEN_DB_LINKS added
  • Procedure IR_CLEAR deprecated
  • Procedure IR_DELETE_REPORT deprecated
  • Procedure IR_DELETE_SUBSCRIPTION deprecated
  • Procedure IR_FILTER deprecated
  • Procedure IR_RESET deprecated
  • Procedure PUBLIC_CHECK_AUTHORIZATION deprecated
  • Procedure RESET_AUTHORIZATIONS deprecated
  • Procedure SET_GROUP_GROUP_GRANTS added
  • Procedure SET_GROUP_USER_GRANTS added

  • Function MAKE_REST_REQUEST_B added

  • Package APEX_SPATIAL added
  • Package APEX_ZIP added
  • Package APEX_JSON added

JavaScript APIS

New namespaces
  • apex.da  
  • apex.debug 
  • apex.lang 
  • apex.util 

apex.navigations additions
  • apex.navigation.dialog
  • apex.navigation.dialog.cancel
  • apex.navigation.dialog.close
  • apex.navigation.dialog.fireCloseHandler
  • apex.navigation.dialog.registerCloseHandler
  • apex.navigation.dialog.title
  • apex.navigation.openInNewWindow
  • apex.navigation.popup
  • apex.navigation.redirect

apex.server additions
  • apex.server.url

Saturday, 7 February 2015

APEX 5 creating regions on an existing page

In APEX 4.2, it's nice and simple. Right click on the body node of the page tree view, and click create:

After clicking create, you would be presented with all the possible region types:

The rest is just a matter of following the steps.

APEX 5, has a new page designer, and it took me a minute or so to figure out how to add a form region, since there seems to be a couple of options for adding regions!

Firstly, in the centre column down the bottom of the page, you have a grid view of (almost) all the region types you can add. You can either drag them onto the visual layout of your page above, or right click and select where to add it to.

The other option is on the left hand pane, right click and then select Create Region.


This will create a region with the type set as: "Static Content". 

You then need to change the type on the right hand pane, and then fill out any region specific settings.

At this point, it's probably also worth mentioning that the settings for the regions on the right pane might not be displaying all settings. There are two options in the toolbar:

1. Show common
2. Show all

E.g. region static ID is only displayed if you have `Show all` mode enabled:

Some regions should be created through the new page wizard, which you will notice if you try to select Tabular Form:

And form region is not listed there - well technically, it's not a region type, so that makes sense. On the top of your page, you will notice a plus icon button. And this is how we can add form regions to our page,

Which gives us all the wizard options we've come to expect:

disclaimer: This is based on the current early adopter (3) instance of apex

Tuesday, 3 February 2015

APEX 5 blob column uploads

Existing behaviour

In APEX 4.2, to upload a file into a BLOB column, there are two patterns for getting the file into your table. In settings, specify the storage type as:

  1. BLOB column specified in Item Source attribute
  2. Table WWV_FLOW_FILES 
I tend to use method 2, and will be focusing on that pattern.

This table can also be referred to with either:
Which you can see with the following query:

select *
from all_synonyms
where table_name = 'WWV_FLOW_FILES'

On your form page, whenever you have chosen a file to upload, and submit the page, the file will be uploaded into wwv_flow_files.

The name column is the value assigned to the page item, so to fetch that particular file upload you would have something like:

    l_filerow apex_application_files%rowtype;

    select *
    into l_filerow
    from apex_application_files
    where name = :Px_ITEM_NAME;
    --Do something with l_filerow
    --e.g. insert into my_Table (filename, mime_Type, file_contents) values (l_filerow.filename, l_filerow.mime_Type, l_filerow.blob_content);

This could be in a validation or page process. Unless you want your files to linger in wwv_flow_files, you also need to be sure to delete the files, after a process/validation and also being sure to delete them it if any exceptions occurred.

New behaviour

In APEX 5, you still have the two options, but WWV_FLOW_FILES has changed to: APEX_APPLICATION_TEMP_FILES.

A neat new feature is the option to purge the file (at). This gives us two options:

  1. End of session
  2. End of request
So no more worrying about deleting that row yourself! You will see that if you specify `End of request` after all page processing, the file will no long live in that table/view.

There are also a reduced set of columns in APEX_APPLICATION_TEMP_FILES:

  • NAME
  • ID
Making the following unavailable (that are in wwv_flow_files):
  • NAME
  • ID

wwv_flow_files is still around, it just means that uploads in your systems will no longer be going there. 

note: Any existing applications migrated that reference WWV_FLOW_FILES will still be using that, with a note that it is deprecated. After you change it and save it, it will be gone as an option.

This is also covered in the (currently beta) release notes:

File Browse Storage Type, Table WWV_FLOW_FILES - Any select, update or delete operations on WWV_FLOW_FILES should be changed to using the APEX_APPLICATION_TEMP_FILES table. Deletes are no longer necessary, as the file will automatically be purged after the request or when the session is purged.

disclaimer: This is based on the current early adopter (3) instance of apex

Monday, 2 February 2015

APEX 5 supporting file enhancements

Existing behaviour

In APEX 4.2, files can be uploading through shared components, and can either be associated with an application or workspace, and can be stored in either:

  1. Cascading Style Sheets
  2. Images
  3. Static Files

These files can generally be found in the view: APEX_WORKSPACE_FILES

Files are then referenced throughout the application by: #APP_IMAGES#<file_name> or #WORKSPACE_IMAGES#<file_name>

Files can also be included in application exports, by creating an installation script for these files (to ensure they are available when deployed):

Select Create Scripts to Install Files under tasks when creating a new installation script:

Then it's just a matter of selecting which files to include in the installation script

New behaviour

In the current form, this has changed to divide files up into application files and workspace files:

This file uploader is not a multi-file uploader control, but it now has support for uploading a zip, which can be extracted after upload - a nice way to upload multiple files in one hit. A word of warning, if you attempt to upload a file in the same path that already exists, it will simply be replaced, without warning.

These files would be referenced in two new views: APEX_APPLICATION_STATIC_FILES AND APEX_WORKSPACE_STATIC_FILES

This substitution string remains the same: #APP_IMAGES# and #WORKSPACE_IMAGES# accordingly.

Another new feature, is that relative paths are supported. So e.g you could have a zip with images, css directories, after you upload the zip, you can refer to files like so: #APP_IMAGES#css/<css file_name>

The docs say it not necessary to create that supporting object script any more for static files and indeed that seems to be the case - they are automatically included in application exports - with no apparent ability to disable this.

disclaimer: This is based on the current early adopter (3) instance of apex

Monday, 26 January 2015

Up and running with the node Oracle driver on Ubuntu

Dan McGhan did a great video guide on getting set up with Node.js and the Oracle driver, which you can check here:

Installing Node

As an alternative method to downloading the node.js tarball as per the video, you can just install using your package manager:

sudo apt-get install nodejs npm

Once installed, you can access the node interpreter with the command `nodejs`:

Post install, you should set the NODE_PATH environment variable so that nodejs can find any modules you install:

export NODE_PATH=/usr/local/lib/node_modules

This is where the node-oracledb driver gets installed to.

Installing the Oracle driver

wget -O
cd node-oracledb-master
sudo npm install -g

Once all that is done, you can get rid of those files:

cd ~
rm -rf node-oracledb-master

Then, we can get to the example files:

cd $NODE_PATH/oracledb/examples

First, update the dbConfig.js with appropriate connection properties, then test with:

nodejs connect.js

You should see: Connection was successful!

Shell script

You can then make a shell script on this. E.g. you may have a job logging database that you want to notify users to whenever they enter a console (assuming the username matches):

#!/usr/bin/env nodejs
var oracledb = require('oracledb');
var dbConfig = require('./dbconfig.js');

    user          : dbConfig.user,
    password      : dbConfig.password,
    connectString : dbConfig.connectString
  function(err, connection)
    if (err) {
      "SELECT count(1) "
    + "FROM jobs "
    + " WHERE status = :jobStatus"
    + " and lower(assigned) = lower(:userName)",
      ["Open", process.env.USER],
      function(err, result)
        if (err) {
        rowCount = result.rows[0][0];
        console.log("You have " + rowCount + " jobs awaiting your attention.")

Make it executable

chmod +x
You have 1 jobs awaiting your attention.

Wednesday, 26 November 2014

Logging in without user intervention

For this, I set up an authentication plugin and behind the scenes it's using a web service to check if the client is valid or not.

To automate the login process, you need to make use of the sentry function that gets called every time you access a page in the application. This is what the item help says:

Enter the name of the PL/SQL function the plug-in can use to perform the session sentry verification. It can reference a function of the anonymous PL/SQL code block, a package function or a stand alone function in the database. For example:


When referencing a database PL/SQL package or stand alone function, you can use the #OWNER# substitution string to reference the parsing schema of the current application. For example:


There is however one caveat with this. It doesn't run on whatever you have defined as the login page (User interface attributes --> Desktop --> Login URL) - which is reasonable enough.

So I left that attribute alone, and created 2 pages that will aid in the process:
  1. Logout page
  2. Invalid session page
The idea being, when the user logs out, they will be taken to the logout page. I got this idea from the Apex Builder actually - with a button to return to the system. I think the best page to return to is the page specified in HOME_LINK. Using the substitution &HOME_LINK. doesn't seem to work, since by default it includes a substitution string for &APP_ID. which doesn't seem to be evaluated. I think a good workaround is to create a new substitution string: &HOME_PAGE. and use that in a redirect to page button action (then update your home link to reference that substitution string).

When the user attempt to access a page and authentication/sentry fails, they will be taken to the invalid login page.

We have 2 associated attributes in the authentication plugin:

  1. Post logout function
  2. Invalid session function
These are really just to determine which page to go to next - which line up with the 2 pages I created earlier on. 

When we logout, we set the page to go to, but because the session is also invalid, it will get fired as well. Since we don't want to get redirected to the invalid session page when we log out, we just need one additional check in that function to make sure the current page isn't the logout page. Here is how I implemented that:

function invalid_session (
    p_authentication in apex_plugin.t_authentication,
    p_plugin         in apex_plugin.t_plugin )
    return apex_plugin.t_authentication_inval_result
    l_invalid_Result apex_plugin.t_authentication_inval_result;
    if apex_application.g_flow_step_id != LOGOUT_PAGE
        l_invalid_result.redirect_url := 
            || apex_application.g_Flow_id
            || ':'
            || INVALID_SESSION_PAGE ;        
    end if;

    return l_invalid_Result;
END invalid_session;

Nothing fancy about the logout function:

function logout (
    p_authentication in apex_plugin.t_authentication,
    p_plugin         in apex_plugin.t_plugin )
    return apex_plugin.t_authentication_logout_result
    l_logout_result apex_plugin.t_authentication_logout_result;

    l_logout_result.redirect_url := 
            || apex_application.g_Flow_id
            || ':'
            || LOGOUT_PAGE;         

    return l_logout_Result;

END logout;

Then for the actual sentry function. I return true if it's one of the pages created above (Logout or Invalid Session page). Then, where necessary, I call the authentication function to get the appropriate result. Since the parameter p_authentication doesn't contain the username attribute on invalid sessions, I declare a local variable of type apex_plugin.t_authentication then copy all the values from p_parameter except p_username - which I'm retrieving in my own function.                     :=;                   :=;
l_auth_attrs.invalid_session_url    := p_authentication.invalid_session_url;
l_auth_attrs.logout_url             := p_authentication.logout_url;
l_auth_attrs.plsql_code             := p_authentication.plsql_code;
l_auth_attrs.session_id             := p_authentication.session_id;
l_auth_attrs.username               := upper(get_user_name());
l_auth_attrs.attribute_01           := p_authentication.attribute_01;
l_auth_attrs.attribute_02           := p_authentication.attribute_02;
l_auth_attrs.attribute_03           := p_authentication.attribute_03;
l_auth_attrs.attribute_04           := p_authentication.attribute_04;
l_auth_attrs.attribute_05           := p_authentication.attribute_05;
l_auth_attrs.attribute_06           := p_authentication.attribute_06;
l_auth_attrs.attribute_07           := p_authentication.attribute_07;
l_auth_attrs.attribute_08           := p_authentication.attribute_08;
l_auth_attrs.attribute_09           := p_authentication.attribute_09;
l_auth_attrs.attribute_10           := p_authentication.attribute_10;
l_auth_attrs.attribute_11           := p_authentication.attribute_11;
l_auth_attrs.attribute_12           := p_authentication.attribute_12;
l_auth_attrs.attribute_13           := p_authentication.attribute_13;
l_auth_attrs.attribute_14           := p_authentication.attribute_14;
l_auth_attrs.attribute_15           := p_authentication.attribute_15;

l_auth_result := 
        p_authentication => l_auth_attrs
      , p_plugin => p_plugin
      , p_password => NULL);
if l_auth_result.is_authenticated

        p_user => l_auth_attrs.username
      , p_session_id => apex_custom_auth.get_next_session_id  

    l_sentry_result.is_valid := l_auth_result.is_authenticated;

    l_sentry_result.is_valid := false;
end if;

Well that about covers it! Good luck with your authentication adventures!