Tuesday 30 December 2014

Apex - end of 2014

What will 2015 bring?

APEX 5 is around the corner

Hi all, after interesting and dynamic 2014 a year has come to an end so I wanted to write my final post. 

All in all this has been dramatic year for APEX developers having 4.2.6 patch out and initial demo versions of release 5.0 announced we had plenty of thing to keeps us busy. Interestingly it has been 10 full year since APEX as technology started and this year brought the most to us so far. I do remember in 2008 main discussion on Oracle User Group Conference among APEX developers was on will it live long enough and will it be able to fully replace Oracle Forms. I thing the answer now is clear and glad to see future is looking really promising.

Even better is that Oracle is preparing another major release that should be the best one so far taking it again to another level which are all great news for us in APEX community. 

Word is that we can expect it to be release in first Quoter of 2015 so fingers crossed everything goes as planned. 

Personally I look forward even more as there are some new projects planned so there will be opportunity to work with latest version really testing it out and learning all new cool features that Oracle prepares for us. Similar to this year all things worth sharing will be posted here so don't you worry :)

From my blog experience perspective it is really nice to see that posts had been and are visited on daily bases which gives me even more confidence that this was a good decision. I can not thank enough all of you who made the effort, hopefully you found some interesting things for you. 

Blog future, I will continue to post as often as I can hopefully fueled with my latest project experience plus sharing some interesting news from APEX community space. It good to see our numbers are growing and "Thumbs up" to Oracle who are doing a really great job further developing all aspects of APEX.

To finish my last post all left to do is to wish you a Merry Christmas and Happy New Year to you and your families. 


Thursday 20 November 2014

Custom export of APEX Classic reports

APEX 4.2.5 - Custom HTML table export

Export your report table after you have modified it with JQuery

Recently I have had a request to change sum totals in my classic report table and then include these newly created figures in excel export.

Problem was that APEX build in download function was not including these figures in standardised excel report.

After some brain storming these were my thoughts:
1. Is there a way I can refresh report to use standard APEX functionality to retrieve new figures? No or at least not that I know of.
2. Can I use some other technology to do the same? Seemed to me to be fairly common question for all web base apps. Exporting any HTML table in excel this surely must be doable.
3.  Is there any APEX plugin that can do the same? Probably but similar to BI Pub reports it will need to generate report from specifically rendered query which is not exactly what I was after.

Just for the challenge itself I decided to explore option 2 as it seemed the most simple way to go with.

Solution can be applied to any HTML table but my code will need to be adjusted as I have specifically used some APEX internal tags in my jQuery selectors. So please be aware of that.

In my example APEX theme is Blue responsive #25. I have been looking on line for similar solution and this is what I have so far.

Code might seem complicated but there is only few important lines of code that are APEX specific.

One note here - there is a difference how IE handles this and how other browsers render this functionality.  Reason behind it E doesn't support some of HTML tags and properties used such as URL download property which other browsers seem to support.

Please note I generated this for my project needs but before you decide to use it make sure you go through it and understand how this is achieved.

Let's start by looking at these functions separately.

