Upgrading to Apex 4.0

Leave a comment

Apex 4.0 is now available for download at oracle.com. I downloaded and installed it today.

Upgrade steps were simple, the same as previous Apex upgrades. In my environment, I encountered no problems. I’m running database 11.2.0 on Windows 2008 on VMWare with Oracle HTTP server 11.1.0.

The upgrade steps were essentially:
> Verify backups
> unzip apex_4.0.zip file
> stop Oracle HTTP server and Listener
> As SYSDBA, run apexins.sql file. I ran utlrp.sql after that as there were some invalid objects.
> Copy new set of images from apex_4.0 zip file to Oracle HTTP server images directory
> If desired, as SYSDBA, run PL/SQL code to give appropriate privileges to the new Apex 4.0 user
> Restart Oracle HTTP server and listener.

Everything in my main application worked fine, with the exception of the field list drop down on the find button on interactive reports. I’m using IE 7, and the drop down list does not display correctly. The same drop down list displays correctly in Firefox 3.6.3, so I’m guessing it’s a IE 7 issue. I don’t have IE 8 handy to try. The other parts of interactive reports are working as advertised.

Interestingly, interactive reports views that are saved by users no longer are referenced by tabs but from a drop-down list. And reports saved by developers for public use are now called “Default” reports in the drop-down list. Report views created by users appear under the same drop down list as “Private” reports.

Apex 3.2, Flash Charts, HTTPS and Proxy

Leave a comment

Another wrinkle on deploying Apex with a proxy server and https, this time concerning flash charts.

I wrote in my last post that I needed to make a few adjustments to work successfully with a proxy server in my environment. My environment: Apex, OHS 11.1.0, DB 11.2.0, Win 2008.

All seemed well until I tested pages with flash charts. First off, the IE browser would throw up the message box “This page contains both secure and nonsecure items. Do you want to display the nonsecure items?” After dismissing the message box, the page loaded but the charts would not display. Instead they hung while outputting the message “Loading data. Please wait.”

The first issue that needed sorting out was the https protocol, which was behind the “nonsecure items” message box. In Apex 3.2 there are hard coded references to the protocol at Region Definition – Source. One is


, the other is


I needed to change each http to https. Interestingly, at the Apex 4.0 website, I learned that this has been fixed. The protocol now has a variable, like this:


The next issue had to do with the proxy server.

The Region – Source screen also uses #HOST# variable, twice in fact. With the proxy server in place, #HOST# came back with the wrong value. Some surfing on the Apex forum at OTN revealed a dads.conf adjustment, which worked.

PlsqlCGIEnvironmentList        HTTP_HOST=proxyhosturl:port/dir
PlsqlCGIEnvironmentList        REQUEST_PROTOCOL=https

NOTE: don’t include the https:// prefix in the HTTP_HOST value.

Prior to that I had tried a more laborious work around. I created an Application Item called MY_HOST. I set this in an Application Process that runs On New Session – After Authentication. Then in every Region – Source for flash charts, I made the following change:




That also worked, but involved updating the Source yet again for each chart.

A final point. The charts available in Interactive Charts are also affected by https and proxies. Fortunately, making the above dads.conf adjustment is all that’s needed.

In summary, getting flash charts to work with https and a proxy requires updating the code for each chart so that https doesn’t generate a prompt, plus updating the dads.conf file.

I’ll be interested to try out 4.0 in my shop to see how that works with proxy and https.

Oracle Apex With HTTPS and Proxy Server

Leave a comment

Some Oracle Apex proxy server and https wrinkles affecting authentication.

In a deployment of Oracle Apex I’m working on, I needed to work with a proxy server. The proxy server has an https url. I’ve also deployed https on my Oracle HTTP server on the Oracle Apex machine.

I’m using the default https certificate that ships with Oracle HTTP Server 11.1.0. While this is not a signed certificate, end users never see a prompt from their browser because this is handled at the proxy server just once by the server administrator.

I’m using the standalone installation of OHS which is now part of the larger Weblogic suite. OHS and Oracle database are on the same Windows 2008 machine. Database version: 11.2.0. Apex Version:

1. I needed to get a value passed in from the proxy server so that users did not have to login. Here’s how.

2. I wanted to base my security on the username passed in from the proxy. To do that, I created an application item which is in the Shared Components – Logic – Application Items section. Then I created an Application Process (also in the Shared components – Logic) section. This process sets the application item during authentication time.

3. My authentication scheme is database.

4. I updated the authorization scheme to use Application Item mentioned above rather than default built in variable APP_USER.

5. I put in some conditional display of items which detects whether or not a user accessed the application via the proxy server. If they did not, then the Application Item is not set and they will not have sufficient authorization to use the application.

6. Finally, I updated the authorization values accordingly as needed on tabs, lists and pages.

Parse HTTP Header in Apex

1 Comment

I needed to get a value passed in from a proxy server via the HTTP header. I’m using Oracle HTTP server.

Browsing the OTN Application Express forums, I learned that do this, one has to:
1. Edit the dads.conf to include the following line.
PlsqlCGIEnvironmentList [your variable name]

2. Add code to obtain that variable. I added a Display as Text item and made its source a PL/SQL Function or Expression.

The source value was:
OWA_UTIL.GET_CGI_ENV(‘[your variable name]’);

Note that the following will print out all environment variables.
You can put this code in a region of type pl/sql block, or as an item.

A Winning Combination: Apex on VM

Leave a comment

I’m working with the following combination for an internal Oracle Apex deployment that will have a limited number of users.

  • Windows 2008 64 bit on VMWare
  • Oracle Database 11.2.0 R2 64 bit
  • Oracle Apex
  • Oracle HTTP Server

Response time thus far is fine. Deploying on VM saves money on hardware and should be able to meet performance requirements for the intended usage of this system.

