APEX IR XLSX Downloader V1.2.3

I have just published Release 1.2.3 of my APEX IR XLX Downloader.

André Meier had a look at my package after DOAG 2014 and pointed out some hick-ups he encountered while using it.
He also made a big effort to bring some proper code formatting and explicit conversions into the XLSX_BUILDER_PKG which is one of the libraries used.

A huge thank you to André, as many of the changes are based on his contribution.

Changes

• XLSX_BUILDER_PKG
• Explicit Conversions
• Code Formatting
• Better CLOB building using VARCHAR2 buffer (as shown in a previous post)
• APEXIR_XLSX_PKG
• If splitting report_columns string would produce a NULL index this is now taken care of and a warning is send to the APEX debug messages.

As always the source code can be found in the GitHub Repository.

ZIP files for all my GitHub projects can now be found on my GitHub Page.

The new demo application is currently under development and will be published soon.
I'm currently creating some sort of packaged application which will better showcase all the different options available.

Stay tuned for more to come.

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.

APEX IR XLSX Downloader V1.1.3

I have just published a new bugfix release of my APEX IR XLSX Downloader.
This time I had to fix some bugs regarding aggregations.

Bugfixes

• Aggregation values are now always taken from the correct SQL columns.
• Columns without aggregations defined no longer break the formatting on rows with aggregation values.

As always the source code can be found on GitHub.

ZIP file is available in my demo application.

APEX IR XLSX Downloader V1.1.2

Just a week ago I published a release which supports getting the  GROUP BY report from an IR.
Like most new functionality this one isn't bug free either.
Therefore I have just published release 1.1.2 of my APEX IR XLSX Downloader.

Bugfixes:

• Package didn't recognize the special aggregate functions like COUNT_DISTINCT, RATIO_TO_REPORT_SUM and RATIO_TO_REPORT_COUNT.

Added Functionality:

• Internal function to convert column display order into column identifier.
  This will support lifting the column restrictions on GROUP BY reports in the future.

Source code can be found on GitHub.

Download available in my demo application.

Enjoy. ;-)

APEX IR XLSX Downloader - New Release available

Release 1.1.1 of my APEX IR XLSX Downloader has just been published.

Bug Fixes:

• Highlights now also support the operators "in" and "not in".
• Highlights with no user defined name now properly display the highlight condition.
• Still existing filters on removed columns no longer result in an exception, they are simply ignored now.
• Columns used in link targets are now excluded if not visible on their own.

Added Functionality

• Added a wrapper procedure handling the whole download task.
  Parameter list is the same as the function returning the results.
• Initial support to receive the GROUP_BY report if defined.

Known Limitations

• Standard and GROUP_BY report cannot be exported together into a single file.
• Columns used in GROUP_BY also need to be present in the standard report, otherwise the columns are not included in the query generated by currently available APEX API.

You can look a the source code on GitHub.
ZIP Archive of package including dependencies can be downloaded from Demo Application.

APEX Dictionary - Who uses a column in filters?

I just had the requirement to find out if some users have filters defined on a certain column of an interactive report.
First try was looking at the saved reports view in the application builder, but that didn't help.
And again the APEX Dictionary Views to the rescue.
Following query can be used to check if a column is used in a column filter or highlight.

SELECT r.created_by
     , condition_type
     , cond.condition_name
     , condition_column_name
     , cond.condition_operator
     , cond.condition_expression
     , cond.condition_expression2
     , cond.condition_display
  FROM apex_application_page_ir_cond cond JOIN
       apex_application_page_ir_rpt r
         ON r.application_id = cond.application_id
        AND r.page_id = cond.page_id
        AND r.report_id = cond.report_id
 WHERE cond.application_id = :application_id
   AND cond.page_id = :page_id
   AND cond.condition_column_name = :column_name
;

Note: It will not find column usage in a row filter.

May the APEX Dictionary be with you!

APEX Dictionary - Comparing two Interactive Reports

Recently I again had the requirement to compare two Interactive Reports from different applications.
Using the power of the APEX Dictionary this can be done quite easily.