Firefox and non IE browsers function:
var tableToExcel = (function() {
       //DOWNLOAD FOR browsers not IE
        var uri = 'data:application/vnd.ms-excel;base64,', template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>', base64 = function(
                s) {
            return window.btoa(unescape(encodeURIComponent(s)))
        }, format = function(s, c) {
            return s.replace(/{(\w+)}/g, function(m, p) {
                return c[p];
       return function(table, name, filename) {
            if (!table.nodeType)
         //APEX specific class used here
                table = $("#"+ table + " .uReportStandard")[0]; //document.getElementById(table);
            var cln=table.cloneNode(true);
            var paras = cln.getElementsByClassName('ignore');

            while(paras[0]) {
            var ctx = {
                worksheet : name || 'Worksheet',
                table : cln.innerHTML
          //THIS renders some random file name
          //window.location.href = uri + base64(format(template, ctx))      
         // EXPLANATION for this part to follow          
          $(".uReportDownloadLinks").after("<a id='Mydlink' style=\"display:none;\"></a>");
          document.getElementById("Mydlink").href = uri + base64(format(template, ctx));
          document.getElementById("Mydlink").download = filename;
Function receives three parameters which represent TABLE_ID,  WORKSHEET name and FILE name. Some parts of function above can be found online but trick that I used is after line commented with //EXPLANATION...... that is specific.

Idea was to replace original download link that you get on APEX classic reports with your own. To be able to do this I changed the original link by adding an ID to it before this function can get to it.

Link that is under my Download is a simple JavaScript call
exportHTML('#report_REGION_ID#','#Report name#');
Of course replacing values with a real ID and file name.
On the other side this is IE specific function:
function exportIEToExcel(tableID, filename, sheetname) {
    //WORKS with IE 11
    // Declare the variables
    var Excel, Book;

    // Create the Excel application object.
    Excel = new ActiveXObject("Excel.Application");

    // Make Excel visible.
    Excel.Visible = true;

    // Create a new work book.
    Book = Excel.Workbooks.Add();


    //new sheet
    Sheet = Book.Worksheets.Add();

    //sheet name
    Sheet.Name = sheetname;       

    //LOOP TABLE ROWS and fill excell sheet.
    var Mytable = $("#"+ tableID );
    //APEX specific selectors   
    Mytable.find('.uReportStandard tr').each(function (i) {
    //for each header
    var $ths = $(this).find('th');
    $ths.each(function (k) {
           Book.ActiveSheet.Cells(i+1,k+1).Value = $(this).html(); 
        });//end headers   

    //for each column             
    var $tds = $(this).find('td');
    $tds.each(function (j) {
        //alert('Row ' + (i + 1) + 'Cell ' + (j + 1)+ ' HMTL:' + $(this).html());
        //write into excell cells
        Book.ActiveSheet.Cells(i+1,j+1).Value = $(this).text(); 
        });//end column                       
    });            //end rows         

        idTmr = window.setInterval("Cleanup();",1000);             
 } //end exportIEToExcel

function Cleanup() {
   //“CollectGarbage” fire JScript's garbage collection to release the reference  to Excel
} // end Cleanup()

You will notice it uses completely different logic than our non IE function. Same as the first one function example can be found online but my addition is APEX related selectors and Cleanup function which makes sure that there is no processes left hanging in your Task Manager after user has finished downloading file.

All there is to do before you call these functions is to add this into your page either as Dynamic Action or on page load that will manage already mentioned trick about the report link.

$(".uReportDownloadLinks a").attr("id","my_anchor"); //add ID to old link link
  var dLink = 'report_REVENUE';
  var filename = 'Revenue';
  $( "#my_anchor" ).attr( "href", "javascript:exportHTML('"+dLink+ "','" + filename+ "');");

 If you have a look with Firebug end result looks like:
<div class="uReportDownloadLinks">

Only known problem is that currently it doesn't support report pagination but I am sure someone will think of the way on how to adjust this code and hopefully let me know about it. :D

Till then all reports without pagination can be downloaded using above approach.

Hope this helps.


Wednesday 19 November 2014

BI Publisher Desktop version and report template issues

Oracle BI Publisher Desktop and APEX reporting

BI Publisher Desktop template "problems" when running in APEX

Recently I had a request from a client to update some of existing reports that run in their APEX 4.2.0 version. As I have done numerous applications that interact with BI Publisher there were no reasons why I should not be able to assist with this request. 

Bearing in mind that I am no BI Publisher expert but have done some BI reporting this is a problem I ended up with. After deploying my changed template all new regions had characters in bold font. No mater how much I tried I couldn't reproduce this in BI Pub Desktop and my template.

Before I go into it just a note that BI Publisher was used here as APEX print server. There are several other posts around that describe all options how you can integrate the two so I will not cover this in my today's post.

Here is an example of my problem, this was my word template:
where all seemed fine some text is bolded some is not. Once deployed under Shared components -> Reports resulted in:
which was surprising as I never encountered this problem before when dealing with BI reports in APEX. 

It took me some time to notice where problem might be and without help of my BI colleague I probably would still be looking at it.

First thing we agreed that we should better check for BI Publisher version this report is running on. Why? 

There is this funny story with BI publisher Desktop Add-ons for MS Word that not all versions are compatible. We both have experienced this before but since change I did in above template was really a minor one (adding 1 table, fields and bolding few of them) it never occur to me this might be the problem.

To verify this all that was needed (after Instance settings provided me the details and the link to the server) was try to open my BI Publisher and check out the version installed.  It was vs
Now question was are Oracle BI Publisher Desktop and Oracle BI Publisher version mutually supported? And I am still am not for sure about this one. :)
Eventually even though we tried everything with an older version of BI Pub Desktop 10.x it hasn't sorted this problem so we turned back to my original template.

At the end it came down to, all problems were caused by this new table that was holding my new data. For some reason BI Publisher printer didn't like the formatting of it (still not sure why) so I had to recreate it from scratch using preexisting table as base for it. After lots of copy and pasting things started working again and report finally looked like it was expected.

This probably isn't directly related to APEX but it may help. I learned my lesson. :)

Till my next post,

Sunday 5 October 2014

APEX and temporary file table

Is saving files in www_flow_files  a good idea?

Where to store your temporary APEX files and www_flow_files issue

How many times did you have the need to store you files temporary in your APEX application? How many times have I heard try not to use www_flow_files table for storing your temporary files. 

There are two reasons: one being security thing and second one is files in local table are easier to manage. 

But does this mean that I have learned my lesson and listen to these tips? Of course not.

Situation: In the company we have an application that project managers use to retrieve some BI Publisher data.  How this can be achieved is a separate subject and I will not go into to many details. 

Bottom line was all of a sudden after two years in production only one combination of parameters for a single client was causing reports to fail. My first suspicion was that it must be data related. 

As everything led me to believe that is to be the only reason. Fortunately I always implement application that have a log capability and this one was no exception. This proved yet again to be crucial part in my analysis while I was trying to convince myself that it had to be data related error.


As odd as it may sound initially this unique constraint error didn't ring a bell but after a while it came to me that it must be related to famous www_flow_files view and ability to write data in there. 

Problem turned to be that some filenames and files we not being properly cleaned up in a run time and for this reason were left in this table which at the end caused my reporting procedure to fail due to unique constraint on FILENAME column.Thankfully this time workaround was easy. 

Log in as SYS and run this query:  

select * from  wwv_flow_file_objects$

As a result you will get all files that were left at some stage in temporary table but never were deleted. Once I deleted these there were no other problems with my reporting. 

Lesson to learn:
0. Always implement logging methods as they will save you heaps of time
1. Avoid temporary tables
2. If you have to use them - make sure you have mechanisms in place to properly manage deletion of the files at the run time
3. Make sure that you have a unique filenames no matter how many time reports are being called

 Hope this helps.