Patching Blues

Leave a comment

No matter what people tell you, keeping Oracle database on most recent security patches can be tricky.

Here’s how my efforts went this afternoon.

I wanted to apply the Jan 2010 Oracle security CPU patch to a instance running Oracle APEX. Jan 2010 CPU patch has a specific APEX patch as well as an Oracle database patch. I learned the hard way that the Oracle database patch needed to be applied first after encountering errors in the APEX patch. Maybe I should have guessed that there was a dependency here, but I glossed over the “Known Issues” section in the APEX patch and it wasn’t mentioned elsewhere…

I got everything completed on my target Windows machine: DB patch then Apex patch.

On to an Oracle database running on Solaris. I applied the Jan 2010 CPU DB patch. Went fine. Ooops, no it didn’t. At the end it spit out some errors that said they were covered in Metalink note 353150.1. Turns out the Metalink note says the errors are false positives, but it took about 15 minutes of reading through the note and running a small test to verify that.

So on to the APEX patch on the Solaris DB. My test instance had been running just fine, but it ran out of shared memory when applying the patch. I retreated momentarily and shutdown some other instances on the machine, add a bit more to the MEMORY_TARGET parameter and fired up the instance. I applied the patch again.

Still errors on Solaris. I looked at the Jan 2010 CPU documentation again. For Solaris, it appears that the bug fix I need is only in the new PSU Jan 2010 patch and not the CPU one. I hadn’t tried a PSU patch, but since this is a lab instance, I figured I’d try it out.

I uploaded the PSU patch and started in with that. The opatch commands kept coming back with an error, so I learned that the opatch utility was no good for PSU patches. I needed the most recent opatch utility. I downloaded and applied that.

So ran the PSU patch on Solaris, which took over 1 hour on fairly fast hardware. I followed the post installation steps, which all went fine. Interestingly, the PSU patch did not have the same OS issue that the CPU patch during the verification section. Then I went back to the APEX patch on the same Solaris database instance. That went through without a hitch!

In sum, to patch Oracle APEX on windows and Solaris, I had to
> Apply Oracle on Windows CPU database patch.
> Apply APEX patch. Windows now done.
> On Solaris, download and apply new OPatch.
> On Solaris, apply PSU patch, including post-install steps.
> On Solaris apply Apex patch. Solaris now done.

Automating all this is possible, but certainly requires work if you do the scripting yourself, or money if you pay for OEM’s patching option or for a product like GridApp.

Oracle Apex 4.0 Demonstration Site Available

Leave a comment

Oracle has made available a demonstration site of Apex 4.0. It’s hosted in the Amazon Elastic Compute cloud, adding further credibility to Apex as a cloud computing platform. Sign up here.

I signed up for an account and took a 15 minute test drive. Here a few quick things I noticed:

1. There are a number of screens under a new Team Development tab. These are all straightforward, but provide ready made data entry screens and reports for the information needed to track a development project. Bug lists, new feature lists, tasks, etc. It’s very handy to have these right in the product.

2. I was most curious to try out the new interactive report features. Compound expressions for filters are available. These were implemented as a separate choice from the existing filter feature. I imagine doing this means that existing 3.x interactive reports will run without any changes in 4.0. The new feature uses an interface similar to the 3.x one for doing computed columns. When creating an expression, one references existing fields in the report by using a letter instead of the column name. It allows one to put in complex AND and OR logic with lots of functions as well. I also tried the GROUP BY feature, which worked fine and was intuitive to deploy. Finally, I saw that under save report there is a “Public” checkbox, which would allow a user to make a saved report that is visible to other users. That is really needed.

3. The initial screen for editing a page now uses a tree view to navigate all the objects on a page. Once you get to an object you wish to edit, the edit pages look quite similar to the 3.x ones.

4. I made a few quick graphs and as promised a newer version of AnyChart is available. The graphs look great!

I hope to port some existing apps to the trial web site over the next few weeks. Note that Oracle will erase the whole site in order to put a phase II beta, so be prepared for your work to get erased if you try this site out!

Application Express

Leave a comment

I had time for fiddling around with Oracle Application Express (APEX) and could justify it as part of Oracle 11g product evaluation. Some notes follow.

When you use the database creation assistant with 11g, it generates a script called apex.sql, which in turn executes $ORACLE_HOME/apex/catapx.sql. That seemed to work OK, except there is a fair amount of configuration afterward. And Oracle Text really needs to be installed in order for apex help files to display correctly.

So here’s my list of stuff to do if you want to install Application Express.
1. Install Oracle Text first. $ORACLE_HOME/ctx/admin/catctx.sql. Check that all objects in dba_objects are valid and that components in DBA_REGISTRY are valid.
2. Run $ORACLE_HOME/apex/apexins.sql. Review the log file and make sure the entry in DBA_REGISTRY is valid. For older versions, you may need to run $ORACLE_HOME/spex/catapex.sql.
3. Review and then execute the steps in the Oracle Application Express documentation. You want the Application Express Installation Guide. It explains quite a bit, including how you can run either the Oracle http web server (Apache) or the web server that’s inside the database called embedded PL/SQL gateway. Pick which web server you want and follow the appropriate instructions. Be sure to remember the password you supply when running $ORACLE_HOME/apex/apxconf.sql. You’ll need it to do admin work with APEX.
4. Something not mentioned anywhere that I saw was the listener.ora change you need to ensure that embedded pl/sql gateway works. Adding this in the DESCRIPTION_LIST worked for me:
(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Thanks Tim Hall of http://www.oracle-base.com for pointing this out on your web site!
5. At point I was able to log in using http://%5Bhostname%5D:8080/apex/apex_admin, using user ADMIN and the password supplied in step 3 above!