Tuesday, 30 June 2015

Kscope 2015 - Summary

All key things on Oracle APEX from this year's event

Is Oracle finally turning in right direction?

Now that I am back I wanted to reflect back to major things that I brought with me that I would like to share with you.

Fist of all I have to mention Joel R. Kallman speech on Application Express Symposium that took place on Sunday 21th. 

Why I want to refer back to it is because it in short summarizes what we all knew and as it was nice for a change to hear a words from THE Oracle APEX manager what the core problem has been so far on internal scale.

One hashtag sums it all up for me #letsreckthistogether

Basically Joel gave us his honest opinion/view on where things were within Oracle itself and what this means for the future of us in APEX community. 

Being based in NZ I can totally connect to the statement that so far Oracle hasn't been doing much to help us win JAVA/.NET or APEX debate. 

By no means here I want to take away from a great work of Oracle APEX developing team but more to its sales managers who so far haven't had APEX in their plans and it was all down to because there was no licenses involved. 

Excellent news for us all is that Oracle cloud is now changing the way Oracle perceive Application Express and its great features. Let's just hope it will bring something to a table to help us wreck this in upcoming year. Finally it sounded like we realize where the problem was/is and now we are here to do whatever needed to make it work. Refreshing and by all means promising.

Biggest value to "fight" the JAVA/.Net wave is us as a community. We are playing the vital role making this break through and hence I totally agree with Joel we all have do our bit to change the overall picture. This was one of the main reasons why I started blogging. APEX market in NZ is almost non-existent very few companies use it and to find appropriate role is an impossible task. On the other side this should be no reason for me not to give it try and connect with people around such a cool tool.

His initial presentation was followed by Patrick's, Shakeeb's, Marc's and David's presentations about great new features brought to us with APEX 5.0 release. I will make sure to blog into more details of sessions that I found most interesting hoping to highlight things I learned while being there. 

Personally it was nice for a change to be a part of a real world having the ability to talk to people from the field, gathering what happens out there what interests others and where do I fit in into all of it. From all perspectives it really means a lot and it was definitely worth the hassle. :)


Tuesday, 9 June 2015

APEX 4.2.6 and g_print_success_message issue

Apex_application.g_print_success_message limitations

Hope this saves you some time

The other day I was working on one of our report applications and encountered the weirdest problem ever. At least it turned out to be and just because it took me a while to understand where the problem is I decided to blog about it.

The story goes like this, users set up a parameters run a report and they receive a success message as the end result. All was fine until I was running a loop for 40+ reports then I started getting some weird error messages at the end of the process. Report files would generate perfectly fine but I would still get an error on my screen.

After whole lot of testing I managed to trace it down to g_print_success_message that was used within one of my packages.

To demonstrate this I created a simple page with a button that executes a simple process. 

Process on this page is nothing but
apex_application.g_print_success_message :='Successfully completed';
Now we come to the point to what story is about. In my package what I would do is 
apex_application.g_print_success_message := apex_application.g_print_success_message|| '<br> Successfully completed for file:' || Filename_x;
concatenate messages so that user would be presented with list of all successfully completed report filenames. 

As mentioned previously I had no reason to believe this would ever fail. Boy was I wrong. 

For demo purposes try doing something like this, change your g_print_success_ message to include a long text such as this one below. 

By long I mean more than 2890 rows at least this is a figure I managed to get to. 

