Applying the APEX 4.2.3 patch

My experience patching Oracle APEX v4.2.1 to 4.2.3 on my Windows 7 laptop.

Background

Personally my main driver for this is to experience the updated themes and templates. I'd also like to explore the packaged applications and see if any of the dynamic PL/SQL regions have been converted to templates.

I already had 4.2.1 installed and before the patch was available to me I thought I'd try running scripts on the full download of 4.2.3

It turns out if you attempt the apxpatch.sql script you get
ORA-39702: database not open for UPGRADE or DOWNGRADE

If you run the apxins.sql
Error: This version of Application Express is already installed (APEX_040200).

So you kinda need to use the patch. If I had more time and a virtual machine ready I would see if it were possible to revert to 4.1, then upgrade.

Process

First I closed my APEX Listener stand-alone console, preventing myself from playing with APEX in the meantime.

I'm still running on Oracle 11g XE, so I run the non-CDB option (referring to 12c container databases)
This means I unzip the patch p17347169_423_Generic.zip to my c:\apex4.2.3 folder.

I then open a cmd window, change directory to that folder and run SQL*Plus as SYSDBA, then run the patch script.
cd \apex4.2.3
sqlplus sys as sysdba
@apxpatch.sql

While that was running I need to manage the images folder. For me I decided this meant copying custom content from c:\apex4.2\images to my new c:\apex4.2.3 folder.
This was a /sage folder that houses my training supplementary files
I also decided it was time to create a /scott folder to store my nick-nacks I play with, instead of dumping that in the root /images folder.
I didn't have anything under /themes I needed to transfer.

This means I modify my start_listener.bat file that launches APEX Listener stand-alone to
cd c:\apex_listener2.0.1\
java -jar apex.war standalone --apex-images \apex4.2.3\images

The next thing I did was open a browser tab to see what the latest APEX Listener version was - and if there's anything that would compel me to upgrade.

After all that, the patch script finished in 28 minutes (on my laptop), and I was ready to restart my listener and log in.

I initially had a blank page with a bunch of resource GET errors - but then I realised I had a typo in the path of my start listener batch file.

At runtime, there is a difference in the developer toolbar - can't we just leave these things alone? ;-)

Difference spotted between 4.2.1 and 4.2.3

Bring on APEX5.

Oracle news you may have missed

During my time off I noted a bunch of information to dig into later, and I thought I'd make some notes as I go - see if you missed any interesting news.

I'll do a separate post for noteable blog posts.

Oracle REST Data Services

As Dimitri announced here, Oracle has another product "formerly known as" - this time it's the APEX Listener. Now known by what I think is a name that may also be confusing to some, though after reading this wiki on REST it's not as bad as I originally thought when I posted my comment on Dimitri's blog. Another good discussion can be found at Kris' blog.
Most importantly, it now get its own cool little logo.

Time to upgrade

This post from Dimitri was back in December, but it's more than relevant now. Just to get to the bar, you should be looking at APEX 4.2.x.
The sooner you're away from APEX 3.x or an earlier v4, the sooner you'll be ready to face APEX 5.
If you're using SQL Developer or GlassFish, those versions should also start with a 4. As mentioned, the APEX Listener/REST Data Services is already one up from his diagram.
If you have any say in your database, apparently 12c is all the rage - hopefully this year I can really start exploring the developer features.

Oracle Developer Day

I did attend one work event in my month off, giving my wife some practice without me home to help. Oracle held a Developer Day in Perth and as usual I learnt a zillion things about 12c new features from Connor McDonald. Reading the massive slide deck will never do the talk any justice, but he does show great examples describing why certain features are awesome and long awaited.

I didn't get to see Penny talk about APEX, though she guarantees I knew her content already. It was also interesting to get ADF workhorse Chris Muir's perspective on mobile development.

SQL Challenge

Congrats to Tony Winn & Scott Wesley on their perf in SQLChallenge, keeping the Aussie dream alive :) @swesley_perth @stevefeuerstein
— Jeffrey Kemp (@jeffreykemp) February 17, 2014

