APEX_IR.GET_REPORT Limitations & Wishlist

Luckily nowadays we as APEX developers have access to APEX_IR.GET_REPORT which gives us back the current query of an interactive report.

With that we can actually build really nice plugins enhancing the functionality.

However one thing you have to be aware of is that it will always give you the query of the "Report" view, so if you want to get the query behind the "Group By" view it's back to rebuilding the query manually.

Talking to Patrick Wolf during DOAG 2014 he asked me to write a blog post about it and showcase the behavior.

Therefore I have created a small demo on apexea.oracle.com using an interactive report and a dynamic action to see if the same behavior is also still in APEX 5.

The dynamic action gets the IR query using APEX_IR.GET_REPORT and also sets a read-only field to SYSDATE in order to show it actually fires on event "After Refresh".

You can check out the current behavior here.

So here's my wishlist for the APEX development team:

• Please give us the query also for the other view-types available for an interactive report.
• And if you want to supercharge the function give us an additional parameter for choosing whether we want the query for the currently displayed view-type or a specific one.

I imagine a possible function declaration like below:

FUNCTION get_report( p_page_id      IN NUMBER
                   , p_region_id    IN NUMBER
                   , p_report_id    IN NUMBER DEFAULT NULL
                   , p_view_mode_in IN VARCHAR2 DEFAULT NULL
                   )
  RETURN t_report;

So if you do not specify the fourth parameter you get the query for currently displayed view mode, similar to the third already available parameter.

Concatenating VARCHAR2 values into a CLOB

After an inspiring DOAG 2014 I was thinking about some performance improvements for my APEX IR XLSX Downloader.
The actual size of all the single files that need to be build isn't known beforehand but you have to plan for more than the maximum size of a VARCHAR2 variable so you need a CLOB.
However building a CLOB can be really slow if you treat it like a VARCHAR2 and just concat values together.
In addition if you are doing that multiple times you're actually asking for some performance trouble.

I remembered reading a blog post by Carsten Czarski a while ago and got the inspiration from there.
He also reviewed my initial code and helped me make it better.

Maybe you have similar tasks at hand and can benefit from the code.
How it works and how to use is enclosed in the code comments.

Update: Thanks to the comment by SSentinel below I could clean up the code.

/**
* Procedure concatenates a VARCHAR2 to a CLOB.
* It uses another VARCHAR2 as a buffer until it reaches 32767 characters.
* Then it flushes the current buffer to the CLOB and resets the buffer using
* the actual VARCHAR2 to add.
* Your final call needs to be done setting p_eof to TRUE in order to
* flush everything to the CLOB.
*
* @param p_clob        The CLOB buffer.
* @param p_vc_buffer   The intermediate VARCHAR2 buffer. (must be VARCHAR2(32767))
* @param p_vc_addition The VARCHAR2 value you want to append.
* @param p_eof         Indicates if complete buffer should be flushed to CLOB.
*/
PROCEDURE clob_vc_concat( p_clob IN OUT NOCOPY CLOB
                        , p_vc_buffer IN OUT NOCOPY VARCHAR2
                        , p_vc_addition IN VARCHAR2
                        , p_eof IN BOOLEAN DEFAULT FALSE
                        )
AS
BEGIN
  
  -- Standard Flow
  IF NVL(LENGTHB(p_vc_buffer), 0) + NVL(LENGTHB(p_vc_addition), 0) < 32767 THEN
    p_vc_buffer := p_vc_buffer || p_vc_addition;
  ELSE
    IF p_clob IS NULL THEN
      dbms_lob.createtemporary(p_clob, TRUE);
    END IF;
    dbms_lob.writeappend(p_clob, length(p_vc_buffer), p_vc_buffer);
    p_vc_buffer := p_vc_addition;
  END IF;
  
  -- Full Flush requested
  IF p_eof THEN
    IF p_clob IS NULL THEN
       p_clob := p_vc_buffer;
    ELSE
      dbms_lob.writeappend(p_clob, length(p_vc_buffer), p_vc_buffer);
    END IF;
    p_vc_buffer := NULL;
  END IF;

END clob_vc_concat;

APEX IR XLSX Downloader V1.2.2

I have just published release 1.2.2 of my APEX IR XLX Downloader.
While attending the DOAG 2014 Conference Dietmar Aust was trying out my package and guess what...
He ran into a bug, so thanks Dietmar for reporting this.

Bugfix

• Columns without help text break the download.

As always the source code can be found on GitHub.

ZIP files for all my GitHub projects can now be found on my GitHub Page.
For a small demo visit my Demo Application on apex.oracle.com.

APEX IR XLSX Downloader V1.2.1

Well, just three days ago I published release 1.2.0.
In the meantime I've been working on updating the README to give you all a better start.

This time there are no bugfixes included as it's only documentation.

Documentation Changes

• Added list of features
• Added current limitations
• Updated "How to Use" section

As always the source code can be found on GitHub.

ZIP files for all my GitHub projects can now be found on my GitHub Page.
For a small demo visit my Demo Application on apex.oracle.com.

APEX IR XLSX Downloader V1.2.0

I have just published a new minor release of my APEX IR XLSX Downloader.
This time I have fixed a bug regarding control breaks and added one enhancement to column headers.

Enhancement

• Help text as defined on IR column is now added as a comment to the column header in the spreadsheet.

Bugfix

• Control breaks with just number or date column are now working.

As always the source code can be found on GitHub.

ZIP file is available in my demo application.