apex_application.g_print_success_message :='<br> Your report <font color="blue">BIS_01052015-31052015_BIS0002.RTF </font> has been successfully saved.<br> Your report <font color="blue">BIS_01052015-31052015_BIS0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">CIVO_01052015-31052015_CIVO0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">MANA_01052015-31052015_MANA0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">ASCO_01052015-31052015_ASCO001.RTF </font> has been successfully saved.<br> Your report <font color="blue">ASCO_01052015-31052015_ASCO002.RTF </font> has been successfully saved.<br> Your report <font color="blue">STOC_01052015-31052015_STOC0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">SAH_01052015-31052015_SAH0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">TBH_01052015-31052015_TBH0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">LLE_01052015-31052015_LLE0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">STVM_01052015-31052015_STVM0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">AMPC_01052015-31052015_AMP0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">NETT_01052015-31052015_NETT0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">BKL_01052015-31052015_BKL0002.RTF </font> has been successfully saved.<br> Your report <font color="blue">GPC_01052015-31052015_GPC0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">GSL_01052015-31052015_GSL0002.RTF </font> has been successfully saved.<br> Your report <font color="blue">WOTI_01052015-31052015_WOTI0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">FOXT_01052015-31052015_FOXT0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">LNS_01052015-31052015_LNS0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">SPBS_01052015-31052015_SPBS0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">SPBS_01052015-31052015_SPBS0002.RTF </font> has been successfully saved.<br> Your report <font color="blue">SPBS_01052015-31052015_SPBS0003.RTF </font> has been successfully saved.<br> Your report <font color="blue">ICMS_01052015-31052015_ICMS0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">APTM_01052015-31052015_APTM0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">APTM_01052015-31052015_APTM0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">APTM_01052015-31052015_APTM0001.RTF </font> has been successfully saved.<br> Your report <font color="blue">APTM_01052015-31052015_APTM0001.RTF </font>  has been  successfully!';
Please notice here this exclamation mark at the end. If you try to run this you now get: 
But where does this come from? Try running without this exclamation mark at the end and your page would render perfectly fine. Understandably you would argue what is the whole point in displaying something long as this to a user, which is a fair point, but I never expected to get an error for this in a first place.

Obviously after long day of thinking and debugging your "brilliant" code you come to a conclusion that there is nothing wrong with it and that there must be some character conversion issue taking place within the core APEX engine. 

If you had a look at definition of g_print_success_message in APEX_040200. wwv_flow package you would find something like this:

Why would this then fail when it can be 4000 bytes? I am still not sure but it is something you should be aware of.


Friday, 5 June 2015

Page processing after wpg_docload download_file call

Problems with page refresh when using wpg_docload download_file function


APEX 4.2.6 workaround with APEX_UTIL.SET_PREFERENCE

Situation a standard form with report parameters

where user have an ability once reports are run result is either saved on shared Network Drive or it is saved temporarily in the custom file table and presented to them on the screen for individual download in a separate page region. See image below. 

Idea was to be able to run report for an account manager that can have multiple Clients and for each of these clients man can have multiple contracts. Sort of Select All Clients and All Contracts for an account manager version of report with download option.

With only one exception that if you select only one client and only one specific contract users would  get document downloaded in a browser in a window familiar to all of us.

All good so basic idea is there and all was working fine except for this one case when individual contract is selected. Let me try to explain why.
When a Run button (that submits the page) would be processed above region would be normally refreshed and users would get the latest report results. But problem with download function for individual contract was in this code:
owa_util.mime_header( 'application/rtf', FALSE );
htp.p('Content-length: ' || t_len);
htp.p('Content-Disposition: attachment; filename="' || p_filename || '"');
apex_application.g_unrecoverable_error := true;
where apex_application.g_unrecoverable_error would prevent APEX to refresh the page as it is basically stopping APEX engine and users would be left with older version of report in their download region while they would still get to download the latest results. 

To avoid this problem I decided not to use g_unrecoverable_error and to use APEX SET_PREFERENCE with JQuery. More about USER PREFERENCE functionality you can find in any APEX guide and I will not go into details of it. Basically it can be used for temporarily setting values for current user session and is ideal as a global variable for all non standard values within your applications.

How to do this? 

Piece of code above was replaced with this:
 p_preference => 'download_refresh',
 p_value      => 'YES');
Create a page item in my case it is called P2_REFRESH and Before header process sets the value of your USER preference. 
 l_history_days    VARCHAR2(255);
 IF nvl(:P2_REFRESH, 'cc') != 'YES' THEN
 :P2_REFRESH := 'YES';
 END If;
with its condition set to 
 l_history_days    VARCHAR2(255);
 l_history_days := APEX_UTIL.GET_PREFERENCE(p_preference => 'download_refresh');
IF nvl(:P2_REFRESH, 'cc') != 'NOTSET' and l_history_days = 'YES' THEN
  return true;
END if;
  return false;
Then create a Dynamic action with these settings:

Where JavaScript code is

and PL/SQL part of Dynamic action is:

Key thing being that JavaScript where after page is submitted and ready for download we trigger a click on Download link using a JQuery. Similar as if user clicked them self.

Maybe not the cleanest option but hopefully it works for you as well and maybe it will save somebodies time.