I'm not sure what Jeff meant with 'keeping the Aussie dream alive' but I did afford myself half an hour to take the first annual SQL challenge at Steven Feuerstein's PL/SQL Challenge website. As the rankings show, I came a respectable 12th out of 39. If you look closely, I did this in the 2nd lowest total time. This isn't because I knew everything, rather I had baby-induced tiredness and I thought I'd fire though the questions as quickly as I could. Some of them were quite tricky, and some required some 12c nouse - but I made some educated guesses and learnt some things.

If you don't know, the PL/SQL challenge not only holds daily (5/week) quizzes on PL/SQL, but also weekly quizzes on SQL, Database design and Logic puzzles. The design quizzes are mostly written by Chris Saxon and are worth a shot.

SQL Quiz

Speaking of quizzes, I see this tweet and of course had to accept the challenge.

Reddit seems to like my blog post: Result of a simple SQL indexing quiz: 60% fail http://t.co/Sc257BCy1U
— Markus Winand (@MarkusWinand) February 20, 2014

They were the perfect selection of 5 questions on general SQL tuning. They were Oracle based, though vendor specific questions were available for other DB technologies. There is a great write-up of the results here - but it'll only take a minute to try first - give him some more data.

APEX Certification BETA

David Peake announces we have until mid-May to take a BETA version of a new APEX 4 certification exam. Amazingly there is a testing centre within 10 minutes drive of my place - I think it might be worth looking into.

ODTUG APEX Theming Competition 2014

Reading through the submission guidelines, this sounds like it would a brilliant learning exercise - but as much as I'd like to give this a crack I might have to pass this time. Besides, I haven't worn a watch since last millenium! Though I would love to attend (and speak at) KScope - maybe when it's back at New Orleans ;-)

APEX 5.0 (EA)

Who could miss this? Well, me so far.. Having babies & all, I haven't had a chance to play with this yet - I've even held back reading much about it before I can really dive deep.
Dimitri once again has a great overview, but I'll be sure to post some myself

Evolution of APEX Evangelists

This sounds like an interesting enterprise, I look forward to seeing where it leads.

Please let me know if I missed any other cool announcements.

In the meantime, enjoy this short history of UX design.

Why I think GlassFish isn't dead

At this week's Perth Insync conference I attended a great session comparing Oracle Glassfish Server (OGS) with Web Logic Server (WLS), only to find out that Oracle will discontinue future commercial support of the OGS product as of 4th November 2013.

This instigated a great flurry of discussion as one of our current clients is on the way to deploying a server dedicated to APEX using one of the Glassfish products.

Background

Oracle recommends WLS as the web tier of choice for JDeveloper ADF, and the only choice for legacy Forms applications currently running on Apache that want to keep up with upgrading OS platforms.
OGS is typically a cheaper, lightweight alternative that can also support ADF - but is a more obvious choice for APEX deployments, in conjunction with the APEX Listener.

My head is rarely in the application server space, and I tend to not get too caught up with licencing discussions - no doubt others I refer to in this post know more about the topic than I, but I'm more than happy to provide an opinion from my perspective.

I try to catch up on my RSS feeds before the conference to keep an eye out for major announcements, this one obviously got through.
My first impression is that GlassFish has returned to what it was prior to the Oracle-Sun acquisition, so I found this made reading people's passionate opinions on the topic even more interesting.

The announcements

After finding this news story from ZDNet, this first post from familiar sources I encountered on the topic was from Kris Rice regarding APEX Listener support for application servers.

He confirmed the APEX Listener was still supported for WLS and Glassfish (3+), and in future support will be available for Apache Tomcat - thanks to public demand.

He also linked to the Aquarium's official annoucement regarding the Java EE and GlassFish Server Roadmap Update.

The discussion

I felt I needed more input so I started poking around to find a very vivid discussion on the topic, from Markus Eisele suggesting OGS is essentially dead and "thanks for all the fish".