WITH prod AS
(
SELECT column_alias, application_id, report_label, help_text
  FROM apex_application_page_ir_col
 WHERE page_id = :page_id_prod
   AND application_id = :app_id_prod
), dev AS
(
SELECT column_alias, application_id, report_label, help_text
  FROM apex_application_page_ir_col
 WHERE page_id = :page_id_dev
   AND application_id = :app_id_dev
)
SELECT NVL(prod.column_alias, dev.column_alias) column_alias,
       prod.report_label prod_label,
       dev.report_label dev_label,
       CASE WHEN prod.column_alias IS NULL THEN 'Only DEV'
            WHEN dev.column_alias IS NULL THEN 'Only PROD'
            WHEN prod.report_label != dev.report_label 'Differs'
            ELSE NULL
       END status
  FROM prod FULL OUTER JOIN dev ON (prod.column_alias = dev.column_alias)
ORDER BY NVL(prod.column_alias, dev.column_alias)
;

I used "prod" and "dev" as identifiers for the different settings.
This time I was mainly looking for the report labels, but you can easily extend the query to include additional fields from the APEX_APPLICATION_PAGE_IR_COL view.

This has been in my stash for a while but I didn't publish it before.
Oliver Lemm just started a series of APEX Metadata related posts and made me think let's share this snippet.

May the APEX Dictionary be with you!

DOAG 2014 Conference - Program announced

The conference program for DOAG 2014 is available.
It's even harder than last year to plan because there are so many interesting sessions running in parallel.

However one session was easy to choose

APEX und OOXML, Office trifft Web

for one simple reason: I'm the presenter. ;-)

I think the time-slot Thursday 14:00 - 14:45 is also a pretty good one.

Means I have enough time to recover from the community event Wednesday evening...

See you all at DOAG 2014!

   Moritz

Speaking at DOAG 2014

For the first time I did apply to be a speaker at DOAG 2014.
Luckily my presentation has been selected so I get the honor to present at this amazing conference.
Are you interested in a brief overview what I will talk about?
Currently I have two APEX and OOXML (Open Office XML) projects which are finished or near to be finished.

APEX IR XLSX Download

The first project uses the really nice XLSX Builder Package created by Anton Scheffer which makes it easy to create Excel OOXML (XLSX) files using a SQL Query or PL/SQL calls.

I made some enhancements and build an additional package on top of it which takes the information from an Interactive Report and creates a spreadsheet representation of it.

Currently supported is only the "Report View" but there are plans to also support "Group By View" in a later release.

How to Use

The package has one main function which just needs the Region ID of the Interactive Report.

This function returns a record with the generated file and additional attributes you might need when offering the file for download.

You can get more information on GitHub where I store the source code.
ZIP Archive of package and dependencies can be downloaded from Demo Application.

PPTX Slide-Deck Generation

The second one is about creating PowerPoint slide-decks based on a template file.

I based my work on a package called OOXML_UTIL_PKG written by Morten Braten.

The package already allows to replace substitution patterns int XLSX, DOCX and PPTX files and many other things also, take a look at above link to learn more.

This time I didn't build a wrapper but used the ideas and build a package just to deal with PowerPoint files but enhancing the pattern replace to create multiple slides if more than one row of replacement strings is handed to the main function.

How to Use

You create your file with one slide using PowerPoint and replace the content you want dynamic with substitution strings. (e.g. #EMPNO#)

Afterwards you upload the template file as a BLOB into the database.

To generate the slide-deck just call the main function handing in the BLOB holding your template, a 1-dimensional array with your patterns and a two-dimensional array with the values replacing the patterns.

The source code is also stored on GitHub and ZIP archive available in Demo Application.

Can I see a demo?

If you haven't seen the hints above here it is again. :-)

Just head over to my Demo Application to see the packages in action and download ZIP archives for installation.

Demo Application Online

Finally my own demo application is online.
It will always be work-in-progress as I will be adding various features as they evolve.

At the moment it serves as a showcase for

• Creating XLSX files from Interactive Report.
• Generate PowerPoint slide-decks from a template slide.
• Code-Highlighting using CodeMirror.

The demo application is hosted at apex.oracle.com, click here to open it.

My next post provides some details.