Oracle APEX 5.0.1 is now available

First APEX 5 patch is now available for download.  

More details including installation notes and bug list can be found here. 

Thanks,

SLino

Oracle APEX 4.2.x and 5.0 environment info

Are we running APEX Listener or HTTPS or Embedded PL/SQL?

Something every APEX DBA (developers as well) should know! :)

Just a short post on how to recognize or find out what is your current APEX architecture. 

Many times it happens that you join the project at the end and there is absolutely no documentation about how and in what type of architecture your APEX instance is running, right? Then we are on the same page and in this post I will try to give some guidelines.

It can not be too difficult as there are only three options. 

It is either APEX Listener (now ORDS), HTTPS or Embedded PL/SQL (EPG). For most DEV environments it is most likely to be Embedded PL/SQL as this one is easier for developers/ DBAs to setup it up.

Most common example for EPG would be

http://www.myapexexample.com:8080/apex

as most often EPG runs on port 8080 but it doesn't have to be. 

Now on prod environments it becomes more interesting. 

Sometimes it is not easy to say for sure what we are using just by looking at your APEX URLs.

For example:

http://www.myapexexample.com:7001/ords/f?p=101

problem is that /ords/ can be reconfigured during the installation to be /apex/ or something else. Then you wouldn't be able to say ORDS or not.

HTTPS example would be 

https://apex.oracle.com/pls/apex/f?p=101

key part most of the times for recognizing it would be /pls/ part. But if you login into APEX workspace and select About option you would notice something different.

Meaning the above link even though it uses /pls/ in its syntax actually runs ORDS.

 

From my experience we found instances where we had clients with Web logic installed and partially configured but they were running as PL/SQL embedded. From DBA perspective it was very hard to determinate which version were we running from just by listening to the opened ports etc. First he thought it was Web logic and ORDS then it turned to be PL/SQL embedded as port 8080 was opened and used.

What we can do from APEX side (if we have access to APEX workspace as administrators/ developers) is to open About option that sits under main Administration tab and see what is says there.

4.2.x version

where you will get a basic idea where you are. Screens below are examples of different APEX versions and values I got for some of my environments.

APEX 4.2.0 running embedded PL/SQL

APEX 4.2.6 running ORDS

 

 APEX 5.0 running ORDS (option is hidden under top right ? menu)

 

Unfortunately I didn't have any HTTPS example where I could have copied the same but there is a work around.

If for any reason you can not find this link you can retrieve the same by using:

begin
OWA_UTIL.print_cgi_env;
end;

in your APEX SQL Workshop. 

What this procedure will do is display similar details to the one you can see under About option. 

Problem with this is that I was unable to run this in TOAD or SQL Developer ether as schema owner or SYS as I was getting exception raised.

I will only include EPG version on APEX 4.2.0 as an example:

PLSQL_GATEWAY = WebDb
GATEWAY_IVERSION = 2
SERVER_SOFTWARE = Oracle Embedded PL/SQL Gateway/11.2.0.3.0
GATEWAY_INTERFACE = CGI/1.1
SERVER_PORT = 8080
SERVER_NAME = XDB HTTP Server

you can clearly see the similarities to About screen.

To simplify the output we can use

select owa_util.get_cgi_env( 'SERVER_SOFTWARE' ) from dual;

which would in HTTPS example give back

Oracle-Application-Server-10g/10.1.2.2.0 Oracle-HTTP-Server

Hopefully this will give you enough details to determinate your APEX instance details.

Regards,

SLino

APEX 5.0.0.00.31 and HTTPS SQL workshop issue

Unable to see the source code in Firefox or Chrome

Just a quick update on something we discovered this morning. Maybe it is an old and known thing but we were definitely not aware of it.

In Firefox 39.0 and latest Chrome 43.0.2 version (if you have APEX 5.0.0.00.31 version installed on ORACLE 12c with HTTPS enabled) if you navigate to SQL Workshop to browse your tables and other DB objects for some reasons it all works except for Packages.

 

If you try to select a package and see its code your browser reports an error:

Mixed Content: The page at 'https://www.xxx.xx/ords/f?p=4500:1001:14464316:::::' was loaded over HTTPS, but requested an insecure resource 'http://www.xxxx.xx/ords/f?p=4500:2250:14464316:GETOBJECT_TREE:NO:::'. This request has been blocked; the content must be served over HTTPS.

Luckily I could confirm this with apex.oracle.com account  where there is different version installed Application Express 5.0.1.00.06 version which works perfectly fine. 

Bizarre or what. 

Update 16 July - problem seems to be in our web server configuration. We are running on Amazon with the SSL certificates in front of Apache and Tomcat/ORDS. Currently Amazon connects over http to Apache which causes the problems.

Solution was to change redirect rules to include:

Header edit Location ^http://(.*)$ https://$1

After this I could normally see package source code in SQL Workshop. Hope this helps.

Regards,
SLino

First Oracle APEX NZ meetup group

ORCLAPEX-NZ

First Oracle APEX meetup group in NZ

Hi all,

happy to announce that officially NZ is on Oracle APEX meetup map with first group meetup set to be held in Wellington on 23 of July.

For any more details you can follow and find on ORCLAPEX-NZ.

So watch the space!

Thanks,

SLino

KScope 2015 - APEX part 1

APEX 5.0 and other cool APEX features/changes

Things I picked up from this years sessions

This is first part of tips and tricks that I picked up during this year's KScope. As mentioned in my intro post there were so many interesting presenters who made us make a difficult choice choosing which session to attend.

Among the ones I attended here are best bits I picked up to carry home:

• page designer - Some would say here they (I) go again but if anything you could pick up on these sessions and chats around the room new Page Designer brings a lot to a table. It may take some time to get used to but once on board things are faster and more accessible making us us more effective
• keyboard shortcuts to access most needed designer features - Alt+Shift+F1
• Apex now supports advanced code editing features such as redo changes
• ability to duplicate objects
• regular expression are supported
• LIVE FEED in page designer that lets us know what is wrong when making changes or creating objects
• themes are now more responsive, versatile and customizable
• label items width is now configurable
• ability to load your own icon library is simplified
• numerous options to improve look and feel of Lists, Cards, Tabs or Carousel region
• menu Lists now can be hierarchical and easily convertible from top to side menu with a note that new hierarchical menu sub items can not have icons associated with it
• theme roller - probably most favorable feature of all people who are into design or have struggled with it in the past. 
• mobile windows support
• multiple interactive reports on same page
• IR have a pivot option
• there are now far fewer templates ~ 52 compared to 102 in some previous versions
• APEX_JSON package
• APEX_ZIP package
• APEX_UTIL.html_pct_graph_mask - very handy way to produce charts lines in your reports which has been present from early versions
• api.oracleapex.com to access official APIs - handy way to access documentation
• apex.oralce.com/ut - short cut for useful APEX 5 theme examples
• JQuery mobile page at some stage has both pages present at the same time which can be an issue if same region IDs are used!!!!
• use cool Chrome development features for debugging mobile apps like emulators etc.
• all application files will now be exported with application export and
• workspace export takes all apps and users and application images with it
• session state protection is now enabled by default
• modal page pages functionality from APEX 5.0 can be taken to older themes with some minimal page changes
• new apex_page.get_url function - acts as alternative for APEX_UTIL. PREPARE_URL
• new variables available in APEX authorization process: app_component_type, app_component_id, app_component_name
• new post authentication process
• New Substitution Syntax Features for escaping values &P1_TEXT!HTML. &P1_TEXT!RAW. &P1_TEXT!JS.
• use chain JQueries, execute all in 1 line instead of n lines 
• stop_apex_engine is an exception which actually stops any APEX page processing and changes are not committed - this can be caught with apex_application.e_stop_apex_engine
• APEX_MAIL.push_queue() procedure has an implicit commit in it! This is something to be aware of
• in Oracle 12c on tables instead of sequence and triggers for table IDs you can use GENERATED ALWAYS AS IDENTITY

Regards,

SLino

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. :)

Cheers,

SLino

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

begin
apex_application.g_print_success_message :='Successfully completed';

end;

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.

Cheers,
SLino