I continued to read articles and they did seem to lean to one direction (anti-Oracle). Some looked back at Oracle's history with with open source projects, considering MySQL's fork into MariaDB.
The general concern is that since MySQL wouldn't be a revenue earner for Oracle, it would go neglected - stating that Oracle is even removing features.

To me this sounded like a slanted discussion without asking the other side. I'm not familiar with how MySQL works, but I'm sure Oracle limits the amount of hard parsing of SQL statements similar to this feature, just in a different component of the architecture.

I remember listening to a podcast describing what's new in MySQL 5.6 thinking that Oracle is vastly improving the MySQL architecture with solutions to match how the Oracle DB solves typical database issues. I didn't get the impression of neglect after reading these new features.

A number of people seem cynical of Larry Ellison's commitment to open source projects, but the information out there doesn't seem to reflect that.

Oracle's opinion

Then I started finding other contributions from Oracle's direction that started to provide some balance - and facts over fancy. Bruno Borges offered 6 Facts about GlassFish Announcement.

Someone else (I can't find the reference) noted the number of free/open source projects Oracle still delivers  - some of them rather high profile, so I think GlassFish shares some great company. To note just a few:

• VirtualBox
• SQL Developer
• Oracle Express Edition
• Hudson CI
• Various Linux projects

Many of them supporting arms for Oracle's revenue engines - as per what the Aquarium originally stated:

The primary role of GlassFish Server Open Source Edition has been, and continues to be, driving adoption of the latest release of the Java Platform, Enterprise Edition

Oracle can't resource every project, so others such as OpenOffice have been handed over to the Apache Software Foundation.

Conclusion

At the end of the day we can always refer to Oracle support document. Premier support ends for OGS 3.1.x in March 2016, extended support until 2019 - but sustaining support is indefinite.

GlassFish continues to feed & support important components of Oracle's infrastructure and I would have confidence it will continue to thrive in the forseeable future.

The following points help me come to this conclusion

1. APEX Listener continues to support OGS, in addition to WLS (and maybe Tomcat) - Oracle is pandering to customers needs, they don't need to 
2. OGS and WLS share code and are closely coupled with the Oracle JDK - oracle is committed to Java EE
3. OGS fills a niche and already shares a decent portion of the market - it has critical mass

I welcome further discussion on this and welcome any corrections to my statements.

APEX Listener 2.0.1 available

I've been using my local APEX install infrequently recently, but I have encountered occasional issues that I'm sure are attributed the the APEX Listener stand-alone.

Tonight I tried to import some plug-ins but received "Internal server error 500". I found a related OTN forum post with a recent comment saying it's solved in 2.0.1 - I'm pretty sure I didn't have that version.

So I just downloaded the latest APEX Listener and had immediate (successful) results.

Since I run the listener stand-alone, so all I did was unpacked the file to C:\apex_listener2.0.1, changed my "start apex listener.bat" to
cd c:\apex_listener2.0.1
java -jar apex.war standalone --apex-images \apex4.2\images
and restarted my listener. I didn't even have to log back into APEX ;-)

For those listening (no pun intended) on twitter, follow @krisrice for relevant announcements.

Happy upgrading!

Patching to APEX 4.2.1

I found the patch for APEX 4.2.1 to be easy, as usual - but I've come out with one question.

When in the APEX builder, keep an eye out for this region on the right hand side - if you haven't heard about a patch through the copious amounts of blogs or social media, then this is on place you might see the prompt.

As described on the APEX download page, you can either download the entire product; for English only; or if you're already on 4.2.0, you can download the 66mb patch set from My Oracle Support - patch #14732511

The patch set notes can be found here, or as part of your download.

My first step to upgrade the instance on my Windows 7 laptop was to terminate APEX Listener with Ctrl-C

Now open a command prompt, change current folder to where the patch is, connected to SQL*Plus and ran the install script
cd \apex4.2\patch
sqlplus /nolog
conn sys as sysdba

Ten minutes later, I found no errors in the apxpatch.log although the log didn't capture the final output I saw in the command display.
It finished in the middle of
Application Express 4.2.1 patch
... lots of stuff
... Sample Access Control: Application ID 7600
... some more stuff
......region template 5047053830717713422
-- no more logs!

I opened f7600.sql, found the exact location but saw no reason why the log would stop.

Moving on, it came to updating the images directory when using Oracle APEX Listener, and for me it got a little obscure.

Ever since I can remember, this step involved copying the images folder to where the relevant web server sourced the documents, but the current documentation suggests creating a web archive.

If you are using the Oracle Application Express Listener, you need to create a Web Archive for Oracle Application Express images

I decided not to do this in my circumstance - stand alone APEX listener, with no WLS/Glassfish because the  documentation for this option didn't require it.

So I made a backup of my old images folder by renaming to /images4.2/

Then dragged /images from the patch folder to my apex4.2 directory.

Now time to restart the APEX Listener using my batch file on the desktop, which runs
cd c:\apex_listener2\
java -jar apex.war standalone --apex-images \apex4.2\images

I also checked my older post regarding APEX 4.1 upgrade to see if there was something I missed.

Opening the localhost:8080/i/apex_version.txt showed the patched version, so no harm done?

My question - under what circumstances would you need to create the web archive - and what advantage/functionality does it provide?

Take care when importing applications from 4.2 to 4.2.1 - you will need to re-apply the theme (see patch notes)
It will also pay to read notes regarding jQuery Mobile 1.2.0, which is part of this patch - there are some interesting widgets to play with.

Now I very much look forward to the new year, upgrading my current client from 4.1 so we can start a project building an application for a tablet.

Scott

Upgrading to APEX 4.2 in three steps

I found upgrading my laptop's 11gXE instance from APEX 4.1 to APEX 4.2 ridiculously easy:

1. Stop APEX Listener (5 seconds)
2. Run install script (19 minutes)
   @apexins.sql apex apex_files TEMP /i/
3. Restart APEX Listener with new images address (15 seconds)
   java -jar apex.war standalone --apex-images \apex4.2\images

Job done.

Further details & other optional steps found in APEX 4.2 Installation Guide

When I have time I must find out if we can still tweak the APEX login page.

Scott

My name is Scott and I have a pet tiger

It seems I drafted this one about 18 months ago, worth sharing...

Me (back in 2000):
"Hi, my name's Scott. I'm your new developer"

My First DBA:
"Here's your db account, just don't change your password to 'tiger'"

Well, it wasn't quite like that, but it was joked about.

While setting up the Apex Listener, using Tomcat - I came across this in the help.

What is the default login for the manager and admin app?

The admin and manager application do not provide a default login. Doing so is a security flaw. You need to edit $CATALINA_HOME/conf/tomcat-users.xml if you are using the default install.

Security is no laughing matter.

Scott

Upgrading to APEX 4.1

Today I finally upgraded the APEX instance on my laptop to 4.1, and I'm very keen to start playing with the new goodies once I start finish my AUSOUG conference presentation.

When installing/upgrading anything Oracle, it's always a good start reading through the relevant installation notes. In my case, I started here:
Installing/upgrading with Apex Listener
http://download.oracle.com/docs/cd/E23903_01/doc/doc.41/e21673/otn_install.htm#BABEHJHG

I've heard some people are a little daunted sometimes with these notes - it's a shame because they are useful & concise. I've summarised the steps I needed to take below - as out of that page I linked to above - I only needed to apply a small percentage of commands. (I have APEX running on Glassfish on a Windows box)

1) First, I downloaded the software I needed for my system. Googling "download oracle apex" is always the best place to start for getting what you need.

