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:


declare
    l_region_id apex_application_page_regions.region_id%type;
begin

    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
    
    apex_ir.reset_report(
        p_page_id => :APP_PAGE_ID
      , p_region_id => l_region_id
      , p_report_id => NULL
    );
    
end;

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

APEX 5 API changes

Based on the current beta API docs: https://docs.oracle.com/cd/E59726_01/doc.50/e39149/toc.htm, here is what's changed.

APEX_APPLICATION_INSTALL
  • Function GET_AUTO_INSTALL_SUP_OBJ added 
  • Procedure SET_AUTO_INSTALL_SUP_OBJ added

APEX_CUSTOM_AUTH
  • LOGOUT procedure deprecated

APEX_ESCAPE
  • Function JSON added
  • Function REGEXP added

APEX_INSTANCE_ADMIN
  • Procedure CREATE_SCHEMA_EXCEPTION added
  • Procedure FREE_WORKSPACE_APP_IDS added
  • Function GET_WORKSPACE_PARAMETER added
  • Procedure REMOVE_SCHEMA_EXCEPTION added
  • Procedure REMOVE_SCHEMA_EXCEPTIONS added
  • Procedure REMOVE_WORKSPACE_EXCEPTIONS added
  • Procedure RESERVE_WORKSPACE_APP_IDS added
  • Procedure RESTRICT_SCHEMA added
  • Procedure SET_WORKSPACE_PARAMETER added
  • Procedure UNRESTRICT_SCHEMA added

APEX_IR
  • Procedure CHANGE_SUBSCRIPTION_EMAIL added
  • Procedure CHANGE_REPORT_OWNER added

APEX_LDAP
  • Function SEARCH added

APEX_PLUGIN_UTIL
  • Function GET_ATTRIBUTE_AS_NUMBER added

APEX_UTIL
  • 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

APEX_WEB_SERVICE
  • Function MAKE_REST_REQUEST_B added

Packages
  • 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:
  • WWV_FLOW_FILES
  • APEX_APPLICATION_FILES
  • HTMLDB_APPLICATION_FILES
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:

declare
    l_filerow apex_application_files%rowtype;
begin

    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);
end;

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:

  • CREATED_ON
  • MIME_TYPE
  • FILENAME
  • NAME
  • APPLICATION_ID
  • ID
  • BLOB_CONTENT
Making the following unavailable (that are in wwv_flow_files):
  • LANGUAGE
  • CONTENT_TYPE
  • LAST_UPDATED
  • UPDATED_ON
  • UPDATED_BY
  • CREATED_ON
  • CREATED_BY
  • MIME_TYPE
  • FILENAME
  • 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: http://docs.oracle.com/cd/E59726_01/doc.50/e39143/toc.htm#BAJJDJBA

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