I unpacked it to a fresh folder on my C drive:

2) Then I opened up SQL*Plus, ensuring my "Start in" was set to "c:\apex41", then logged in as SYS.
I ran the following script, which took about 15 minutes to run through and rejig my APEX 4.0 to 4.1
@apexins apex sysaux temp /i/

3) Then I had to copy all the images to my application server folder. APEX 4.1 has some fresh icons & updates to various supporting software like JQuery - so that needed to be updated.
However before I did this I backed up my existing images folder as I had modified some themes.
This is the command I executed:
xcopy /E /I C:\apex41\images C:\glassfishv3\glassfish\domains\domain1\docroot\i

The first time I ran I had permission issues overwriting the files, so I turned off read only for files in the folder. I probably should have thought a little harder over this, but since this laptop is very much localised just for my use, it didn't concern me much.

4) Before I did any cleaning up, I wanted to ensure I could log into the updated 4.1 instance, so I opened up the APEX log in page only to see the following error:
ORA-01658: unable to create INITIAL extent for segment in tablespace APEX

Turns out my tablespace was full, so I added another datafile:
alter tablespace apex
add datafile 'C:\app\Scott\oracle\11.2.0\oradata\sw11g\apex_b.dbf'
SIZE 500M;

From then I was able to log in successfully.

5) Then I decided to clean out any old users I had, so I ran this slightly modified SQL to list the users I could drop:

SELECT 'drop user '||username||' cascade;' ddl
   FROM dba_users 
 WHERE (username LIKE 'FLOWS_%' OR USERNAME LIKE 'APEX_%') 
   AND USERNAME NOT IN (
        SELECT 'FLOWS_FILES' 
          FROM DUAL 
         UNION 
        SELECT 'APEX_PUBLIC_USER' FROM DUAL 
         UNION
        SELECT SCHEMA s 
           FROM dba_registry
         WHERE comp_id = 'APEX');

DDL
----------------------------------
drop user APEX_040000 cascade;
drop user APEXLIB cascade;
drop user APEX_030200 cascade;

And after executing those drop statements my tablespaces got a little lighter anyway.

6) The only issue I've spotted so far is my modified login page, (thanks to Peter Raganitsch)
When opening my log in page, I noticed the following error in my console window - and my modifications weren't showing;
Error: uncaught exception: Syntax error, unrecognized expression: [src$=apex-logo-white.gif]

A quick google revealed this sort of response:
http://forum.jquery.com/topic/error-after-upgrade-to-jquery-1-5-in-img-src-new-png

So in our case this is probably due to the APEX team upgrading from JQuery 1.4.2 to 1.6.2 - so keep an eye out for that when you test your existing applications, if they use JQuery.

So I had a play with the code in the ApexLib_Loginpage.js script. I had to change this:
var vLoginTitle$ = jQuery("img[src$=apex-logo-white.gif]").parent();
to this, noting the quote usage:
var vLoginTitle$ = jQuery('img[src$="apex-logo-white.gif"]').parent();

This meant lines containing the bold also needed modification (to what you see here)
var vLoginRegionLogoImage$ = jQuery('img[src$="apex-db-apps.png"]');
jQuery('a[href^="f?p=4550:7:"]').parents(".rounded-corner-region").css("display","none");
jQuery('a[href^="f?p=4600:6:"]').parents(".rounded-corner-region").css("display","none");
jQuery('a[href="http://forums.oracle.com/forums/forum.jspa?forumID=137"]').parents(".rounded-corner-region").css("display","none");

So the upgrade took about 15 minutes computing time & about half an hour of my time, plus me sledgehammering jQuery to make it work.

Happy coding! Now, about that presentation...

Scott

Glassfish - url is not allowed

On my laptop I use Glassfish as my server platform for the Apex Listener.

A common problem I see (mainly from trainees trying to plug in my address) is the following error.
HTTP Status 404 - Requested url http://localhost:8080/apex40 is not allowed.

Now this isn't really a problem for me day-to-day because I have a nice bookmark that takes me to the correct location. But next time I try to set-up Glassfish, or I'm having some troubleshooting problems, I'll forget this issue.

It turns out, all I'm missing is a trailing slash on my address. It should be
http://localhost:8080/apex40/

I don't know if this is solved by some setting - I'm certainly not a web server expert. If anyone has any comments I'd be glad to listen.

... hold the phone! 

I just thought I'd have a read about the changes in Release 1.1.2, and I notice this fix:

Fixed a problem with an IllegalStateException being thrown when the path /apex (instead of /apex/) was accessed on the standalone Listener

I was adding this entry so I might also remember in future, and let's just say I passively found the solution, instead of actively looking for a potential one.

I remember seeing the announcement for this release, I figured I wouldn't worry about going through pain of upgrading since it's just my laptop... now I'm downloading as I post... (well I was, I scheduled this post a while ago to stop the bombardment of the start of July - but now I've created a paradox to confuse time travelers)

Maybe I'll remember to record the upgrade process...

ScottWe.

Apex Listener Issues

The configuration for my Apex 4.0 environment on my laptop is basically as you see on the front page for the Apex Listener.

I have Google Chrome on the left; my J2EE Container is Glassfish; and I have Oracle 11gR2 on the right, with Apex4.0 installed.

Sometimes I hate to admit it, but I don't do "networking". My Achilles heal is anything starting with "serv".

So when I rebooted my laptop recently and my browser said I couldn't connect to Apex, I thought - "I wonder what part of that horrible black box in the middle has broken?"

System Unavailable? I don't think so, Mr Laptop.

It wasn't really that bad. I pretended it was a development issue and started eliminating factors.

• My database was up
• I was about to get into the GlassFish Server Administration console ok (http://localhost:4848/), there didn't seem to be anything out of place.
• I restarted the server, the TNS listener, the Apex Listener
• I read through some documentation to look for troubleshooting ideas
• my apex-config.xml file was present and ok

Then I found myself looking at the username/password combination for APEX_PUBLIC_USER and thought "I wonder if that password has expired..."

Sure enough, all I needed to do was reset the password.

ALTER USER apex_public_user IDENTIFIED BY my_awesome_password;

Problem solved.

I mention this because it could have quite easily bugged me for an hour, and I like to save some people pain. I know there are some out there learning Apex and their deployment is balancing on guesswork from (barely) following Oracle's installation instructions.

In other news, I find myself reading through Oracle's Apex Listener front page, and I don't notice the big red

APEX Listener Release 1.1 is now available

So Apex developers, the Apex Listener Release 1.1 is now available ;-)

ScottWe

Apex Listener has arrived

For those of you who've missed it amongst all the Apex4.0 hubbub, the Apex Listener that was originally mentioned as being a separate release, is now available.

I've finally managed to get it running on my new Windows 7 64 bit laptop, but that was only after fiddling with the pre-release version and having a few other semi-related difficulties.

I've gone with Glassfish as my HTTP server, using Oracle Glassfish Server 3.0.1

There is now a dedicated OTN forum for the Apex Listener, and I've added forum entries on these problems - which I'm sure along with other little nagging things - the Oracle development will get onto with Glassfish as time goes on.

1) My URL to open Apex Builder requires the slash at the end, otherwise no deal
http://localhost:8080/apex40/

2) Unfortunately when I try to re-visit my listener configuration page
http://localhost:4848/apex40/listenerConfigure/
I get the following error
/apex40/listenerConfigure//index.jsf not found

I'm fairly happy with the set-up, it was relatively easy to do in the end and the new documentation accompanying the release is vastly improved - but Tomcat is no longer supported, only OC4j, WLS and Glassfish.

So now to continue exploring Apex4.0

I must say I'm coming across some improvements to the documentation. Apparently when creating new workspaces for existing schemas, Apex4.0 no longer grants any roles.
From there I noticed that there is a new glossary describing item types. I think this along with the Available Conditions appendix is the sort of documentation that is needed to assist people new to Application Express - that and the context sensitive help within the builder, that is sometimes a little lacking.

I confidence that over time, like all Oracle products, this will improve. I wonder what they're thinking about for the next release...