(YABAOAE) Yet Another Blog About Oracle Application Express

Oracle Learning Library Mobile

2012-01-26T08:32:00.003-05:00

The Oracle Learning Library team recently rolled out their new mobile application. If you go to http://www.oracle.com/oll, you should see a mobile icon in the upper left. Or you can simply bookmark this link on your mobile device.

As I discussed in the past, the Oracle Learning Library isn't just about Oracle Application Express or the Oracle Database. There is content there for virtually every Oracle product offered - JD Edwards, NetBeans, Solaris, Java, Oracle Business Intelligence, Fusion Middleware, Oracle E-Business Suite, etc. There are tutorials, white papers, Oracle By Examples, demonstrations, data sheets, and more.

And at the risk of being criticized (again), I can proudly proclaim that this is all running entirely on Oracle Application Express at http://apex.oracle.com - the same hosted instance of Oracle Application Express that gets over 10 million page views a week and between 600 - 700 new workspace requests every week.

Oracle Learning Library Advertisement Video

2011-12-15T15:09:00.000-05:00

The Oracle Learning Library team just published a new advertisement video, promoting the Oracle Learning Library. OLL is a great, free source for a lot of technical content on virtually every Oracle product. Just be sure you turn your volume down before playing this video - the music is quite loud.

You may ask why I'm promoting this on an APEX blog. Well, it's because:

The Oracle Learning Library application itself is written in Oracle Application Express.
There is a wealth of Oracle Application Express content available in the OLL.
It's yet one more application that runs successfully on apex.oracle.com. This application alone accounts for over 2M page views/events per week on apex.oracle.com

High CPU Usage and APEX (again)

2011-12-14T23:39:00.001-05:00

A couple months ago, I wrote about a customer who was experiencing High CPU Waits presumably from Oracle Application Express. Well, earlier this week I was at another customer site with exactly the same problem.

This was a case of "the business users" using Oracle Application Express to build an application. The application was built in short order and well received by the end users. The application became popular, they expanded the application and grew the user base, and after moving it to a different and faster environment, they hit a wall. The DBA's performed some analysis on the application, and based upon the Active Session History reports, they concluded that the statements using the most CPU were all from Application Express and of the form:

declare
   rc__ number; 
   simple_list__ owa_util.vc_arr; 
    complex_list__ owa_util.vc_arr; 
begin
owa.init_cgi_env(:n__, :nm__, :v__); 
htp.HTBUF_LEN := 63; 
null;  null; 
simple_list__(1) := 'sys.%'; 
simple_list__(2) := 'dbms\_%'; 
simple_list__(3) := 'utl\_%'; 
simple_list__(4) := 'owa\_%'; 
simple_list__(5) := 'owa.%'; 
simple_list__(6) := 'htp.%'; 
simple_list__(7) := 'htf.%'; 
simple_list__(8) := 'wpg_docload.%'; 
simple_list__(9) := 'ctxsys.%'; 
simple_list__(10) := 'mdsys.%'; 
if ((owa_match.match_pattern(p_string => 'f' /* */, p_simple_pattern => simple_list__ , p_complex_pattern => complex_list__ , p_use_special_chars => false))) then
    rc__ := 2; 
else
    null; null; f(p=>:p); 
if (wpg_docload.is_file_download) then
   rc__ := 1; 
   wpg_docload.get_download_file(:doc_info); 
   null;  null;   null; 
   commit; 
else
  rc__ := 0;  null;      null;   null;  commit; 
   owa.get_page(:data__, :ndata__); 
end if; 
end if; 
:rc__ := rc__; 
end;

The problem was, as I tried to explain to the DBA's, this PL/SQL block merely reflected the execution of a page view in Application Express. The ASH report will aggregate the "top" sections by top-level call, but the problem really lies within these calls, with what the application developers coded in their application. For all practical purposes, the overhead of the APEX engine is fixed, and anything else beyond that is what is coded inside of the application by the application developer. The DBA's asked a very reasonable question, namely "what do we do next?"

We started the process by analyzing the activity log of Oracle Application Express for the particular time window, to identify what was consuming the vast majority of time. And we were able to produce a nice listing of applications and pages, sorted by average execution time (really no differently than what I posted earlier). But once we did this, the DBA's wanted to be able to drill down into what was executing on those pages so they could analyze it and tune it. Given that the vast majority of this customer's primary application used SQL Reports and Interactive Reports, we joined the activity log with the APEX view which contained the SQL statements for the reporting regions on those pages (APEX_APPLICATION_PAGE_REGIONS). We ended up with the following query:

select sum(elapsed_time), count(*), 
       to_char(avg(elapsed_time),'9999.99') avg_elap, 
       a.application_id||':'||a.page_id app_and_page,
       dbms_lob.substr(b.region_source,2000,1)
  from apex_workspace_activity_log a
  join apex_application_page_regions b 
    on a.application_id = b.application_id and a.page_id = b.page_id
 where a.view_date between to_date('201112142100','RRRRMMDDHH24MI') 
                       and to_date('201112142105','RRRRMMDDHH24MI')
   and a.page_id is not null
   and b.source_type like '%Report%'
 group by a.application_id||':'||a.page_id, dbms_lob.substr(b.region_source,2000,1)
having count(*) > 10
 order by 3 desc

This query can also be issued directly from SQL*Plus by the DBA's. If they connect as SYS or as a user who has been granted the database role APEX_ADMINISTRATOR_ROLE, in Application Express 4.1, they will be able to query across all workspaces and APEX applications on the instance.

Understand that there are many places in an application which could be causing performance issues. There could be slow queries in an authorization scheme that is used in multiple places on a page. There could be processes or validations or elements on page 0 or a slow page sentry or a host of other things that could be causing degraded performance and which would become apparent when debugging a page. But to identify the report queries on pages and associate those with the activity log and slowest performing pages, the above query is a great place to start.

Oracle Application Express / Oracle Access Manager Integration White Paper now available

2011-11-21T13:41:00.003-05:00

David Peake, the product manager of Oracle Application Express, recently published on the APEX OTN site a white paper describing how to integrate Oracle Application Express 4.1 with Oracle Access Manager 11g. If you go to the Learn More section on the Application Express site on OTN, look for it in the bottom section entitled "Technical Information and White Papers". It's the one with the (obvious) title of Integrating Oracle Application Express with Oracle Access Manager.

Many thanks to Christian Neumueller from the Application Express team and Ramana Turlapati from the Access Manager team for all their efforts in this integration and white paper.

Adjusting the Width of an Interactive Report Filter Textbox

2011-11-08T07:36:00.004-05:00

I learn something new every day.

Today, Oracle Support sent the following customer question my way:

"We have columns with long data value but the filter (while running a Interactive report) in action menu is of fixed width and hence difficult for us to view the complete data. Is there a possibility for increasing the size of the filter dialog ? (which contains Columns, Operator and Expressions)."

When I asked for clarification, I received the following picture:

I'll never claim to know it all. But I will claim to know the people who collectively do know it all. Shakeeb Rahman from the Application Express development team replied with a simple solution. As Shakeeb stated:

"We can target the expression field and its drop down menu using the IDs that are assigned to these items. Here is the style that you would put into your page header to give these two items a fixed width:"

<style>
#apexir_EXPR, #apexir_col_values_drop { width: 300px; }
</style>

And that's all there is to it. If you want to adjust the width of the expression field and the associated drop down menu, simply add this inline style to the page header attribute on the page where your Interactive Report is defined.

High CPU Waits and SQL from Oracle APEX

2011-10-26T20:40:00.017-04:00

I was recently at a customer site helping one of the DBAs diagnose a performance issue on their rather large Oracle Application Express instance (over 800 production applications on the one instance). This gentleman self-admittedly didn't know all that much about APEX but he was adept at producing and analyzing ASH (Active Session History) and AWR (Active Workload Repository) reports.

He showed me the extraordinarily high CPU times between 0900 and 1100 his time, and he traced it to the following PL/SQL block that "comes from APEX":

declare 
   rc__ number; 
   simple_list__ owa_util.vc_arr; 
    complex_list__ owa_util.vc_arr; 
begin 
owa.init_cgi_env(:n__, :nm__, :v__); 
htp.HTBUF_LEN := 63; 
null;  null; 
simple_list__(1) := 'sys.%'; 
simple_list__(2) := 'dbms\_%'; 
simple_list__(3) := 'utl\_%'; 
simple_list__(4) := 'owa\_%'; 
simple_list__(5) := 'owa.%'; 
simple_list__(6) := 'htp.%'; 
simple_list__(7) := 'htf.%'; 
simple_list__(8) := 'wpg_docload.%'; 
simple_list__(9) := 'ctxsys.%'; 
simple_list__(10) := 'mdsys.%'; 
if ((owa_match.match_pattern(p_string => 'f' /* */, p_simple_pattern => simple_list__ , p_complex_pattern => complex_list__ , p_use_special_chars => false))) then 
    rc__ := 2; 
else 
    null; null; f(p=>:p); 
if (wpg_docload.is_file_download) then 
   rc__ := 1; 
   wpg_docload.get_download_file(:doc_info); 
   null;  null;   null; 
   commit; 
else 
  rc__ := 0;  null;      null;   null;  commit; 
   owa.get_page(:data__, :ndata__); 
end if; 
end if; 
:rc__ := rc__; 
end;

I've worked with Oracle Support on issues exactly like this from other customers too, so it seems to be a common problem "with APEX".

As I explained to this customer:

The engine of Oracle Application Express is written in SQL and PL/SQL.
When someone performs a page request from their browser to the Application Express engine, they are ultimately calling a PL/SQL procedure which is the entry point into APEX.
Oracle Application Express is front-ended by a Web server which has some facility to map requests in the URL to execution of the APEX engine - this is usually either Apache & mod_plsql or the APEX Listener or the XDB HTTP Protocol Server & embedded PL/SQL Gateway.
This "agent" on the Web server prepares an anonymous PL/SQL block for each and every request into the APEX engine. The PL/SQL he was showing me was exactly this anonymous PL/SQL block.

To state this succinctly and in more lucid terms, the PL/SQL block he was showing me wasn't anything out of the ordinary. I saw an anonymous PL/SQL block which was ultimately calling the APEX engine (via the code in the block: f(p=>:p); ). And since he showed me that this specific PL/SQL block executed about 800K times in an hour, I said that this directly correlated to the number of page events on his system, e.g., the number of times a page was rendered or posted or report paginated. He needed to drill down into the APEX instance and see what was consuming the vasty majority of execution time within APEX.

While there are nice instance administration reports in APEX to convey this type of information, this DBA didn't have instance administration access - poor guy. So we did some very basic queries against the APEX views to return the necessary information.

One of the most relevant views for him was APEX_WORKSPACE_ACTIVITY_LOG. Connecting as SYS (or in APEX 4.1 or later, connecting as SYS or a database user granted the APEX_ADMINISTRATOR_ROLE database role), we are able to see all information across all applications and workspaces. So we came up with a very easy query to pinpoint the slowest pages on the system during his time window:

select workspace, application_name, application_id, page_id, count(*) total_page_events, avg(elapsed_time) avg_elapsed_time, sum(elapsed_time) elapsed_time
  from apex_workspace_activity_log
 where view_date between to_date('201110260900','RRRRMMDDHH24MISS') and to_date('201110261100','RRRRMMDDHH24MISS')
 group by workspace, application_name, application_id, page_id
 order by 6, 7 asc

Thus, using this query, I was then able to tell him which application and page was consuming, on average, the most time on his system. Then, using this information along with other APEX views, we were able to identify the workspace names, applications, pages, report queries and eventually the administrators of these workspaces and applications so they could begin their own analysis and tuning exercise.

Who uses apex.oracle.com (as of August 28, 2011)?

2011-08-28T22:08:00.007-04:00

I was recently browsing a blog post over at ApexNinjas.com, where they showed the breakdown of visitors by country to their site, and found that most developers visiting their site came from the U.S. and Germany and asked the "guys who manage apex.oracle.com" if they could confirm this. From time to time, I like to post the Google Analytics report for apex.oracle.com, so people can see where users are coming from.

Note that I only capture the Google Analytics information from the apex.oracle.com login page and nowhere else.

Here is the map overlay and table by country of visitors to the login page.

apex.oracle.com - Google Analytics - 20110828

My interpretation of this report:

This report should not be used to determine the primary geographies of Application Express developers. There are countless developers who happily use APEX yet never visit the login page of apex.oracle.com.
The U.S. consistently has more visits than any other country.
Since I did this same exercise in 2008, there has been a significant increase from India.
Canada has also climbed into the top 5.
Poland and Brazil have vaulted into the top 10. Three years ago, Brazil was 23rd, now they are 7th. Impressive.

Where did the online help go in APEX 4.1?

2011-08-26T09:42:00.005-04:00

This morning, I responded to an inquiry from Oracle Support (on behalf of a customer) about the lack of "online help" in Application Express 4.1. There is also a current thread on the Oracle Technology Network discussion forum that is asking the question "where did the help go?"

This is not an oversight. This was intentional by design and for the following reasons:

This gives our documentation team greater flexibility in correcting documentation and online help issues. Here is a great example of why this is beneficial to us and our customers.
Many customers would never use the online help and use Google Search instead.
The online documentation is searchable.
This dramatically reduces the overall distribution size of APEX 4.1 as well as the size of the content loaded into the XDB repository (if you're using the embedded PL/SQL gateway).
This removes the complexity associated with indexing the online help files, especially if your instance is accessed over SSL and/or the database is behind a firewall that cannot get to the Web server.

And what about a customer who cannot get to the Internet, if they are disconnected or at a facility (like a military customer or government intelligence) where access to the Internet is blocked? Well, this customer can download the entire documentation, stage it somewhere, and then adjust the URL that is used when clicking the 'Help' link from within Oracle Application Express.

So....is this "improvement on the wrong side" as one customer suggests? I personally don't think so.

Error in Installation Guide for Oracle Application Express 4.1

2011-08-25T08:38:00.005-04:00

The new release of Oracle Application Express 4.1 will be installed into a new schema in your database. The name of this schema is 'APEX_040100'. Unfortunately, the Installation Guide for Oracle Application Express 4.1 references a schema name of 'APEX_041000'. This is incorrect. For any example code (e.g., creation of Network ACL) which references APEX_041000, simply replace it first with APEX_040100.

Hopefully this error is short-lived, as I have reported this error to our documentation team and requested that they correct this in the hosted documentation. If you're reading this blog post a month from now and you don't see any reference to the incorrect APEX_041000, then the problem has been resolved.

Thanks to Dimitri Gielis of APEX Evangelists for finding and reporting this error.

Important Update: As of August 26, 2011, this problem is now resolved. This is the beauty and flexibility of online help and documentation.

Oracle Application Express 4.1 released

2011-08-24T11:49:00.009-04:00

Oracle Application Express 4.1 was officially released today and is available for download from the Oracle Technology Network.

There are many new features in Oracle Application Express 4.1, including

Data Upload - add functionality to your application so your end users can upload CSV data (similar to what you can use in Application Express SQL Workshop)
Error Handling - Dramatically improved error handling, where you can define how exceptions are handled in your application instead of being constrained by the APEX engine itself. Patrick discussed this in detail here and here.
ROWID - Instead of being constrained by one or two element primary keys, you can simply use ROWID as the unique identifier for Automatic DML forms and tabular forms.
Websheets - Websheets have undergone a complete overhaul of the user interface, as well as some interesting new features, like the ability to create reports on SQL queries against DataGrid data.
Tabular Forms - Many new features with tabular forms, including tabular forms processes and tabular form validations.
Plug-Ins - Support for Authentication and Authorization plug-ins
Calendars - Enable editing of data directly from links in a calendar, and also support drag-and-drop to move rows from one day/time to another.
Dynamic Actions - Support added for dynamic actions against buttons, and other enhancements.
Accessibility - Numerous accessibility improvements have been made in the templates and HTML produced by Application Express.
Translations - Complete overhaul of the user interface, previously discussed here.

One obvious question is going to be "where is mobile support?" When jQuery Mobile becomes production software, we will include it in the very next patch set of Oracle Application Express. The infrastructure is there in APEX 4.1 to support it. And Marc Sewtz has promised to blog about how you can drop in the jQuery Mobile Beta into an APEX 4.1 installation. The jQuery Mobile Beta portion can't be supported by Oracle Support, obviously.

Our many thanks to the countless people who participated in the Application Express 4.1 Early Adopter program and provided feedback, suggestions and bug reports. We also owe a special thanks to the many Oracle employees who run their mission critical applications on apex.oraclecorp.com (an internal instance of Application Express, but for production applications, used by virtually every business unit in the company). They reported many issues and endured some outages while we rectified flaws in the upgrade process and Application Express engine itself. It is thanks to their patience and problem reporting that we were able to avoid embarrassing and reputation-breaking occasions for our customers.

UK Ministry of Justice, Zippy Zebra and Oracle Application Express

2011-08-11T20:57:00.005-04:00

An Oracle partner, Zippy Zebra, has recently published a case study of one of their most recent engagements. The system is for the Ministry of Justice in the United Kingdom. Zippy Zebra was commissioned by Capita and HP on this program, and their efforts were part of a modernization program for a system called OASys, which is the abbreviated term for the Offender Assessment System.

I received a personal demonstration of the Web interface of OASsys (written in Oracle Application Express) from Jonathan Rhind of Zippy Zebra. I was impressed not only with their expert knowledge of Oracle Application Express, but also their competence in Web technologies and user interface design. Most Web applications are "okay"; this system was beautiful.

You can read the full details in the case study on the Zippy Zebra site. Please note that the system is targeted to support a user population of 30,000, all running on Oracle Real Application Clusters (Oracle RAC), with Oracle Database 11g Enterprise Edition.

And who said APEX is only good for Access or Excel replacement?

tryapexnow.com for APEX 4.1 Early Adopter going away

2011-08-11T10:16:00.002-04:00

This may be a late blog posting, but for those who haven't noticed, on the login page of http://tryapexnow.com there is the following message:

"This instance will be turned off on 12-AUG-2011."

After this Friday, August 12, 2011, we will be extinguishing the tryapexnow.com database instance and server. It has served its purpose. If you want anything from there, please go grab it now.

Thanks to the 1,411 customers who signed up to kick the tires of Application Express 4.1 Early Adopter. Your time spent is appreciated and your feedback has been invaluable.

Need Your Support

2011-08-03T22:02:00.010-04:00

Firstly, this post has nothing to do with Oracle nor Oracle Application Express. So you can stop reading now, if you wish. Also, this is a shameless request for financial donations. So, again, you can stop reading now. As our friend Carl Backstrom said so many years ago, "Hey it's my blog I can post what I want ! :)"

In a few weeks, I will be participating in a bike tour with the sole purpose of raising money for cancer research. Last year, I rode with a group of guys for 43 miles. This year, I am doing the full 180 mile (289.6 km) tour over two days. I get nothing out of this endeavor other than tired legs and sore arms. My goals are to to raise awareness of this dreadful disease and help solicit donations which will help fund cancer research.

The tour is called Pelotonia. 100% of every donation will fund essential research at The Ohio State University Comprehensive Cancer Center - James Cancer Hospital and Solove Research Institute. I am simply asking you to help me reach my fundraising goal. Large or small, every donation makes a difference. Even a $5 donation is relevant - every contribution is important. In 2010, over 4,000 riders and volunteers participated to raise $7,846,705 for cancer research.

My ride this year has extra meaning for me. I am dedicating my 180 mile ride in honor of my father, Robert R. Kallman, a prostate cancer survivor, who passed away on July 22, 2011. He handled his disease and old age with both grace and zeal.

If you're interested, my Pelotonia profile is at: http://www.mypelotonia.org/riders_profile.jsp?MemberID=64498. Look for the "Donate To My Ride" link in red. And I know that you can be on the opposite side of the planet and still make a donation, as my good friend Arie Geller from Israel has already proven.

I am grateful for your support in this cause, no matter how large or small. Thank you.

Oracle APEX on Facebook

2011-07-06T16:08:00.004-04:00

Today, an email was sent out to everyone who subscribes to the Oracle Database Insider newsletter. In this email were 3 links:

This announcement isn't going to move mountains, but the important thing to note is that Oracle APEX on Facebook is created and run by Oracle Product Marketing. For all those people who ask "where's the marketing for APEX from Oracle?", here's just the start of it. And with more to come.

You shouldn't use Oracle Application Express because...

2011-07-04T19:48:00.014-04:00

I always revel at the myriad of untruths and misinformation given about Oracle Application Express and the reasons why you shouldn't use it. Some of the most common and my favorites are:

It can't scale. Most people know this is my favorite to refute.
It's only suitable for Access and Excel replacement.
With the Sun acquisition, it's going to be all Java all the time. And APEX is not Java. (An Oracle partner told me this, not aware that I work for Oracle).
It's written in SQL and PL/SQL. It can't really be all that extensible. It's in SQL, after all. (This gem came to me from Oracle's own Siebel CRM team).

Well, I have a new one to add to the list. At the recent ODTUG Kscope conference in Long Beach, CA, I met with two partners who have been building an elegant application for use by the United Nations (yes, those United Nations). In their presentation, one of the attendees raised the issue of Oracle's commitment to APEX. He raised the point of how most Oracle products are labeled as 11 release whereas APEX has its own release numbers. For him this must be a sure sign of long term non-commitment and preference to ADF.

Let me state a few things:

The difference in APEX version number has zero (as in nil) bearing on any direct or indirect commitment by Oracle for APEX
We have a major revision of Oracle Application Express at least once a year. The database does not release that frequently. It's beneficial to not have the same version number, otherwise, we'd have to sit and cool our heels between 11gR1 and 11gR2, or 11gR2 and Database 12.
The Oracle Database ships with Oracle Application Express, and has done so since Database 10gR1 - all the way through 10gR2, 11gR1, 11gR2 and Database 12.
With respect to the forthcoming Database Express Edition (XE), to reduce the size of the download and size of on-disk distribution, the early decision was to not include Oracle Application Express. But the Senior VP of Server Technologies responsible for all of Oracle Database asked for APEX to be put back in and included with XE.

Oracle Application Express was first started in 1999. Oracle HTML DB and Oracle Application Express have been in constant development since the first supported production release in 2004. The versions and year of release are:

HTML DB 1.5	2004
HTML DB 1.6	2005
HTML DB 2.0	2005
Application Express 2.1	2006
Application Express 2.2	2006
Application Express 3.0	2007
Application Express 3.1	2008
Application Express 3.2	2009
Application Express 4.0	2010
Application Express 4.1	2011*

I think there's only one thing to safely conclude about this person who doubted Oracle's commitment to APEX solely because of the difference in version numbers....he has no idea what he's talking about.

Translations in Application Express 4.1 Early Adopter 2

2011-06-11T03:52:00.018-04:00

With Application Express 4.1 Early Adopter 2 recently announced, one of the enhancements that needs clarification are those affecting translations. The purpose of this post isn't necessarily to explain how the translation process works in Application Express but, instead, to explain the improvements. These aren't earth-shattering enhancements, but they're intended to improve the process and information for the developer.

It does begin with a revision to the translation home. Here, a developer should be able to quickly see what translations are defined for the current application and if any of them require synchronization. A translated version requires synchronization when the primary application has been changed, but the translated version hasn't been published, to pick up this change. Anyone who has worked with translations in the past has gone through the pain of debugging a translated version of their application, only later to realize that it's out of sync with the primary application.

When it comes to seeding and publishing, these operations can now be performed on the same page and in bulk. If you have multiple languages mapped for your application, you can quickly seed and publish them all from the same page.

And the same type of improvement has been made to the XLIFF file upload and apply translation process. You can now upload up to 10 XLIFF files at a time and also apply these files to different translations in one operation.

Lastly, and most importantly, is the ability to include the application translations in an application export. Prior to Application Express 4.1, if you ever needed to move your application to a different workspace or different application ID, there was no practical way to bring along your translations. You basically hit the end of the road. Now, as an option to application export, you have the choice to include all of your translations in the application export file.

While these enhancements aren't perfect, they are at least an improvement over the previous interface and process. I must give credit to a few people for pushing for these changes.

David Bliss from the Oracle Store team has cringed every time he has to perform the translations in Application Express 4.0 for the Oracle Store. The Oracle Store is translated into a multitude of languages and the process he must follow is very cumbersome. He offered to even write the necessary changes in Application Express Application Builder for us if we didn't have time. David provided a very detailed recommendation and extensive feedback on how this should be improved. There is nothing like getting feedback from an end-customer who frequently lives and breathes through this process.
Francis Mignault from Insum has been asking for years for a way to move translations from one instance to another, and to different applications. I praise his persistence. He also helped to rationalize the numerous ways to accomplish the same tasks.
And lastly, credit must be given to Roel Hartman and Peter Raganitsch - for Peter's presentation at Oracle Gebruikersclub Holland and Roel's blog post about it. When someone says about translations in APEX "In short: it works, but takes a lot of work and maintenance is not so simple. So if you can stay away from multi-lingual applications, you should.", that is not to be perceived as a compliment. And justifiably so.

Application Express 4.1 Early Adopter 2 is now available

2011-06-10T16:25:00.007-04:00

As promised earlier this week, Application Express 4.1 Early Adopter 2 is now available at http://tryapexnow.com. Thanks to the many participants (1,136 total workspaces) who participated in the Early Adopter 1 of Application Express 4.1. The feedback has been extraordinarily valuable.

A few notes about Early Adopter 2:

Nothing was migrated or updated from Early Adopter 1. You'll need to sign up for a new workspace again in Early Adopter 2.
Some of the issues of using Internet Explorer 7 with Application Express 4.1 have been corrected, but not all of them. You will be best-served to use IE8 or higher (if you're stuck on using Internet Explorer).
Click the "What's New" link to see what's new in APEX 4.1, as well as what's new in Early Adopter 2. Some notable additions include Data Loading wizard, Drag & Drop Calendar, and numerous translation improvements (including the ability, finally, to include translations in your application export).
And just to state the obvious, this is evaluation software. You can't contact Oracle Support about any issues on tryapexnow.com. They won't have any idea what you're talking about.

Thank you once again for all of your support and many contributions.

Advanced Registration for ODTUG KScope11 ends tomorrow

2011-06-08T09:37:00.002-04:00

The Oracle Development Tools User Group is holding their annual technical conference (named KScope11) in Long Beach, California in a few weeks. If you're into Oracle Application Express, this is the conference for you. There is a separate track dedicated to Oracle Application Express.

Advanced registration, with the opportunity to save $250 off of the normal conference fee, ends tomorrow, June 9, 2011.

Hope to see you there.

Application Express 4.1 Early Adopter 2 is coming

2011-06-06T09:24:00.002-04:00

The Early Adopter of Application Express 4.1 is progressing nicely. There are 998 workspaces on http://tryapexnow.com and the feedback has been invaluable.

Early Adopter 2 of Application Express 4.1 is coming. It will introduce a number of features which didn't make the cut for EA1, including Data Loading, Drag & Drop Calendars, and numerous translation improvements.

On or after this Friday, 10-JUN-2011, the current Application Express instance on http://tryapexnow.com will be terminated and a new one will be created. Nothing will be migrated. If there is any information you would like to save from your workspace, I encourage you to export it before Friday, 10-JUN-2011.

Thanks once again for the tremendous response and extremely valuable feedback and guidance. It is our customers who ultimately help make Application Express what it is.

Application Express 4.1 Early Adopter 1 available

2011-05-06T15:40:00.006-04:00

The Early Adopter 1 version of Oracle Application Express 4.1 is now available at tryapexnow.com. Feel free to sign up for a workspace and give it a whirl. We welcome your feedback (via the Feedback link) at any time.

A few important points about Early Adopter 1 of Oracle Application Express 4.1:

If you're using Internet Explorer, you will be best-served to use IE8 or higher. You may run into difficulties with IE7, which we hope to correct in the future.
Click the "What's New" link to get an overview of the new features in Application Express 4.1. You'll quickly get the sense that this is not the same "big bang" that you saw between APEX 3.2 and APEX 4.0 - but that's by design.
You will see some items listed on the APEX Statement of Direction which are not in Early Adopter 1, most notably Mobile Support and Data Upload. Those didn't make the cut for Early Adopter 1 but we hope to be able to provide these when we refresh the tryapexnow.com instance.
When we do refresh the tryapexnow.com instance, we will not be migrating any information from the workspaces there. There's also no guarantee that what you export from APEX 4.1 Early Adopter 1 can even be imported into a later Early Adopter instance or APEX 4.1 production.
Our friend Patrick Wolf should be blogging soon about the extensive changes made in support of error handling. More explicit control over the error handling in APEX has been a long sought feature. And hopefully this will satisfy those of you who have said the "error handling in APEX sucks" (yes, Bart, that's you).
And just to state the obvious, this is evaluation software. You can't contact Oracle Support about any issues on tryapexnow.com. They won't have any idea what you're talking about.

Thank you for all of your support.

Oracle Application Express in Research Environments

2011-04-28T10:22:00.003-04:00

Today, Oracle issued a press release in conjunction with Indiana University, highlighting their use of the Oracle Database and Oracle Application Express in their research environment.

Indiana University Supports Researchers, Inspires Innovation and Secures More Research Grants with Web-Based Data Environment

I think it's a perfect example of:

An organization providing access to the rich functionality of an Oracle Database as a service, on their "private cloud".
How Oracle Application Express can be used to provide easy access to this rich database functionality.
How Web and database application development via APEX is approachable by those who don't have "Web Master" on their business card.

It's a natural extension to see how Oracle Application Express could be used in research environments, but on Oracle Exadata. Stay tuned.

Application Express 4.0 and Library Cache Latch Contention in Oracle DB 11.2.0.2

2011-04-27T17:04:00.026-04:00

In January of 2011, Oracle Support approached me about a Service Request they received from a customer. This customer had an APEX 4.0.2 application in production with more than 1,000 users. After they upgraded their Oracle database to version 11.2.0.2, the system ran fine for 2 days and then eventually "locked up", as they put it. They identified the problem as being related to library cache locks, and the offending statement they reported was:

begin wwv_flow_log.g_content_length := sys.htp.getcontentlength; end;

I reviewed the problem myself, reviewed it with a couple of other folks on the APEX development team, and we were mystified how this could possibly be related to library cache locks.

Fast forward to today, and I was contacted by a completely different customer who was running a benchmark with 100 simulated concurrent users (concurrent as in truly concurrent with no think time). Their conclusion after the benchmark was that there was library cache latch contention due to Application Express, and lo and behold, the statement they identified as being the source of library cache latch contention was:

begin wwv_flow_log.g_content_length := sys.htp.getcontentlength; end;

This was beyond coincidence that two different customers, in highly concurrent use of an APEX application, identify latch locks and/or latch contention issues with the very same line of the APEX source code.

The PL/SQL block in question was in the activity logging PL/SQL package of APEX. This is the package that writes to the activity log of Application Express, which tracks information like the application, page, user, elapsed time, etc. One of the changes in Application Express 4.0 was a new attribute in the activity log called content length. It was populated via a block like:

begin
    execute immediate ('begin wwv_flow_log.g_content_length := sys.htp.getcontentlength; end;');
exception when others then
    g_content_length := 0;
end;

I'm sure some will ask why this was done dynamically. Simply stated, because it could not be statically compiled. Most versions of the PL/SQL Web Toolkit will not contain the function getcontentlength() in the package specification. So for those installations that do contain this function, the dynamic execution will succeed. For those that don't (the vast majority), the parse of the PL/SQL block will fail, an exception will be raised, and the exception block will catch it and set a variable to 0. But why would something like this cause library cache latch contention?

I really had no idea. I found no bugs on this issue. apex.oracle.com was on APEX 4.0.2 on 11.2.0.2 and this was never an issue. So I diagnosed this problem the only remaining way I knew how...I asked Tom.

I knew in advance that Tom was going to rail about the "when others", but I told him I wasn't the author of this code. He also pointed out that doing this dynamically would not result in optimal performance. Sounds good...but that's not the source of the latch contention. In a nutshell, Tom suspected that there is a change in the way the parse happens in 11.2, and a bad parse (i.e., failed parse) is more expensive in 11.2 than earlier database versions. Is it a database bug? Maybe - but it also could simply be changed behavior.

Rather than wait for this problem to be diagnosed as potentially a database bug and maybe some database patch set may contain a fix, I decided to work around this. This particular piece of code, for all practical purposes, has been commented out - no dynamic execution, no failed parse and exception processing, for each and every invocation of the activity logging procedures.

I believe this issue is quite important. For our customers on large, highly concurrent production sites on APEX 4.0, when they decide to upgrade to Database version 11.2.0.2, they may experience degradation in performance for the same application with the identical load. There is now a patch set exception (a "one-off patch") for this issue for APEX 4.0.2.00.07 on My Oracle Support. Look for patch 12404581. The actual patch itself is not database version specific. Hopefully this blog post (and patch) will save some of our customers from future headaches.

Oracle Finance is looking for an experienced Application Express developer in Reston, Virginia, USA

2011-04-14T19:57:00.003-04:00

I'm posting this to help my buddy Arie in Oracle Finance locate the right candidate.

Oracle America, Inc. is looking for a competent Oracle Application Express developer, to work out of the Reston, Virginia, USA office. OBIEE experience is also desirable. This person would work directly for the Oracle Finance division.

If you're interested in more details about this position:

Go to https://irecruitment.oracle.com
In the field labeled "Search Job Descriptions by Keywords ", enter IRC1453677
Click the "Search" button

Oracle Application Express 4.0 with Ext JS

2011-03-29T09:46:00.004-04:00

Packt Publishing is certainly getting in the groove when it comes to books on Oracle Application Express. They just released Oracle Application Express 4.0 with Ext JS, authored by Mark Lancaster.

I won't pretend to be an expert on ExtJS - I'm not. But I do know a number of customers who use Oracle Application Express combined with ExtJS and they love it. Additionally, I do know some of them have informally consulted with Mark Lancaster, as he is one of the recognized experts on the planet for Oracle Application Express and ExtJS. Mark's Playpen is his hosted demonstration site of the types of things you can accomplish with APEX and ExtJS.

I've browsed through this book and it seems to be another home run from Packt - very clearly presented and very polished.

Rewrite it in .NET

2011-03-14T22:15:00.016-04:00

A few weeks ago I received a call from a customer who had an urgent issue. While this customer had gone the proper route and was working with Oracle Support, they really needed an immediate resolution. I've also worked directly with this customer since 1996 and have respect for them. They're judicious with my time - they usually only call when the situation is dire.

This customer had an Internet facing application that was previously written using an older version of Oracle Portal. To get a "quick win", they rewrote the application in Oracle Application Express - it's a simple lookup application that also uses Oracle Text. Once they released this application, during the peak periods of the day they would witness hundreds of database sessions blocking each other. The cause of the problem? Why - Oracle Application Express, of course. The director had called me to "give one last try using Oracle Application Express" before they rewrote it in .NET. He knew how to push my buttons! I dropped what I was doing, reviewed their Service Request with Oracle Support, and immediately arranged to visit them onsite that afternoon.

Once I was onsite, the DBA showed me numerous sessions on their production system, all executing the SQL statement:


DELETE FROM wwv_flow_data
WHERE flow_instance = :b1
AND item_id IN (
SELECT ID
FROM wwv_flow_page_plugs
WHERE flow_id = :b3
AND page_id = :b2
AND plug_source_type IN
('SIMPLE_CHART', 'UPDATABLE_SQL_QUERY',
'DBMSSQL_CURSOR',
'FUNCTION_RETURNING_DBMSSQL_CURSOR',
'FUNCTION_RETURNING_SQL_QUERY_CACHED',
'FUNCTION_RETURNING_SQL_QUERY', 'STRUCTURED_QUERY',
'SQL_QUERY', 'DYNAMIC_QUERY'))

I looked at this statement and I immediately knew that this was a Reset Pagination process. The scope of this statement is for a single logical APEX session - meaning that there are Internet users growing impatient with the performance of the application and clicking "Refresh" in their browsers. [When a page request is executing in the APEX engine, if the end-user clicks Reload in their browser, it will not preempt the database session currently processing their request - it will initiate a new request to a new database session, concurrently competing for the same finite resources as the first.] And why would an end-user click "Refresh" in their browser? Most likely, it's a poor performing application.

Without getting into too many details of Oracle Text, this customer had implemented their search interface such that it could easily result in a broad wildcard expansion search, i.e., the query would be expanded to essentially include all indexed search terms in their Oracle Text CONTAINS clause. To prove my point, I asked them to isolate the query that one of these long-running sessions was performing and issue the same query from their command-line client. The same query took between 30 and 60 seconds - from the command line! Now imagine if you're some Internet user trying to get some work done. You enter your search terms and click Go - the natural tendency after a few seconds, let alone 60 seconds, is to try the search again. And that's what was happening - end-users were growing impatient and repeatedly clicking Go.

The SQL statement from above (DELETE FROM wwv_flow_data...) was due to a Reset Pagination process they were performing after clicking the Go button. The pagination information for the user in the session table of APEX would be deleted, the APEX engine intentionally does not COMMIT, and then their slow-running query would run - all the while maintaining the row-lock until the query completes. The second session comes along (from the impatient user), the DELETE for the pagination information is issued, but now it's blocked from the first session.

My simple advice to them was to improve the performance of the application, as it was essentially unusable. My recommendations included:

Inspect the search terms being provided, and if all of the search terms were to be eliminated because of stop words in their Oracle Text stopword list or the search terms didn't meet a minimum length, raise an error and don't issue the query.
Add a PREFIX_INDEX and SUBSTRING_INDEX to their Oracle Text index, since they were performing numerous wildcard queries
Disable the Go button after it was clicked. This doesn't make the query run faster, but it can help to reduce the number of concurrently running sessions, emanating from an impatient user.
Change the pagination of their Classic SQL Report to "Row Ranges X to Y (with next and previous links)." Again, this won't make the query run any faster, but this is one of the fastest pagination styles of Classic Reports - simply because it reduces the number of row fetches performed by the APEX Reporting Engine.

This customer implemented these changes over the next couple of days and they haven't had any issues since (and this was over 2 months ago).

The customer presented this as "an APEX issue." And somehow rewriting it in .NET was going to correct all these problems. But guess what? If they rewrote it in .NET or PHP or Ruby or Java or Formspider or whatever, the original query that took 30 - 60 seconds to execute is going to continue to take 30 - 60 seconds to execute in all of these environments, and their end-users were going to continue to be impatient. In my recommendations above, only the last one is specific to Application Express, and that one is simply a nice-to have. It was the first and second recommendations which fixed their application. Was APEX the cause of their performance problem? I say "no" - and in fact, it did a good job of exposing their application problem.

APEX and Tablespaces

2011-02-17T11:17:00.016-05:00

Adrian, an internal employee, recently sent me the following issue/question:

I am the administrator and owner of an internal APEX workspace. Through time I made multiple requests to increase the storage size of this workspace each time by 10 MB. All these requests got approved and as a result the total table space should have been of at least 80-100 MB. However, it is of only 41 MB.

To see the current table space I went to Administration > Monitor Activity > Report Tablespace Utilization (popup) and I got the following report:

Tablespace Name	Bytes	Amount Used	Amount Free	Percentage Used
APEX_1193421002954380607	43,057,152	40,697,856	2,359,296	94.52

I then requested 10 additional MB to be added and the request was immediately approved. Then, I extracted again the above report, but the exact same values appear (total tablespace – 41MB) so the approved increase of 10 MB is not visible.

Could you please indicate if there is a limit of table space that I can request, if this is a bug or if I am no (sic) looking in the right place?

Good question. Adrian stumbled upon a bug in APEX, but this topic deserves some further explanation of how tablespaces are created in Oracle Application Express.

When requesting or creating a new workspace in Oracle Application Express, you can choose to associate the workspace with an existing schema (one that you or a DBA may have created beforehand), or you can ask for Application Express to provision a new database user and associated tablespace. This post is centered on the latter scenario - where APEX provisions the tablespace and user.

The actual location of the tablespace's associated data file is determined by:

If Oracle Managed Files are in use, then the name of the datafile and location are determined by the DBA via the OMF setting.
If Oracle Managed Files are not in use, then the datafile is created in the same physical database file system directory where Application Express is installed. For example, if Application Express is installed in the SYSAUX tablespace, and the location of a datafile for the SYSAUX tablespace is /d1/oracle/oradata/mydb/sysaux01.dbf, then the files for the newly provisioned tablespaces will also be created in /d1/oracle/oradata/mydb.

In Application Express 3.2.1 and earlier, the name of the tablespace is FLOW_x and the datafile is named FLOW_y.dbf, where x and y are an integer. In Application Express 4.0 and later, the name of the tablespace follows the pattern of APEX_x and data file name of APEX_y.dbf. In both APEX 3.2.1 and earlier and APEX 4.0, obviously, the data file name is generated by OMF when Oracle Managed Files is in use.

So how big will these files and tablespaces be when initially created? Good question. In APEX 3.2.1 and earlier, the data file for the provisioned tablespace is as big as the "storage request" for the workspace. If someone signed up for a workspace and requested a 100 MB workspace, APEX would create a tablespace and associated datafile of exactly 100 MB. The datafile of this tablespace would not autoextend. The actual size of the data file on the file system would be 100 MB. In our hosting of Application Express over the years, we learned two important facts:

People will always request the maximum permitted. If you let them choose 1 GB, they'll choose 1 GB even if they're only going to store 100 rows in a single table.
Most of the tablespaces on our hosted instances were full of "air" and occupied a lot of disk space unnecessarily.

In Application Express 4.0, we got a bit smarter. There is now an internal instance administration setting named Auto Extend Tablespaces, and in all APEX 4.0 and later instances, this is enabled by default. Now when a tablespace is created, the associated data file is set to autoextend up to a maximum of the requested size. The initial size of the data file will be 10% of the requested size, and autoextend up 10% at a time. So if you request a 100 MB workspace, the initial data file will be roughly 10 MB and autoextend 10 MB at a time up to 100 MB.

Workspace administrators can request additional storage, and in APEX 3.2.1 and earlier, when this type of request is processed, the tablespace data file is simply resized by the requested amount. But in APEX 4.0 and later, if the data file is set to autoextend, then when processing this type of request, the maximum size of the data file is adjusted but the data file itself is not increased in size until it needs to be (via the normal auto extend action of a tablespace).

So now getting back to Adrian's initial question, he stumbled upon a bug in Application Express 4.0. The report he was executing in Application Express showed the physical allocated size of the tablespace data file and did not take into account that the data file itself could grow. To prove this, I ran the following query:

SQL> select bytes, autoextensible, maxbytes 
       from dba_data_files 
      where tablespace_name = 'APEX_1193421002954380607';

     BYTES AUT   MAXBYTES
---------- --- ----------
  45154304 YES  320339968

Sure enough - Adrian's data file is only approximately 45 MB and can grow up to roughly 320 MB. Thus, I can conclude with the following:

This is a simple bug which will be fixed in Oracle Application Express 4.1.
Adrian - you can take a break from making so many workspace storage requests, please.

Is Application Express only good for Excel or Access replacement?

2011-02-08T09:21:00.005-05:00

Is Oracle Application Express only good for Excel or Access replacement? This is what those with no experience with Oracle Application Express like to assert. And although I repeatedly give hard evidence on my blog about the scalability and breadth of Oracle Application Express, some (rightly so) view my writings as biased. Your Honor, I present to you Exhibit A.

Here is a recent press release from our partner Insum Solutions inc.:

MONTREAL POLYTECHNIQUE SCHOOL SELECTS INSUM SOLUTION TO DEVELOP THEIR INTERNSHIP AND JOB MANAGEMENT APPLICATION

A couple things to observe:

The customer, Ecole Polytechnique de Montreal, decided upon an Oracle Application Express solution before putting it out for bid.
The application (over 100 pages, 1,500 companies, 3,000 contacts, 5,000 students) is certainly more than an Excel or Access replacement application.

Is anyone using Oracle Application Express?

2011-01-21T15:57:00.012-05:00

Is anyone really using Oracle Application Express? I couldn't tell you the number of times I've been asked that question by customers. There's been little marketing of APEX (in part because the marketing dollars flow to for-cost products & features), yet I believe it's grown organically at a high rate over the last few years.

Iloon Ellen-Wolf from Oracle Support provided a list of all of the customers who have filed a Service Request against Application Express through Oracle Support over the last two calendar years - all 1,723 of them. And as I often say, it's a virtual who's who of the Fortune 1000.

Recently, Kris Rice was kind enough to aggregate the Oracle Technology Network download numbers of Application Express over the last few years, and it shows a nice trend:


2006    83,482
2007   107,021
2008   119,092
2009   148,304
2010   209,555

In 3 years, we have doubled the number of downloads of Oracle Application Express. It's difficult to extrapolate this to "number of users" or "number of developers", because a single instance of Oracle Application Express can suffice for hundreds of workspaces and thousands of developers and even more end-users (for example, within Oracle we have one APEX application used by 40,000 distinct users on a daily basis). Other customers download Application Express for their personal use on XE. So the number of developers and users per download is somewhere between 1 and "a plethora."

Activity on apex.oracle.com continues to be very brisk. We typically average well over 4 million page views per week and over 500 new workspaces every week. For the past 7 days:


Total Page Views:              4,882,817
Distinct Applications:             3,644
Distinct Users:                    4,141
Number of Workspaces:             10,777
Number of Applications:           37,883
Workspaces Approved:                 535
Workspaces Purged:                   578
To be purged in a week:              594

535 new users in the past week alone kicking the tires of Oracle Application Express can't be all that bad.

So back to my original question - is anyone using Oracle Application Express? Yes, without a doubt - and it continues to grow.

APEX 4.0 Cookbook

2011-01-20T13:39:00.011-05:00

At the risk of alienating other authors (which is not my intent at all), I did want to raise awareness of a recently published book Oracle APEX 4.0 Cookbook written by Marcel van der Plas and Michel van Zoest. Very often, new developers to Oracle Application Express get up to speed quickly with the basics of APEX, but sometimes are left floundering when trying to do a specific task. The Oracle documentation on APEX tends to explain "what something is", and the Oracle By Examples are excellent soup-to-nuts tutorials, but often times, someone just wants to know "how do I do X?". That's where I think this book does an excellent job at filling this void, with very lucid and finite topics (e.g., Sending mail via APEX, Uploading and downloading files, Protecting a page using an authorization scheme).

P.S. For the record, I am deeply appreciative of all authors of APEX Books who have contributed so much of their time to help grow the APEX community.

Counting Clicks via Dynamic Actions

2011-01-14T11:15:00.010-05:00

The ink was barely dry on my recent post Counting Clicks when Dimitri Gielis suggested a much simpler way to achieve this in Oracle Application Express 4.0. And even though the suggested implementation is buried in the comments of that post, I wanted to explicitly offer it here.

This second solution is even more straightforward. It involves:

A dynamic action
An anchor with a specific class or ID

That's it! A demonstration of this logic is on pages 4 and 5 of this application here.

I created a simple table to store some of my most commonly used URLs. I built an application containing a report and form on top of this table, and then removed a lot of the functionality of the form to make it read-only.

Then, I created a simple log table with columns for some of the information I'd like to capture:

create table click_log(
    user_name        varchar2(1000),
    click_ts         timestamp,
    ip_address       varchar2(100),
    user_agent       varchar2(1000),
    apex_session_id  number,
    rownum_clicked   number,
    info             varchar2(4000) )

With this basic infrastructure in place, I now want to implement the two constructs to easily enable click logging of these URLs.

On page 5 of my application (the "form" page), I created a dynamic action named "Log Click" and with the following attributes:

Event: Click
Selection Type: jQuery Selector
jQuery Selector: #link
Condition: No Condition

The True Action was of Action Type "Execute PL/SQL Code" and the actual code itself was:


insert into click_log(
    user_name,
    click_ts,
    ip_address,
    user_agent,
    apex_session_id,
    rownum_clicked,
    info )
values
    ( v('APP_USER'),
      systimestamp,
      owa_util.get_cgi_env('REMOTE_ADDR'), 
      owa_util.get_cgi_env('HTTP_USER_AGENT'), 
      v('APP_SESSION'),
      v('P5_ID'),
      'Clicked: ' || v('P5_NAME') );
commit;

So with my Dynamic Action, all I needed to do is modify the anchor on my page to include the id "link". I did this by augmenting the URLs on my form page, changing them from:

<a href="http://www.amazon.com/" target="_new">http://www.amazon.com</a>

to:

<a href="http://www.amazon.com/" target="_new" id="link">http://www.amazon.com</a>

Because my jQuery Selector was for any element with an ID of "link", and I wanted the event to operate on my link, I needed to add this "id" attribute to my anchor.

You can see a demonstration of this logic in pages 4 and 5 here in My Favorite URLs application.

Counting clicks

2011-01-12T15:27:00.023-05:00

In the past week, I've been asked to help implement the "counting of clicks" in two different applications, the forthcoming Oracle Learning Library being one of these applications. All that's really desired is to track when someone clicks on a link. This is usually accomplished by responding to the click, inserting a row into a log table and then redirecting to the desired target URL.

If you're familiar with APEX_UTIL.COUNT_CLICK, this is implemented in a similar fashion. There is an underlying log table in Application Express which has a row inserted into it before redirecting to the desired URL. But the requirements of these two applications were a little bit different and required something more than APEX_UTIL.COUNT_CLICK, namely:

They wanted to preserve the logged clicks for all time and they wanted to insert into their own log table. In the case of APEX_UTIL.COUNT_CLICK, it's actually stored in a set of two rotating tables which switch every 2 weeks.
In the case of the Oracle Learning Library, we found that when you're on an SSL-rendered page, if you redirect to another URL, even a fully-qualified one, it will always redirect to an SSL (https) URL. Thus, we needed a solution which would not do a browser redirect.

The solution I devised was quite straightforward. It involved:

A page-level on-demand process
A local JavaScript function
and an anchor with an Onclick event

That's it! A demonstration of this logic is in an application here.

To demonstrate, I created a simple table to store some of my most commonly used URLs. I built an application containing a report and form on top of this table, and then removed a lot of the functionality of the form to make it read-only.

Then, I created a simple log table with columns for some of the information I'd like to capture:


create table click_log(   
    user_name        varchar2(1000),
    click_ts         timestamp,
    ip_address       varchar2(100),
    user_agent       varchar2(1000),
    apex_session_id  number,
    rownum_clicked   number,
    info             varchar2(4000) )

With this basic infrastructure in place, I now want to implement the three constructs to easily enable click logging of these URLs.

On page 2 of my application (the "form" page), I created a PL/SQL process named LOG_CLICK with execution point "On Demand - Run this process when requested by AJAX". It consisted of the code:


insert into click_log(
    user_name,
    click_ts,
    ip_address,
    user_agent,
    apex_session_id,
    rownum_clicked,
    info )
values
    ( v('APP_USER'),
      systimestamp,
      owa_util.get_cgi_env('REMOTE_ADDR'), 
      owa_util.get_cgi_env('HTTP_USER_AGENT'), 
      v('APP_SESSION'),
      v('P2_ID'),
      'Clicked: ' || v('P2_NAME') );
commit;

Note how I'm able to use both session state information via the APEX v() function and the PL/SQL Web Toolkit functions owa_util to get other information about the user.

The second part of this solution is a JavaScript function which I used to invoke my on-demand function. I defined this in the page-level attributes of page 2 in the JavaScript section for Function and Global Variable Declaration:


function f_logClick(){
 var req = new  htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=LOG_CLICK',&APP_PAGE_ID.);
 var gReturn = req.get();
 req = null;
 return gReturn;
}

I'm no AJAX wizard and I won't pretend to be, but this is the syntax (even in Application Express 4.0) to invoke an On-Demand process from JavaScript. For those familiar with On-Demand process invocation, note how I had to specify &APP_PAGE_ID. because I defined this process at the page-level and not the application-level.

So with my On-Demand process in place and my JavaScript function defined, all I needed to do is simply employ this function. And I did this by augmenting the URLs on my form page, changing them from:

<a href="http://www.amazon.com/" target="_new">http://www.amazon.com</a>

to:

<a href="http://www.amazon.com/" target="_new" onclick="f_logClick();">http://www.amazon.com</a>

You can see a demonstration of this logic here in My Favorite URLs application.

Note: View the comments below for an even simpler implementation using dynamic actions, new in Application Express 4.0

APEX 4.0.2.00.07

2011-01-12T09:15:00.002-05:00

Today, Niels, a customer, asked me:

"We have currently installed 4.0.2.00.06 and I couldn't find any information about the upgrade from this version to 4.0.2.00.07. What are the changes? What should be installed (metalink patch or full install)?"

A customer had earlier pointed out how this new .07 version had silently been rolled out. I addressed it on the OTN forum, but to repeat it here:

There are two differences (bug fixes) between 4.0.2.00.06 and 4.0.2.00.07, namely:

10347091 - Page sentry function of custom auth scheme does not work in 4.0.2.00.06
10406645 - Expired Application Express password cannot be changed

These issues were discovered after the release of APEX 4.0.2.00.06 and I didn't think that the fixes for these warranted a "4.0.3". I knew I risked confusion for our customers, which appeared to have happened, but that may only be a result of poor communication on our part.

If you have APEX 4.0.0.00.46, 4.0.1.00.03 or 4.0.2.00.06 installed, you can download the patch set (p10173973_11202_GENERIC.zip) from My Oracle Support and apply it. If you don't have Application Express installed at all, then when you download Application Express 4.0, you'll automatically get 4.0.2.00.07.

BugDB Reporting - Version 2

2010-12-10T12:36:00.008-05:00

Sorry, but this is another post only relevant for Oracle employees.

In June 2009 I made available a simple application to reporting against the Bug Database. I blogged about it here. Since that time, there have been changes made to the BugDB (implementation of VPD) and also a later release of Application Express. With the exception of the download link, everything else is accurate from that blog post, which I have copied below.

It seems a lot of teams are looking to create an analysis application on top of the BugDB, to perform their own analysis of the BugDB data for their own product. There is a right way and a wrong way to get at this data. The wrong slow way to get at this data is to build a report on top of a query across the database link to the BugDB and query this data each and every page view in your application. The more efficient way to get at this data is to create a local table in your schema and then refresh from the BugDB into this local table, on demand. This results in much less load on the BugDB database itself, and is also a much faster application for your end users.

I actually cooked up something for someone else in Server Technologies. It will create all the necessary database objects to refresh from the BugDB and it also includes an Interactive Report on top of the primary BugDB table RPTHEAD. Anyone is welcome to use this application as a starting point. It’s also hooked up with SSO authentication, so if you import this into your workspace on the internal instance of Application Express at http://apex.oraclecorp.com, you’ll be ready to go with internal single sign-on. All you need to do is:

Download and unzip the application export BugReports_installer.sql from: http://general.us.oracle.com/~jkallman/bugreports/BugReports_v2.zip
Create a database link to the BugDB named ‘BUGDB.WORLD’ in your schema. https://bug.oraclecorp.com/help/ConnectToBugHelp.html#dblink
Import and install the application export BugReports_v2_Installer.sql into your workspace (presumably on apex.oraclecorp.com).
After installation, you’ll be prompted to Install Supporting Objects. You should simply click Next > through the wizard. Prior to this step, the database link BUGDB.WORLD must exist.

That’s all there is to it! Run the application, navigate to the Administration tab, choose your product and timeframe, click Refresh, and then click the Bugs tab to view the Interactive Report on top of your local data.

It may seem like a simple application, but with the Interactive Report on top of the BugDB RPTHEAD, each end user can create a variety of customized reports and save them. You can read about Interactive Reports here: http://st-curriculum.oracle.com/obe/db/apex/r40/apexirr/index.html

Oracle Application Express 4.0.2 released

2010-11-19T18:26:00.005-05:00

Today, Oracle Application Express 4.0.2 was released. This corrects a number of issues discovered in the original Application Express 4.0 release as well as the Application Express 4.0.1 release. In the README of the Application Express 4.0.2 patch set, there is a listing of all of the bugs corrected in this patch set. You can view a copy of it here. The patch set is cumulative and includes all of the patches and bugs fixed in the Application Express 4.0.1 patch set.

We have also repackaged the Oracle Application Express full distribution. If you have never installed or upgraded to Oracle Application Express 4.0, you can download the latest full distribution and install this and you'll have Application Express 4.0.2 out-of-the-box. Since this always creates confusion for customers (do you need the patch set or the full distribution?), use this guide:

If you have Oracle Application Express release 4.0 or Oracle Application Express 4.0.1 installed, download the Oracle Application Express 4.0.2 patch set from My Oracle Support and apply it. Look for patch number 10173973. Even though the file is named p10173973_11202_GENERIC.zip, seemingly only for DB 11.2.0.2, it can actually be used against any supported database version running Application Express 4.0.
If you have Oracle Application Express release 3.2.1 or earlier installed (including Oracle HTML DB release 1.5), download and install the entire Oracle Application Express 4.0.2 release from the Oracle Technology Network (OTN).
If you do not have Oracle Application Express installed, download and install the entire Oracle Application Express 4.0.2 release from the Oracle Technology Network (OTN).

And how do you determine what version you're running? Well, if you're a DBA, you can connect to the database as SYS using SQL*Plus and issue:

select version from sys.dba_registry where comp_id = 'APEX';

If you can login to the Application Express development environment, you'll see the version number in the lower right of every page.

As mentioned in an earlier post, there are two new and very polished themes included in Application Express 4.0.2. A lot of work has also gone into correcting the display and usability issues of all of the modern themes.

Oracle Application Express at http://apex.oracle.com had the final 4.0.2.00.06 patch set applied today, November 19, 2010.

Did You Get Your Free Server, Yet?

2010-11-14T20:14:00.004-05:00

Did you know that Amazon Web Services is giving away free virtual servers? I got mine yesterday. As of November 1, 2010, Amazon Web Services has a free usage tier for a year if you sign up for a new Amazon Web Services account. You can sign up and read more here. According to Amazon:

"To help new AWS customers get started in the cloud, AWS is introducing a new free usage tier. Beginning November 1, new AWS customers will be able to run a free Amazon EC2 Micro Instance for a year, while also leveraging a new free usage tier for Amazon S3, Amazon Elastic Block Store, Amazon Elastic Load Balancing, and AWS data transfer. AWS’s free usage tier can be used for anything you want to run in the cloud: launch new applications, test existing applications in the cloud, or simply gain hands-on experience with AWS."

If you've ever wanted to experiment with a server in the cloud and experience the robust offering that Amazon Web Services has in this space, now's your chance. We have had tremendous success running the Early Adopter programs of Oracle Application Express on Amazon Web Services.

Application Express 4.0.2 applied to apex.oracle.com

2010-11-13T06:43:00.007-05:00

On Friday evening, 12-NOV-2010, a pre-release version of the Application Express 4.0.2 patch set was applied to apex.oracle.com. A listing of all issues corrected in this patch set will be published next week when we release this patch set on My Oracle Support.

Normally, new features are not included in a patch set but there are two new themes included in APEX 4.0.2 (themes finely crafted by Shakeeb).

Theme 21

Theme 22

Application Express, Network ACLs and Oracle Database 11gR2

2010-10-27T10:39:00.012-04:00

In Oracle Database 11gR1, a new feature was introduced called Fine-Grained Access to External Network Services. Succinctly defined, this feature gives an administrator control over which database users are permitted to access external network services, and on which ports. If an application relied upon the PL/SQL packages UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP or UTL_INADDR, they would now need to be given permission to access the external network service via a Network ACL. An excellent writeup of this feature is on ORACLE-BASE.

There have been two changes in this feature in Database 11gR2 which may impact Application Express users. These aren't necessarily documented so prominently in the release notes or README of Database 11gR2, so I felt it necessary to share them here.

In Database 11gR2 11.2.0.1, the precedence order in evaluation of the network ACL entries has been changed to most specific to least specific. More about this below.
In Database 11gR2 11.2.0.2, the network ACL now applies to any use of DBMS_LDAP.

In the installation guide for Oracle Application Express, we document this feature in Oracle Database 11gR1 and also how to create a network access control list which permits the APEX engine to access any network service on any network port. The name of the ACL in our example is power_users.xml. It just so happened that someone else on our instance of Application Express needed access to an outbound HTTP proxy, so I created a separate network ACL for this user.

The Network ACL privileges looked like:


SQL> column host format a30
SQL> column acl format a40
SQL> select host, lower_port, upper_port, acl from dba_network_acls;

HOST                           LOWER_PORT UPPER_PORT ACL
------------------------------ ---------- ---------- ------------------------------------
proxyserver.domain.com          80         80        /sys/acls/proxy_rule.xml
*                                                    /sys/acls/power_users.xml


SQL> column acl format a40
SQL> column principal format a30
SQL> select acl, principal from dba_network_acl_privileges;

ACL                                      PRINCIPAL
---------------------------------------- ------------------------------
/sys/acls/proxy_rule.xml                 SOME_OTHER_USER
/sys/acls/power_users.xml                APEX_040000

Prior to 11.2.0.1, if APEX_040000 needed to access the outbound HTTP proxy, this would be evaluated in terms of the least specific rule to the most specific rule. The ACL power_users.xml permitted access to all hosts on all ports, and thus, there were no issues. But in 11.2.0.1 and higher, this is now evaluated from most specific to least specific. If APEX_040000 now needs to access the outbound HTTP proxy, it is blocked and we'll encounter the dreaded error message "ORA-24247: network access denied by access control list (ACL)". A match for the host in the outbound HTTP proxy is found in /sys/acls/proxy_rule.xml, APEX_040000 does not have privileges on that ACL, and now the fine-grained access control blocks the request.

To correct this, I had to grant privilege on the proxy ACL to APEX_040000:


    dbms_network_acl_admin.add_privilege(
        acl => 'proxy_rule.xml',
        principal => 'APEX_040000',
        is_grant => TRUE,
        privilege => 'connect' );

To address the second point, in 11.2.0.2, access to the host and port specified by methods in the DBMS_LDAP PL/SQL package are now controlled via this same fine-grained access control to external network services.

I can understand, from a security perspective, why this behavior was changed. However, if you've come to rely upon this behavior, upgrading to Oracle Database 11gR2 may introduce some changed behavior when accessing external network resources.

Custom Authentication Scheme for Oracle Application Express and Oracle Access Manager - Addendum

2010-10-20T11:46:00.002-04:00

As mentioned in my earlier post about Oracle Application Express integration with Oracle Access Manager, Dilip Gowda, an Oracle consultant, very kindly shared a Word document detailing all of the steps he performed to get Oracle Access Manager 10.1 working with Oracle Application Express 3.2. It can be downloaded from here. The custom authentication scheme for APEX should work with any APEX version - it's a generic header variable authentication scheme.

Once I get Oracle Access Manager 11gR1 installed and configured, I hope to prepare and share a similar document. The eventual goal is to turn this into an official whitepaper and then ultimately provide an out-of-the-box header variable authentication scheme in a future version of Oracle Application Express.

Custom Authentication Scheme for Oracle Application Express and Oracle Access Manager

2010-10-19T13:12:00.010-04:00

Our customers frequently ask about how to integrate Oracle Access Manager authentication with Oracle Application Express. There is currently a thread on the Oracle Technology Network discussion forum, asking for this type of solution. It has always been my intention to present this as an official whitepaper and recommended solution from Oracle. However, I have been struggling with some Oracle Access Manager configuration issues and I simply did not want to delay any further. The "official" whitepaper and detailed instructions will have to come later.

Back in March, 2010, I took careful note of a message that Scott Spadafore on our team had sent to someone in Oracle Support. It was a generic solution for authentication via an HTTP header variable. A couple months ago, this question came up again and Tyler Muth provided me a slightly modified version of what Scott had originally authored. With some more minor modifications on my part, I can share this custom authentication scheme, which can be used with Oracle Access Manager and really any environment which will securely set a header variable to an authenticated username.

The page sentry function is:


create or replace function header_variable_page_sentry ( p_apex_user in varchar2 default 'APEX_PUBLIC_USER' ) 
    return boolean 
as
    l_cgi_var_name              varchar2(100) := 'REMOTE_USER';
    l_authenticated_username    varchar2(256) := upper(owa_util.get_cgi_env(l_cgi_var_name));
    --
    l_current_sid               number;    
begin
    -- check to ensure that we are running as the correct database user
    if user != upper(p_apex_user) then 
        return false; 
    end if;

    if l_authenticated_username is null then
        return false; 
    end if;     
  
  
    l_current_sid := apex_custom_auth.get_session_id_from_cookie;
    if apex_custom_auth.is_session_valid then
        apex_application.g_instance := l_current_sid;
        if l_authenticated_username = apex_custom_auth.get_username then
            apex_custom_auth.define_user_session(
                p_user=>l_authenticated_username,
                p_session_id=>l_current_sid);      
            return true;
        else -- username mismatch. unset the session cookie and redirect back here to take other branch
            apex_custom_auth.logout(
                p_this_app=>v('APP_ID'),
                p_next_app_page_sess=>v('APP_ID')||':'||nvl(v('APP_PAGE_ID'),0)||':'||l_current_sid);
            apex_application.g_unrecoverable_error := true; -- tell apex engine to quit            
            return false;
        end if;
        
    else -- application session cookie not valid; we need a new apex session
        apex_custom_auth.define_user_session(
            p_user=>l_authenticated_username, 
            p_session_id=>apex_custom_auth.get_next_session_id);
        apex_application.g_unrecoverable_error := true; -- tell apex engine to quit
        --
        if owa_util.get_cgi_env('REQUEST_METHOD') = 'GET'  then
            wwv_flow_custom_auth.remember_deep_link(p_url => 'f?'|| wwv_flow_utilities.url_decode2(owa_util.get_cgi_env('QUERY_STRING')));
        else
            wwv_flow_custom_auth.remember_deep_link(p_url=>'f?p='||
                to_char(apex_application.g_flow_id)||':'||
                to_char(nvl(apex_application.g_flow_step_id,0))||':'||
                to_char(apex_application.g_instance));
        end if;
        --        -- register session in APEX sessions table,set cookie,redirect back
        apex_custom_auth.post_login( 
            p_uname      => l_authenticated_username,
            p_session_id => nv('APP_SESSION'),
            p_app_page   => apex_application.g_flow_id||':'||nvl(apex_application.g_flow_step_id,0)); 
        return false;        
    end if;    
end header_variable_page_sentry;

The high-level steps to be performed are:

Compile this function header_variable_page_sentry in the parsing schema of your application
Create a new custom authentication scheme. In the Page Sentry Function attribute of the custom authentication scheme, enter: return header_variable_page_sentry;
Add directive PlsqlCGIEnvironmentList inside the corresponding APEX Database Access Descriptor. By default, OAM Webgate uses the header variable REMOTE_USER.
Secure the APEX application in Oracle Access Policy Manager by defining its corresponding Policy Domain.
Back in your APEX application, make this new authentication scheme "current" for your application.

The custom authentication scheme should work in any version of Application Express. A gentleman from Oracle Consulting got this to work at a customer site using APEX 3.2 and OAM 10g. He very graciously put together a document detailing all of the steps he performed in Application Express and Oracle Access Manager to get this to work, which is invaluable to someone like me who is essentially OAM-ignorant. I've asked for his permission to share this document, and when/if I get his okay, I'll make it available from this blog.

Brief Discussion of Oracle Application Express

2010-09-27T13:44:00.007-04:00

Last week at Oracle OpenWorld 2010 in San Francisco, Justin Kestelyn of the Oracle Technology Network interviewed Oracle Application Express Product Manager David Peake. If you're looking for a fairly short discussion about Oracle Application Express, this is a good place to start as I thought David's message was clear.

Customizing the look of Interactive Reports

2010-09-19T18:00:00.002-04:00

If you've ever wanted to have precise control over the look and feel of the Interactive Reports in your Oracle Application Express application, take a look at Shakeeb Rahman's recent blog post on his new blog http://apex.shak.us. Shakeeb Rahman is on the Application Express product development team and is the master of all things HTML and CSS.

APEX events at Oracle OpenWorld 2010 - for your iPhone

2010-09-15T22:32:00.004-04:00

Marc Sewtz from the Oracle Application Express development team has been busy crafting a set of HTML templates and styleswhich are suitable for an iPhone or other small device. And to demonstrate this, Marc has crafted an APEX application running on apex.oracle.com (and Application Express 4.0) listing all of the Application Express-related sessions at Oracle OpenWorld 2010.

Bookmark this: http://apex.oracle.com/pls/apex/f?p=oow2010

And you can read more about it on Marc's blog.

P.S. Don't bother using this application with Firefox nor Internet Explorer. It will work with any Webkit-based browser (Safari, Chrome). But it will look even better on your iPhone or Droid.

SINCE when?

2010-09-14T23:40:00.010-04:00

This will not be new information for long-time Oracle Application Express users, but it's worth mentioning for those new to Oracle Application Express.

In APEX page items (those using Automatic DML) and columns in SQL Reports & Interactive Reports, you have the ability to format date and timestamp values using a variety of format masks. Every report column and form page item has a format attribute. For date and timestamp columns, you can specify any valid Oracle date or timestamp format mask, with a full listing provided here. Some examples include:

MM/DD/YYYY

DD.MM.RRRR

DS FMHH24:MI:SS

RRRR-MM-DD"T"hh24:mi:ss.xff

In APEX applications, you can also employ the SINCE format mask on date and timestamp columns. This is specific to APEX applications in the context of the format mask for report columns and Automatic DML page items. Instead of showing your end-users values like:

09/11/2010

you can present a value of:

3 days ago

The time period is relative to the difference between the time it is rendered and the value itself. Values can include 'seconds ago', 'minutes ago', 'hours ago', 'days ago', 'weeks ago', 'months ago', and 'years ago'. The benefit of using SINCE is:

It's time zone agnostic. Regardless of the time zone of your end user, 3 minutes ago is always 3 minutes ago.
It's locale agnostic. Some locales interpret 09/11 as September 11, other locales interpret 09/11 as November 9. Everyone interprets '3 days ago' as 3 days ago.
It's translated into the 10 languages provided for Application Express 4.0. The output will be correctly translated based upon your user's language preference.

New in Application Express 4.0:

Support for future dates and timestamps. You can now have values like '3 weeks from now', '2 days from now'.
Support for the SINCE format mask against columns of type TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.
And an API (should be documented, but I find that it's not right now) to compute this yourself:


APEX_UTIL.GET_SINCE( p_date IN DATE ) RETURN VARCHAR2

APEX_UTIL.GET_SINCE_TSWTZ( p_timestamp IN TIMESTAMP WITH TIME ZONE ) RETURN VARCHAR2

APEX_UTIL.GET_SINCE_TSWLTZ( p_ltimestamp IN TIMESTAMP WITH LOCAL TIME ZONE ) RETURN VARCHAR2

Impress your boss and your end-users with the SINCE format mask!

Going to Oracle OpenWorld 2010?

2010-09-02T14:53:00.004-04:00

Are you going to Oracle OpenWorld 2010, which starts on September 19, 2010? If so, be sure to attend the "APEX Meetup" on Tuesday night. Dimitri has more information on his blog.

Automatic Time Zone support in Application Express 4.0

2010-09-02T13:43:00.018-04:00

A feature of Application Express 4.0 which hasn't received a lot of press but is useful for those building applications that span time zones is the Automatic Time Zone application attribute.

The Oracle database has this wonderfully rich data type called TIMESTAMP WITH LOCAL TIME ZONE. The elegance of this data type is that the value stored in this column will be displayed in the user's current database session time zone. Having written a PL/SQL package to do time zone conversion, it is a non-trival exercise to develop this type of functionality let alone maintain it. Wouldn't it be great if we could put this burden of maintaining constantly evolving time zone rules and daylight saving time dates on the database? Well, you get this for free with TIMESTAMP WITH LOCAL TIME ZONE.

So if all we need to do is set the database session time zone, then:

How do we elegantly derive this for each end user of our application?
How do we ensure that every page view and page submission in Application Express has its database session time zone set correctly for a particular user?

There were numerous suggestions in the past, of storing a user's preferred time zone as a preference and then authoring a PL/SQL block in the VPD attribute of an application like:

execute immediate 'alter session set time_zone =''' || :MY_USER_TIMEZONE || '''';

Not exactly obvious. And this still doesn't answer question #1 of how do we elegantly derive this. This is where the new Automatic Time Zone attribute is useful.

In the Application Builder, if you edit the Application Properties and navigate to the Globalization subtab, you should see something like:

By default, Automatic Time Zone will be set to 'No'. When set to 'Yes', this will now change the behavior of your application:

At the beginning of an Application Express session (which happens at the beginning each time a user runs your application ), the time zone offset will be calculated from their Web browser client.
This time zone offset information will be sent to Application Express and recorded in the APEX session information for that user.
Then, each and every page view for the duration of their APEX session, the Application Express engine will read this value and set the database session time zone to this value.

All you have to do is employ data types which are time zone aware (like TIMESTAMP WITH LOCAL TIME ZONE; DATE is not time zone aware) and check a box in your application definition. It couldn't be simpler!

To demonstrate this, I created a simple application using the following DDL:


create table tz_log( 
    id number primary key, 
    username varchar2(255) not null, 
    tz varchar2(512) not null, 
    created_ts timestamp not null );

create or replace trigger tz_log_trg1
    before insert on tz_log
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    --
    :new.created_ts := localtimestamp;
end;

Then, I just built an application with a SQL report on this table and added an on New Instance PL/SQL Process of:

insert into tz_log (username, tz) values(:APP_USER, apex_util.get_session_time_zone );
commit;

You can run this sample application here. Just keep in mind - it will require you to authenticate with your oracle.com credentials (the same credentials you use if you login to the OTN discussion forum) and it will record your visit in a log table, which others can view. Here's what it looks like - nothing fancy:

If you pay close attention, immediately after authentication, you'll see a URL like:

https://apex.oracle.com/pls/otn/f?p=27207:1:127976719236631&tz=-4:00

Obviously, your APEX session identifier and time zone value will be different than what I show above. But you'll see that there is a new parameter 'tz' to the 'f' procedure. And it is through this interface that you can create a URL to an APEX application and explicitly set the APEX session time zone to a different value. After you login, change the time zone value in the URL to something else (e.g., tz=0:00) and watch the values in the "Inserted into the Log Table (in your local time zone)" report column automatically adjust to that time zone. The underlying report definition didn't change - we're still simply selecting the TIMESTAMP WITH LOCAL TIME ZONE column out of the database, just now the database is automatically converting that value to display in the current session time zone.

You can also programmatically set and get the APEX session time zone setting using two new APIs in Application Express 4.0, namely APEX_UTIL.SET_SESSION_TIME_ZONE and APEX_UTIL.GET_SESSION_TIME_ZONE.

Pelotonia10

2010-08-22T22:01:00.009-04:00

Sorry, this is not a post about anything Oracle-related. But as our dear, departed friend Carl used to say - it's my blog.

I had the privilege of participating in this year's Pelotonia bike tour. What is Pelotonia, you may ask? "Pelotonia is a grassroots bike tour with one goal: to end cancer. Pelotonia raises money for innovative and life saving cancer research at The Ohio State University Comprehensive Cancer Center - James Cancer Hospital and Solove Research Institute."

This event had special significance for me, as our next-door neighbor, Jacob Carlino, was diagnosed with a rare form of cancer earlier this year and is undergoing treatment now. Jacob is 12 years old. A child like Jacob or any person, for that matter, shouldn't have to suffer through this horrible disease. Jacob's father Dave organized a team in support of his son Jacob, and I had the privilege of being on this team. Above is the team - Dan, Matt, Scott, me and Dave. On the front of our shirts is a picture of Jacob who we were honoring with our team's tour.

Pelotonia was a most-impressive and well-organized event. There were over 4,000 riders and thousands of volunteers in addition to a great deal of corporate sponsorship. The estimate is that this one event will raise over $8 million, with 100% of this money going directly to fund cancer research at the Ohio State University Cancer Care Center and Solove Research Institute. The honorary chair of this year's Pelotonia tour was Ohio State football legend Chris Spielman, who he himself lost his wife to cancer just 9 short months ago.

My goal for this blog post? To simply raise awareness of this disease, and to also let the world know that there is an ever-growing army of people right here in Ohio who is contributing in ways small and large to the eradication of this disease. Lastly, I would like to recognize and thank the sponsors of me for this ride - I am grateful for your generous support: Sue, Sergio & Priscila, Jason & Shelley, Eric, Bob & Marge, Matt & Gretchen, Tim & Susie, Anton & Chris, Kathy & Terry, Neil & Margaret, Tom, Nada & Matt, Frank, Harry, and Mike & Anita.

Application Express 4.0.1 released

2010-08-18T10:00:00.009-04:00

Today, Oracle Application Express 4.0.1 was released. This corrects a number of issues discovered in the original Application Express 4.0 release. In the README of the Application Express 4.0.1 patch set, there is a listing of all of the bugs corrected in this patch set. You can view a copy of it here.

We have also repackaged the Oracle Application Express full distribution. If you have never installed or upgraded to Oracle Application Express 4.0, you can download the latest full distribution and install this and you'll have Application Express 4.0.1 out-of-the-box. Since this always creates confusion for customers (do you need the patch set or the full distribution?), use this guide:

If you have Oracle Application Express release 4.0 installed, download the Oracle Application Express 4.0.1 patch set from My Oracle Support and apply it. Look for patch number 9976149. Even though the file is named p9976149_11201_GENERIC.zip, seemingly only for DB 11.2.0.1, it can actually be used against any supported database version running Application Express 4.0.
If you have Oracle Application Express release 3.2.1 or earlier installed (including Oracle HTML DB release 1.5), download and install the entire Oracle Application Express 4.0.1 release from the Oracle Technology Network (OTN).
If you do not have Oracle Application Express installed, download and install the entire Oracle Application Express 4.0.1 release from the Oracle Technology Network (OTN).

And how do you determine what version you're running? Well, if you're a DBA, you can connect to the database as SYS using SQL*Plus and issue:

select version from sys.dba_registry where comp_id = 'APEX';

If you can login to the Application Express development environment, you'll see the version number in the lower right of every page.

Oracle Application Express at http://apex.oracle.com had the 4.0.1.00.03 patch set applied on Saturday, August 14, 2010.

Application Express and parsing of SQL

2010-08-13T15:03:00.006-04:00

Recently, I worked with Oracle Support who was helping a customer who had a variety of questions about Oracle Application Express. One of the questions was:

"Please provide a detailed explanation of how apex interacts with database security. It appears that apex is manipulating queries prior to presenting them tot he database to be run either by wrapping them in a pl/sql block that prevents role based security from working or doing a pre-security check that doesn't check role level security prior to issuing a query, or some other even that bypasses role based security. "

Since there really isn't any great explanation of this anywhere, I'd like to provide a brief but lucid explanation about how parsing of SQL works in Oracle Application Express.

The Oracle database ships with a supplied PL/SQL package named DBMS_SQL, to perform the execution of dynamic SQL.
The Oracle database also ships with a supplied but undocumented PL/SQL package named SYS.DBMS_SYS_SQL. This package enables the execution of dynamic SQL but it also enables the parsing of a statement as a specific database user and with the privileges of this specific database user. This PL/SQL package is highly privileged, obviously. The EXECUTE privilege on this package is not granted to database user, by default. It should almost never be granted to any database user, ever. When asked to grant execution on this package to someone, a smart DBA will always say "no."
Until Database version 10.2.0.3, both DBMS_SQL and DBMS_SYS_SQL would not observe roles when parsing SQL. This was consistent with database object access in PL/SQL itself (database roles are not observed in PL/SQL, in case you didn't know).
In Database 10.2.0.3 and later (and XE), SYS.DBMS_SYS_SQL was changed to support a flag which enabled the observation of database roles when parsing SQL.
In Application Express 2.1 and later, support was added to Oracle Application Express SQL Workshop on DB versions 10.2.0.3 and later to observe database roles, to be consistent with SQL*Plus. However, the execution of SQL in an Application Express application still does not observe roles when parsing user SQL. This remains true for the recently released Application Express 4.0.

So this explains a few things:

If you've ever wondered how Application Express can parse SQL as a specific database user and with the privileges of this database user but without ever connecting as that database user, this is the answer - DBMS_SYS_SQL.
When a new database user/schema is provisioned through Application Express, the discrete system privileges are granted to this new database user and not through any database role.
When you look the underlying database view V$SESSION, it will show that the database sessions associated with Application Express applications are connected as the minimally privileged database user APEX_PUBLIC_USER (or ANONYMOUS, if you're using the embedded PL/SQL gateway). But within that session, the underlying Application Express engine is being invoked and, after determining who the SQL can be parsed as for that specific page view, the SQL is being parsed as a different database user.
This also explains why, in some database versions, it will appear that roles are enabled when you issue DML statements from SQL Commands in SQL Workshop, but in the development of your application and in the execution of your application, it will appear that database roles are not enabled and that direct object privileges are required.

Moving your XLIFF Files

2010-07-23T00:15:00.014-04:00

The XML translation files generated from Oracle Application Express are produced in XML Localization Interchange File Format (XLIFF) format. XLIFF is a recognized standard for the localization of computer software. "It is intended to give any software provider a single interchange file format that can be understood by any localization provider."

One of the unique characteristics of Application Express is that it is one of the few development frameworks where the decision to localize and translate an application can be made after the application is actually completed. Because the definition of the application is maintained in meta data in the APEX repository, it's already known in advance which attributes of your application are translatable and which are not.

The process to produce a translated application is pretty straightforward. It's as simple as:

Seed the translation repository from your existing application
Export the XLIFF file
Translate the XLIFF file
Upload the XLIFF file
Apply the XLIFF file
Publish your translated application

The first few translation unit lines of a sample XLIFF file generated from Application Express look like:



Logout
Logout


Print
Print


Logout
Logout


Print
Print


Home
Home


Customers
Customers


Products
Products


Orders
Orders


Charts
Charts

Each translatable string is included as a 'trans-unit' in the XLIFF file. The last two elements of each translation unit ID are the meta data ID and the application ID. For example, in translation unit with id S-4-885632445599895776-25721, the meta data ID is 885632445599895776 and the application ID is 25721. (S-4 is an internal code signifying that this is a meta data string and corresponds to the text of a tab).

As I've discussed in a recent blog post about saved Interactive Reports, I explained how the internal meta data IDs "shift" or are recalculated when importing an application to a new ID. And this has presented problems for those customers who make use of the translation facilities of Application Express. Because the meta data IDs are a part of the XLIFF translation unit IDs, when those IDs change, the existing XLIFF files for the original application cannot be used against a new version of the application imported elsewhere as a new application ID. What a dead end!

I have authored an APEX application which helps customers overcome this problem. You can run the hosted version of the XLIFF Transformation application which is running in my workspace on apex.oracle.com, or you can download a copy of it and run it on your own APEX 4.0 or later instance. You need to provide 3 things when running this application:

The original XLIFF file
The application ID of the new application
The offset value between the two applications

To compute #3, I'll refer you to this same blog post where I give a couple examples how to determine the offset value.

The logic is really quite simple. After importing the application and installing the supporting objects, only 3 objects will be created - a table named XLIFF_FILES, a trigger on this table, and a small PL/SQL package named XLIFF_TRANSFORM. The PL/SQL package parses the XML file and uses some XDB APIs to replace certain elements of the XML file. By exploiting the native functionality of the database, this was really quite easy to write. For anyone who says the Oracle database is only good for "persisting data", I say smoke this!

This isn't my ideal solution. In a future release of Application Express, I'd like to make it as simple as choosing to include your translations in your application export file, and they move around with you. As the metadata gets transformed on a new import, so do the translations. But until then, this solution can be used.

Where Did My Saved Interactive Reports Go?

2010-07-20T16:27:00.022-04:00

A problem I've seen reported numerous times from customers is that users' saved (or customized) interactive reports are missing after they import a new version of their application. This is a problem we've known about for a while with no adequate remedy. However, given the introduction of the APEX_APPLICATION_INSTALL API in Application Express 4.0, I can offer a solution. Granted, it's not an ideal answer but it's certainly a feasible and supported solution. Firstly, some explanations are in order.

When an APEX application is imported into a workspace, the very first thing that's done is the existing version of the application is completely deleted. All of the meta data associated with the application is deleted - the definition of the pages, the reports on the pages, the templates, the buttons, the branches, the shared components, everything - it's all deleted. Once this is complete, then the application meta data of the APEX application being imported is then inserted. This whole process is atomic - so if an error occurs, the transaction is rolled back and the net effect is no change.

In the case of saved Interactive Reports, it's a little bit different. Imagine you have a production instance running application 645 - you have numerous users who have saved many Customized Interactive Reports. Upon import of a new version of the application, all meta data associated with application 645 is first deleted except the Customized Interactive Reports. In essence, these are left "dangling" until the new application 645 is installed. Once the application import is complete, then the meta data of the Customized Interactive Reports will reference real interactive report definitions again.

But there's a catch. If the application ID changes upon import, then this results in totally new meta data IDs being generated. (This is done in an attempt to prevent collisions of meta data, so you can freely export your application and give to anyone in the world to use on their own APEX instance). A meta data offset number is randomly generated and added to all of the existing IDs. This is done uniformly across all of the application meta data (this is important, and you'll see why shortly). Since the IDs of all of the application meta data have changed, all of your users' customized reports in the previous version of the application are forever left orphaned until they're cleaned up by an internal APEX batch process. Yikes!

Let's look at an example. On apex.oracle.com, I created a simple application with an Interactive Report on the EMP table. I defined this application as application 70000. I then exported this application and imported it back as application 70001.

Using SQL Commands and the APEX Data Dictionary views, I ran the following queries:


select tab_label, tab_id
from APEX_APPLICATION_TABS
where application_id = 70000

tab_label: Emp
tab_id: 1573281607527253166


select tab_label, tab_id
from APEX_APPLICATION_TABS
where application_id = 70001

tab_label: Emp
tab_id: 3146580610985521585

And the difference between the two IDs is 3146580610985521585 - 1573281607527253166 = 1573299003458268419

Let's do this again, but this time, for the APEX data dictionary view for page templates:


select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70000

template_id:  1573270610302252883


select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70001

template_id:  3146569613760521302

If we once again compute the differences between these two IDs, we get: 3146569613760521302 - 1573270610302252883 = 1573299003458268419

This happens to be exactly the difference between the IDs of all of the application meta data, with the exception of the application and page IDs. All of the meta data is consistently "pushed" or offset to a new value.

How is this relevant to missing saved Interactive Reports? Simple. Since we're able to compute the offset which was used between the two applications, if we had a way to ensure that the same offset is used every time upon application import, then there would be no issue with the old saved Interactive Report IDs matching with the newly imported meta data. They would be married again. And how is this done? In Application Express 4.0, there is a new API named APEX_APPLICATION_INSTALL which enables you to control this offset value.

To ensure that I didn't lose the saved Interactive Reports on subsequent imports of application 70000 to application 70001, I included the computed offset before importing this application via SQL*Plus:


begin    
    apex_application_install.set_application_id( p_application_id => 70001 );
    apex_application_install.set_offset( p_offset => 1573299003458268419 );
    --
    -- set the alias so it doesn't collide with the alias from app 70000
    --
    apex_application_install.set_application_alias( 
        'F' || apex_application.get_application_id );  
end;
/


@f70000.sql

That's all there is to it. Note that I didn't have to call apex_application_install.set_workspace_id above, because application 70000 and 70001 are in the same workspace where I performed this test.

As I stated earlier, this isn't the most elegant solution on the planet and most people don't want or need to know about meta data IDs or offsets or any of this complexity. But for those experienced users who are stuck with this problem of losing saved interactive reports when migrating from one application ID to another or across workspaces or instances, this is a supported and feasible solution.

APEX_APPLICATION_INSTALL

2010-07-20T07:55:00.019-04:00

Overview

Oracle Application Express provides two ways to import an application into an Application Express instance:

Upload and installation of an application export file via the Web interface of Application Express.
Execution of the application export file as a SQL script, typically in the command-line utility SQL*Plus

Using the file upload capability of the Web interface of Application Express, developers can import an application with a different application ID, different workspace ID and different parsing schema. But when importing an application via a command-line tool like SQL*Plus, none of these attributes (application ID, workspace ID, parsing schema) can be changed without directly modifying the application export file.

As more and more Application Express customers create applications which are meant to be deployed via command-line utilities or via a non-Web-based installer, they are faced with this challenge of how to import their application into an arbitrary workspace on any APEX instance.

Another common scenario is in training classes, to install an application into 50 different workspaces, all using the same application export file. Today, customers work around this by adding their own global variables to an application export file (never recommended and certainly not supported) and then varying the values of these global variables at installation time. However, this manual modification of the application export file (usually done with a post-export sed or awk script) shouldn't be necessary - and again, not supported.

In Oracle Application Express 4.0, there is a new API available named APEX_APPLICATION_INSTALL. This PL/SQL API provides a number of methods to set application attributes during the Application Express application installation process. All export files in Application Express 4.0 contain references to the values set by the APEX_APPLICATION_INSTALL API. However, the methods in this API will only be used to override the default application installation behavior.

APEX_APPLICATION_INSTALL Summary

Workspace

Used to set and get the workspace ID for the application to be imported. This number can be determined by querying the view APEX_WORKSPACES.

procedure set_workspace_id( p_workspace_id in number );

function get_workspace_id return number;

Application ID

Used to set and get the application ID for the application to be imported. The application ID should either not exist in the instance, or if it does exist in the instance, it must be in the workspace where the application will be imported into. This number must be a positive integer and must not be from the reserved range of Application Express application IDs.

procedure set_application_id( p_application_id in number );

function get_application_id return number;

Generates an available application ID on the instance and sets the application ID in APEX_APPLICATION_INSTALL.

procedure generate_application_id;

Offset

Used to set the offset value during application import. This value is used to ensure that the metadata for the Application Express application definition does not collide with other metadata on the instance. For a new application installation, it's almost always sufficient to call generate_offset to have Application Express generate this offset value for you. This number must be a positive integer.

procedure set_offset( p_offset in number );

function get_offset return number;

procedure generate_offset;

Schema

Used to set the parsing schema ("owner") of the Application Express application. The database user of this schema must already exist, and this schema name must already be mapped to the workspace which will be used to import the application.

procedure set_schema( p_schema in varchar2 );

function get_schema return varchar2;

Name

Sets the application name of the application to be imported.

procedure set_application_name( p_application_name in varchar2 );

function get_application_name return varchar2;

Alias

Sets the application alias of the application to be imported. This will only be used if the application to be imported has an alias specified. An application alias must be unique within a workspace, and it's recommended to be unique within an instance.

procedure set_application_alias( p_application_alias in varchar2 );

function get_application_alias return varchar2;

Image Prefix

Sets the image prefix of the application to be imported. The default can usually be used, as most Application Express instances use the default image prefix of /i/.

procedure set_image_prefix( p_image_prefix in varchar2 );

function get_image_prefix return varchar2;

Proxy

Sets the proxy server attributes of the application to be imported.

procedure set_proxy( p_proxy in varchar2 );

function get_proxy return varchar2;

Clear

Clears all values currently maintained in the APEX_APPLICATION_INSTALL package.

procedure clear_all;

Examples

Using the workspace FRED_DEV on the development instance, you generate an application export of application 645 and save it as file f645.sql. All examples below assume you are connected to SQL*Plus.

To import this application back into the FRED_DEV workspace on the same development instance using the same application ID:


@f645.sql

To import this application back into the FRED_DEV workspace on the same development instance, but using application ID 702:


begin
  apex_application_install.set_application_id( 702);
  apex_application_install.generate_offset;
  apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@645.sql

To import this application back into the FRED_DEV workspace on the same development instance, but using an available application ID generated by Application Express:


begin
  apex_application_install.generate_application_id;
  apex_application_install.generate_offset;
  apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@f645.sql

To import this application into the FRED_PROD workspace on the production instance, using schema FREDDY, and the workspace ID of FRED_DEV and FRED_PROD are different:


declare
    l_workspace_id number;
begin
    select workspace_id into l_workspace_id
      from apex_workspaces
     where workspace = 'FRED_PROD';
    --
    apex_application_install.set_workspace_id( l_workspace_id );
    apex_application_install.generate_offset;
    apex_application_install.set_schema( 'FREDDY' );
    apex_application_install.set_application_alias( 'FREDPROD_APP' );
end;
/

@f645.sql

To import this application into the Training instance for 3 different workspaces (each workspace with their own schema):


declare
    l_workspace_id number;
begin
    select workspace_id into l_workspace_id
      from apex_workspaces
     where workspace = 'TRAINING1';
    --
    apex_application_install.set_workspace_id( l_workspace_id );
    apex_application_install.generate_application_id;
    apex_application_install.generate_offset;
    apex_application_install.set_schema( 'STUDENT1' );
    apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@f645.sql

declare
    l_workspace_id number;
begin
    select workspace_id into l_workspace_id
      from apex_workspaces
     where workspace = 'TRAINING2';
    --
    apex_application_install.set_workspace_id( l_workspace_id );
    apex_application_install.generate_application_id;
    apex_application_install.generate_offset;
    apex_application_install.set_schema( 'STUDENT2' );
    apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@f645.sql

declare
    l_workspace_id number;
begin
    select workspace_id into l_workspace_id
      from apex_workspaces
     where workspace = 'TRAINING3';
    --
    apex_application_install.set_workspace_id( l_workspace_id );
    apex_application_install.generate_application_id;
    apex_application_install.generate_offset;
    apex_application_install.set_schema( 'STUDENT3' );
    apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@f645.sql

Now a final word of caution - with great power comes great responsibility. You should almost never set the offset value yourself unless you absolutely know what you're doing. One of the primary benefits of letting Application Express generate the meta data offset value for you is you avoid the possibility of any "collisions" with the meta data of any other application on any other APEX instance on the planet. If you have no known reason to manually set the offset value, then simply let Application Express set it for you.

In summary, the APEX_APPLICATION_INSTALL API in Application Express 4.0 now enables you to overcome a limitation in all previous versions of Application Express - namely, to take an arbitrary application export file and import it into any workspace on any arbitrary Application Express instance using SQL*Plus or any other command-line tool.

Welcome Mike Hichwa to the Blogosphere!

2010-07-02T15:41:00.005-04:00

My manager since 1999, Mike Hichwa, has finally decided to join the blogosphere. Mike is the visionary and driving force behind Oracle Application Express and has been since he authored the very first line of APEX (then called "Flows") in 1999. Mike is the Vice President of Database Tools and is responsible for Application Express, SQL Developer, .NET tools, Java in the database, and others.

Language Preference of the Application Development Environment in APEX 4.0

2010-06-30T07:29:00.004-04:00

A customer filed a Service Request with Oracle Support, asking the following:

In Apex 4, Irrespective of the Client OS / Locale / Browser Language settings, the Language picked up the interface shows as “ Language: en “ . Where as from the same browser/client if I login into Apex.3.2.1, it shows the correct language as "DE" , "TH", "FR", etc.,

Has anybody seen this issue? Am I missing something?

This is expected behavior. In Application Express 4.0, the development environment uses the new "Session" language derivation, bringing it in line with modern Web applications which do not use the browser language as the primary language preference. This new language derivation type is documented briefly here and additionally in the item-level help.

If you go to the Login page of apex.oracle.com, you’ll see the language bar at the bottom. This language list is determined by the translated versions of Application Express which are installed in the instance. You can change your language preference from this language bar or within Application Express too. The language preference is "remembered" via a persistent cookie.

Oracle Application Express 4.0 is released

2010-06-22T15:59:00.010-04:00

Oracle Application Express 4.0 is now available for download from the Oracle Technology Network here. What began in 2008 has culminated in the most ambitious release of Oracle Application Express since the very first version of Oracle HTML DB in 2004. Major enhancements include:

Websheets
Dynamic Actions
Plug-Ins
Team Development
Improved Charts, including Maps and Gantt Charts
Support for RESTful Web Services
Enhanced Interactive Reports
Dramatically improved themes/templates
Tabular form validations
Dynamic calendars
and many more...

You can read about the new features of Application Express 4.0 here.

If you wish to kick the tires of Oracle Application Express 4.0, feel free to sign up for a free workspace at http://apex.oracle.com. We're currently averaging around 600 new workspaces per week on apex.oracle.com, so the interest in the APEX Community is growing every day.

Our thanks go out to the thousands of customers who participated in the three different Early Adopter releases since December 2009. Your feedback, bug reports and suggestions have all made this a far better release in terms of quality and functionality.

apex.oracle.com upgraded to APEX 4.0, version 4.0.0.00.46

2010-06-18T23:05:00.000-04:00

Tonight, I patched apex.oracle.com to version 4.0.0.00.46. This is our proposed final release candidate of the software.

All translated versions have been installed too, which includes German, Spanish, French, Italian, Japanese, Korean, Brazilian Portuguese, Simplified Chinese and Traditional Chinese. Unlike previous releases of Application Express, the language for the Application Builder is no longer determined by your browser language setting. You can simply choose your language from the Login page or from within the home page in Application Express (after you login).

It has proven extraordinarily useful to upgrade apex.oracle.com and gather the feedback prior to release. There were a number of bugs which were uncovered and fixed that would have otherwise been missed. Thanks to all who reported any issues.

Oracle Application Express and the Corporate IT Architect - Part I

2010-06-17T15:32:00.000-04:00

A few weeks ago, I attended an event in Columbus, Ohio called "IT Martini Hour 10: Agile of All Trades", sponsored by IT Martini. It's a very well organized event, gathering a few hundred people from the local IT community. I also was interested in going because a friend of mine from Pacejet was part of a discussion panel, hosted by Amazon Web Services. We've had such smashing success with Amazon Web Services in the hosting of http://tryapexnow.com, I just simply wanted to meet them and thank them.

During this event, I ran into a gentleman I worked with 20 years ago, where he and I were both in the same product development organization. This gentleman, "Mr. G", was a smart guy 20 years ago and is a smart guy today. Today, "Mr. G" works for a large financial institution where he is an architect for corporate IT. After glancing at my name tag which said "Oracle", he asked what I was doing and I gave him the 20-second story of Oracle Application Express. I even went so far as to say that his own company is running over 100 internal applications, all built with Oracle Application Express. I was expecting a positive reaction. That's not what I got.

In a rather abrupt and candid response, "Mr. G" said that he learned of Application Express just a couple weeks ago. He said it was stupid. It's RAD and RAD is bad. It's for "quick and dirty applications that don't scale". When I told him what organization was a primary user of APEX in his company, he referred to them as "the business users", and of course they'd use APEX. He wasn't surprised. They tend to use one of everything, whether or not it's the IT standard. He went on - a problem with APEX, like other frameworks, it's that it's a black box, and when there's a problem with the black box, you're stuck. Of course, I asked him if he doesn't use any framework, since presumably all frameworks are bad - but of course they do.

Another problem he had - he said I'd be surprised if I looked at what crappy systems the "business users" run these systems on. Then, when they fail, it becomes an IT problem. I had to point out that this wasn't a criticism of Application Express but a flaw within their organization.

So I asked him what alternatives are provided by "corporate IT". He said they have a bundle of software, some of it open-source, that is the official development platform distributed amongst the various businesses, which corporate IT can support. When I asked him if this can be used by the "business users", of course he said no. To which I concluded that he'd rather have the business users not solve their problems.

This went on for a few more minutes until the beer and testosterone kicked in. I finally blurted out how he was arriving at conclusions based upon no knowledge or experience. Surprisingly, "Mr. G." agreed. I offered to come to his place of business and give him a personal demonstration, being as honest and forthcoming as I can be about the positioning of Application Express and its strengths and weaknesses.

When the dust of Oracle Application Express 4.0 settles, I'll be visiting him.

Oracle Application Express 3.2: The Essentials and More

2010-06-07T22:32:00.008-04:00

Last week, Packt Publishing shipped the book Oracle Application Express 3.2: The Essentials and More. This book is authored by my friend Arie Geller and Matthew Lyon. Many people have been helped by Arie on the Application Express discussion forum on OTN for years, and I can easily say that Arie is one of the foremost experts in the world on creating multi-lingual and globalized applications in Oracle Application Express. I've even learned a few things from Arie about properly developing applications which support RTL (right-to-left) languages like Hebrew and Arabic.

apex.oracle.com upgraded to Oracle Application Express 4.0

2010-06-04T20:50:00.009-04:00

Oracle Application Express at http://apex.oracle.com has been upgraded to a pre-production version of Oracle Application Express 4.0. There's nothing like upgrading an instance of 8,000 workspaces and 31,000 applications to ensure that the upgrade works perfectly and what used to work in Application Express 3.2 continues to run flawlessly

As most users in the APEX Community have come to realize, this is one of the final milestones in our development cycle before release. So we're getting close. You can read about what's new in Application Express 4.0 here.

The participation of the thousands of people in the Early Adopter program since December has been overwhelming. We are grateful for all of the work and feedback and suggestions and bugs reported by everyone. The APEX Community is alive and well...and growing!

Application Express 4.0 Early Adopter Phase 3 is now available

2010-05-10T13:30:00.003-04:00

The third (and hopefully final phase) of Oracle Application Express 4.0 Early Adopter is now available. A new instance with the latest build of Application Express 4.0 is now available on http://tryapexnow.com.

If you had used the second Application Express 4.0 Early Adopter instance, your workspace and schema have not been migrated to this instance. You will need to sign up for a workspace but you shouldn't have to take the survey again. The original Application Express 4.0 Early Adopter 2 instance is available at http://184.73.244.154/apex, and it will be available for the next couple of weeks.

Thanks to everyone who has participated and provided feedback, as the feedback and suggestions have been invaluable as always.

Oracle Learning Library

2010-04-06T09:12:00.003-04:00

Marcie Young, from Curriculum Development, has just released the Oracle Learning Library. This is an application which she developed on apex.oracle.com and is a directory listing of free online training from Oracle - Oracle By Examples, Tutorials and Demonstrations. This application lets you search for free training covering numerous product and functional categories. Product categories include Database, Beehive, Enterprise Linux, Enterprise Manager, Fusion Middleware, JDeveloper and Oracle VM.

Memorials for Scott Spadafore

2010-03-30T08:50:00.009-04:00

There will be two memorials for our dear friend, Scott Spadafore:

Saturday, April 10, 2010 at 2:00P PDT
Clovis Masonic Lodge
375 5th Street (at DeWitt)
Clovis, CA 93612

+1.559.299.4992

and

Saturday, May 1, 2010 at 2:00P PDT
Santa Cruz Masonic Lodge
828 N. Branciforte Avenue
Santa Cruz, CA 95062

+1.831.423.1530

In lieu of flowers, donations may be made to a college fund for Scott’s daughters, Blaire and Keely. Checks should be payable to:

SCOTT SPADAFORE MEMORIAL FUND

and donations can be sent to:

SSMF
PMB#3
1187 N. Willow Avenue, #103
Clovis, CA 93612

Scott Spadafore

2010-03-22T10:29:00.003-04:00

I am quite saddened to say that our friend, team mate and long time contributor to Oracle Application Express, Scott Spadafore, died last night of a heart attack.

Scott has made extraordinary contributions to Oracle Application Express since he joined our team in 2001. Scott also worked tirelessly for many years on the Application Express discussion forum on OTN, where he helped (literally) thousands of customers to become successful with Application Express. His patience and precision were to be admired.

I ask that you pray for Scott's wife, Gail, and their two daughters, at this difficult time.

Application Express 4.0 Early Adopter Phase II is available

2010-02-26T16:21:00.004-05:00

The second phase of the Application Express 4.0 Early Adopter is now available. A new instance with the latest build of Application Express 4.0 is now available on http://tryapexnow.com.

This database was created with database character set AL32UTF8 (we're still a little confused by the Amazon Machine Images from Oracle aren't already AL32UTF8.
The Websheets feature is now available. The security of Websheet applications still needs to be refined, but the functionality and ease of creation of these types of applications is amazing.
The new tree region makes its debut.
Collections now support NUMBER and DATE
apex_collection.create_collection_from_query_b and apex_collection.create_collection_from_queryb2 support bind variables and row limits
Debugging and logging has been completely rewritten - no longer is debug output emitted within the page, but it's collected and can be viewed later (and also queried via views).
You won't be able to see this, but the tablespaces / data files of provisioned workspaces will now autoextend
There have been many improvements in the Plug-In infrastructure, which Patrick will surely expand upon.
A Migration Assistant has been created, which lets you upgrade all of the elements of your pre APEX 4.0 application in one place.
Native validations and declarative validations have also been rewritten and improved (again, Patrick will expand upon this).
All of the item types have been consolidated (so you no longer see 1,000 different Date Picker types in your item type select list, as well as other item types)
A large number of bugs and usability issues have been corrected, thanks in a large part to the tremendous amount of feedback we received.

This isn't an exhaustive list. The updated list of new features will be available on tryapexnow.com.

If you had used the first Application Express 4.0 Early Adopter instance, your workspace and schema have not been migrated to this instance. You will need to sign up for a workspace but you shouldn't have to take the survey again. The original Application Express 4.0 Early Adopter instance is available at http://184.73.244.154/apex, and it will be available for the next couple of weeks.

Thanks to everyone who has participated and provided feedback, as the feedback and suggestions have been invaluable.

FamZoo goes live!

2010-02-24T09:18:00.015-05:00

Nine years ago, when Oracle started down the path of turning Application Express (nee HTML DB) into a real, supported product that was shipped with the Oracle database, we had the good fortune of being mentored and led by Bill Dwight. Bill was a hands-on Vice President in the Oracle Tools division and he led the development of JDeveloper, Developer, Forms, Designer, and iLearning. Bill had a lot of experience and success at Oracle, but also he had the right kind of savvy to guide us from a "project" to a product. He imparted a lot of sound advice that still helps us today.

Fast forward to today, and Bill Dwight is the founder and CEO of FamZoo, Inc. In a nutshell, FamZoo is "a family-friendly web site that helps parents teach their children the practical skills they’ll need to thrive in the real world." FamZoo the company, and FamZoo the Web site have been under development and in private beta for a few years, but as of January 21, 2010, FamZoo is now open for business to the public. As you navigate around the FamZoo site, you'll see the tell-tale signature of Application Express in the URL. There is an extensive FamZoo tour also available.

FamZoo has been an interesting case-study for Oracle Application Express, in that:

They made the conscious decision to write the customer-facing portions of FamZoo in lovingly hand-crafted HTML / JavaScript / AJAX. According to Bill, this was important because "our consumer facing UI is very, very custom and particular - a key part of our brand."
The internal administration and dashboard pages of FamZoo were written using the traditional wizards and declarative APEX infrastructure.
Even with the very custom customer-facing UI, they still capitalize on other benefits of the Application Express framework, including security, session state, page templates, etc.
With the exception of one local APEX instance for upgrade testing, the development and deployment of FamZoo has been done on commercial, hosted instances of Application Express from two different hosting providers. Not only was this extraordinarily cost effective, but it let the FamZoo team focus on building their business and their brand, and not have to worry about database and Web site administration, backups, power, etc.

The user interface of FamZoo is really superb and the overall flow and navigation of the site is elegant. To develop this kind of site takes a lot of time and effort and careful planning, regardless of the development framework that is chosen. I'm just happy they chose to go the Application Express route.

Making apex.oracle.com fast (again)

2010-02-08T14:20:00.033-05:00

For the latter part of 2009, the performance on apex.oracle.com was lacking at times, to put it kindly. As evidenced by this long-running thread on the APEX discussion forum on OTN, performance issues started cropping up in August and things only got worse as the year went on. Ben Burrell, a very frequent contributor on the APEX discussion forum said it best with:

"The dedication and patience shown by the contributors to this forum is nothing short of incredible. Oracle should be magnificently proud of the APEX community - and reward it with a decent service."

I wholeheartedly agree! The least Oracle can do for the army of APEX experts who graciously give of their time to help others with Application Express is provide them a usable service. Additionally, for those users of Application Express who sign up for a test account on apex.oracle.com, a first impression is everything. Even if the cause of the issue is not "an APEX problem", per se, it will always have the perception that APEX is slow and cannot scale.

This has been a learning experience for me, and I'm simply here to report a number of the issues uncovered and the steps we took to remedy them.

1) KeepAlive - In November 2009, I was monitoring the database listener log files. There were a very large number of incoming connection requests to the database listener - in some instances, on the order of 30 connection requests per second. One of the most expensive actions you can take is to establish a new database session, hence, why session pooling is so commonly used. As it turns out, an administrator had turned off the KeepAlive setting in Oracle HTTP Server. Every new APEX page view request had to establish a new database session. For a site that gets millions of page views a day, this is not good. I had them turn KeepAlive back to On and with a KeepAliveTimeout of 6 seconds. As busy as apex.oracle.com is today, we now only average about one new database session request every 10 to 15 seconds.

2) robots.txt - The robots.txt file is supposed to be used by Web crawlers and search engines, although not all of them seem to observe it. This file enables a Web site administrator to control when Web crawlers are allowed to crawl a site and how frequently. The "window" of time that we permitted Web crawlers was too great - that's the last thing we want is to have some new user of Application Express competing for the same resources with some Web crawler which is hurling requests at our site. So I reduced the open window for Web crawlers: http://apex.oracle.com/robots.txt.

3) Number of Database Access Descriptors - Since the days of the original 'marvel.oracle.com', the Database Access Descriptor used for the site was named 'otn'. Earlier this year, I wanted to have apex.oracle.com support the documented and standard Database Access Descriptor named 'apex', so we added that one to our configuration. Then, with the migration of AskTom to the apex.oracle.com instance, we created a third Database Access Descriptor named 'asktom' (otherwise, we would have broken any bookmarks to Tom's questions and answers).

The Web server software used by Oracle Global IT on this instance is Oracle Application Server 10.1.2. Unfortunately, Oracle HTTP Server and mod_plsql do not support true connection pooling in this version (see the documentation for the explanation). Each HTTP Server process would establish and maintain a database connection per DAD - that's three database connections times the number of HTTP Server processes. There are two Web servers that front-end apex.oracle.com - so 2 Web servers * 3 connections per Oracle HTTP Server process * 200 - 250 HTTP Server processes - and we easily had more than 1,000 database sessions during peak times. Even if only a handful of these sessions were active at any given time, the memory consumed by all of these sessions was too large. So large, that the system log was recording messages like "Out of Memory: Killed process 31088 (oracle)" every few minutes. Not good.

With the help of Kris Rice, we were able to remove the 'asktom' Database Access Descriptor and replace it with the following rewrite directives in httpd.conf:

RewriteEngine On
RewriteCond %{THE_REQUEST} ^.*/pls/asktom.* [NC]
RewriteRule ^.*/pls/asktom(.*)$ /pls/apex$1 [NC,QSA,PT]

With this one change, the database server wasn't swapping memory and the OS was not randomly killing processes because it was out of memory. The next day, Tom even commented how snappy it was.

4) File System Caching - The system administrators reviewed the network performance and I/O systems. They changed the "noac" and "actimeo" parameters so that the system would cache file system parameters, thus reducing the I/O on the system. Remember that this was a database with over 15,000 tablespaces and datafiles - that's a lot of potential I/O savings. Don't ask me for any more detail on this one, though - it's beyond my domain of expertise.

5) Purge of Stale Workspaces - There is no built-in workspace purging process in Application Express. The way the process has worked over the past 3 or 4 year is I personally would run a hand-crafted collection of custom PL/SQL programs which identified the workspaces that have not been touched in the last 45 days, notify the administrators of these workspaces via e-mail that their workspace was going to be purged, let them retain the workspace if they wished, and then after a two-week period - go through and purge the ones which hadn't been retained.

The problem with this solution is that apex.oracle.com would go through these peaks and valleys - dropping to 7,000 workspaces and then over time, creep up to 17,000 workspaces. I would manually run the purge process a year later, bring it back down to 8,000 workspaces, and then the process starts all over again. The system would be fastest after the purge and then slowly degrade over time.

I rewrote the purge process from scratch, but this time, I designed it to run perpetually (I have to give credit to my manager, Mike Hichwa, for this idea). The parameters are roughly the same - every night, it wakes up, computes who hasn't used their workspace in the past N days and isn't already on "the list", sends the administrators an e-mail, gives them the chance to retain their workspace, sends a reminder 3 days in advance of the purge, and and then after a total of 10 days, purges the workspace. Purging the workspace involves dropping the tablespace & data file, dropping the database user corresponding to the workspace schema, and deleting the workspace and all associated meta data. The great thing is this runs every day - I don't have to kick it off and monitor it, I just get daily e-mails about the status and statistics.

Since the first workspace was purged by this process on January 18, 2010, this perpetual purge process has been used to remove 5,998 workspaces and 5,860 tablespaces, reclaiming 64,531,415,040 bytes from the file system. And now apex.oracle.com is on auto-pilot.

My manager, Mike Hichwa, also suggested I simply fold this feature into Application Express 4.0 - so Application Express can become a self-maintaining system. I'm not convinced this feature would be used by that many customers outside of apex.oracle.com. I tend to believe DBA's and our customers would rarely want to automatically drop data.

6) Memory - This past Saturday, February 6, 2010, the memory on apex.oracle.com was upgraded from 16 GB to 32 GB. Since then, the machine is really very, very fast. I've monitored the system load and it is consistently running at about 50% of maximum capacity, even during peak times.

Going forward, I am going to request that we upgrade the database to version 11.2.0.1. Additionally, I am going to request an upgrade to Oracle HTTP Server which supports true connection pooling and which should dramatically reduce the number of overall database sessions to the database server, itself. If the upgrade to Oracle HTTP Server can't be done in a reasonable time frame, then I'm going to try to setup server-side pooling via Database Resident Connection Pooling. Even though I always advise against multiplexing multiplexed connections, I can't really describe the connection pooling on our version of Oracle HTTP Server today as "multiplexed."

If I were in charge of this site, I would have done these upgrades yesterday. But probably like the company you're at, our IT organization that actually runs this site has their software levels and it takes time to uptake new versions and roll it out. I'm not complaining, as I understand and appreciate this when you have to manage hundreds of databases and Web applications, as they do.

Contrary to some assertions, the performance of apex.oracle.com was of great concern to us and to me personally. However, you cannot fix a problem until you have diagnosed the actual cause of the issue. And unfortunately, that takes time. Additionally, some of these steps took a great deal of time to actually implement and test before rolling out on apex.oracle.com. As much as I would have liked to flip the "fast=true" bit, that wasn't reality. This has been a learning process and one which will ultimately benefit the users of apex.oracle.com going forward as well as the product, Oracle Application Express. I am grateful to those customers and members of the APEX community for their patience during this time. To paraphrase Frank Costanza, "We're back, baby!"

The Perils of Modifying the Application Express Metadata

2010-01-27T11:28:00.006-05:00

Last week, Oracle Support contacted me about an escalated Service Request they received from a customer. This customer, who was running Application Express 3.2, was unable to take an application export file from their production environment and import it into their development environment. During import, it would fail with:

ORA-20001: GET_BLOCK Error. ORA-20001: Execution of the statement was unsuccessful. ORA-00001: unique constraint (APEX_030200.WWV_FLOW_WORKSHEET_RPTS_UK) violated

I received a copy of this application export file, uploaded it to apex.oracle.com, and immediately ran into the same error when I attempted to install it into my workspace. Unfortunately, the customer was in a situation where they could no longer import this application into any other workspace or instance. It appeared that the generated export file and metadata on their production instance was corrupted.

How did they possibly get into this state? Well, it was a sequence of:

The customer was first directed to David's blog posting at http://dpeake.blogspot.com/2009/01/preserving-user-saved-interactive.html.
That was unsatisfactory, so they followed a link to Martin's blog posting at: http://apex-smb.blogspot.com/2009/10/saving-saved-interactive-reports-when.html
Then, not based on any information in either of these blog postings, the customer dropped a unique index and disabled two foreign key constraints from the APEX_030200 schema.
Finally, via some manual DML, it appears that the customer was able to modify the metadata of saved Interactive Reports such that they now violated the conditions of this unique constraint.

While I completely understand why the customer went down the path that they did (to restore their end user's Interactive Reports), they should have never resorted to manually modifying the metadata and database objects in the Application Express schema. Fortunately, I was able to easily reproduce the customer's problem and was able to craft a custom patch script for their environment to restore what had been done to it.

Did the customer perform an action which was unsupported by Oracle Support which resulted in the corruption of their environment? Yes. Did the customer have any recourse? No, not really. Was Oracle Support obligated to help this customer recover? Maybe not. But even though they knowingly did this, I didn't want to have one unhappy Oracle and APEX customer when we were done.

In general, Oracle Support will make every effort to help a customer with their Oracle Application Express product issues. But in cases like this one, where the customer performed actions directly against the undocumented, internal Application Express schema, there really aren't any guarantees that Oracle Support nor Application Express product development will be able to help a customer recover from this type of corruption.

Typical unsupported actions which can lead to corruption include dropping users, revoking privileges, dropping/modifying constraints, inserting/updating/deleting data, etc.. This situation is really no different than if a customer dropped SYS.STANDARD from their database or dropped internal packages or views from their eBusiness Suite environment.

Moral of the story: When you're considering to directly modify any objects in the Application Express schema, it's in your best interest to abstain.

Application Express 4.0 Early Adopter Refresh is coming

2010-01-25T09:02:00.004-05:00

The Application Express 4.0 Early Adopter's program at http://tryapexnow.com has been a smashing success, so far. 2,440 workspaces have been requested and created on this instance, and a plethora of feedback has been received. Our customers have reported both bugs and made numerous feature suggestions, some of which you will see implemented in Application Express 4.0 production. So thank you for this tremendous amount of participation - this is further proof that Application Express has such a vibrant community.

In the coming weeks, we will be refreshing the Application Express 4.0 Early Adopter's Instance with an updated build of Application Express 4.0. One of the significant features to be enabled in this build will be Oracle Application Express Websheets.

Although it has been our intention to preserve the existing workspaces when we install the new Application Express 4.0 environment, the odds are about 97% that you will need to sign up for an entirely new workspace and schema - that is, nothing from the current Early Adopter's will be preserved. An application export *may* work in the updated instance, but that is not guaranteed either. Certainly, though, all of the submitted feedback from our customers will be preserved and available. We'll provide advance notice of at least a couple weeks before this refresh (really, "rebuild") of the environment will take place. And this new environment will happen in a database with an AL32UTF8 character set.

Thank you for your many contributions to the success of Application Express 4.0.

OTN Developer Day in the Big Apple

2010-01-06T11:29:00.005-05:00

For those of you in or near New York City, next week Wednesday, January 13, 2010, at the New York Marriot Marquis, there is an Oracle-sponsored OTN Developer Day- Hands-on Oracle Database 11g Applications Development. There are four different tracks, and one of the tracks is dedicated to Oracle Application Express. The other three tracks are for Database Development, Java and .NET.

This event is free. Also, you should bring your own laptop for the labs throughout the day. You'll walk away with a virtual machine containing Oracle Database 11gR2 with Oracle Application Express 3.2.1 installed. Lastly, this will be a good time to meet with several members of the Oracle Application Express development team, including Mike Hichwa (our VP), Marc Sewtz, David Peake and Christina Cho.

A few notes about the Application Express 4.0 Early Adopter

2009-12-20T22:50:00.002-05:00

The database character set is WE8MSWIN1252. When we create the Early Adopter 2 instance of APEX 4.0 (hopefully in January, 2010), the database character set will be AL32UTF8. Unfortunately, today you may experience some character conversion errors if you attempt to use characters outside of the character set (e.g., Chinese, Japanese, Hebrew, Arabic, etc.).
We will notify people in advance, at least a week, when we will install APEX 4.0 Early Adopter 2.
When APEX 4.0 Early Adopter 2 is installed, it is our intention to preserve the workspace definitions, your workspace credentials, and also your schema (objects and data). However, any APEX applications will be purged. You may be able to export the applications beforehand and import them into APEX 4.0 EA2, but that's not guaranteed.
APEX 4.0 Early Adopter 2 will add Websheets in addition to better support for Internet Explorer, among other things.

Moving table data from SQL Developer into Application Express using XML

2009-12-10T15:19:00.017-05:00

Gary, an Application Express customer for many years, recently sent me a message with a problem he was having getting table data, exported from Oracle SQL Developer in XML format, into Oracle Application Express. He pointed me to his post on the Oracle Application Express discussion forum on OTN.

Using the EMP table as an example, he showed me that the data he exported from SQL Developer looked something like:

He originally thought that Application Express was choking on the CDATA values. It isn't that Application Express didn't like the CDATA elements, it's just that they are two completely different XML formats. Underneath the hood, Application Express uses DBMS_XMLSAVE.INSERTXML for the XML loading, and it uses the default ROWSET and ROW tags. SQL Developer, for whatever reason, uses a different format. Maybe there's a way to control this in SQL Developer, but I'm not aware of it.

So how do we transform the XML from SQL Developer into something compatible with the standard ROWSET format used by Application Express? Well...this is exactly what XSLT was designed for. I'm no XSLT expert, but I learned enough from Carl's examples to accomplish this type of simple transformation. You can use any XSLT engine to do this type of transformation, but I chose to use Microsoft's command-line utility, msxsl, to do this.

1) Before I did anything else, I had to re-generate the XML from SQL Developer so the DATA type data values would be in the canonical date format used by Application Express XML data loading. To do this, from the SQL Worksheet in SQL Developer, I issued the statement:

alter session set nls_date_format = 'rrrr-MM-dd"T"HH24:mi:ss."000"'

And then exported the data from SQL Developer to XML again, which now gave me:

2) mxsxl did not like the encoding the SQL Developer included in my XML file (it will give an error like "System does not support the specified encoding.") So I edited the XML file from SQL Developer and changed:

encoding='Cp1252'

to

encoding='utf-8'

In my case, this didn't present a problem, as I have all ASCII characters in my XML file.

3) Then, I created a text file named 'emp.xslt' with the following contents:

4) Finally, using the command-line in Windows, I simply issued:

msxsl emp.xml emp.xslt

And voilà! I was able to take:

And turn it into:




7839
KING
PRESIDENT

1981-11-17T00:00:00.000
5000

10


7934
MILLER
CLERK
7782
1982-01-23T00:00:00.000
1300

10

ready for loading into Application Express, and all with just a little bit of XSLT.

I haven't tested what is output from SQL Developer for CLOBs and BLOBs, so I can't speak for this solution for those types. But for the most common data types, this simple transformation can work quite easily.

(ā'pěks)

2009-11-12T16:27:00.004-05:00

At the ODTUG APEXposed conference in Atlanta this week, a customer asked me to blog about how to abbreviate and pronounce the abbreviation for Oracle Application Express. I've seen numerous abbreviations (Apex, ApEx, APPEx, AppEx, APEX), and I think the lack of a formal abbreviation lends to the confusion in pronunciation. So here goes:

Whenever an abbreviation is used in informal writing, it's abbreviated in all uppercase: APEX
It's pronounced (ā'pěks). It's a long 'A' as in 'acorn'.
In formal written materials from Oracle, it is always written as 'Oracle Application Express' and never abbreviated.

In conversation and in some presentation materials, you will see the Oracle Application Express team use the above spelling and pronunciation.

Who uses asktom.oracle.com?

2009-11-02T21:15:00.007-05:00

I have dedicated a couple posts to describe who is using apex.oracle.com. I have focused on the scalability of the Application Express environment on relatively cheap hardware. And towards the end of September, we moved asktom.oracle.com to apex.oracle.com. So why not combine the best of these topics and post about who is using asktom.oracle.com?

AskTom officially moved to apex.oracle.com on 18-SEP-2009. I turned on Google Analytics a day or two later. So my snapshot of asktom.oracle.com covers the period from September 20, 2009 through November 2, 2009 - not quite a month and a half.

Some interesting statistics:

There were 517,599 "visits" and 1,005,189 page views.
78% of the page views were from hits from search engine results pages (thanks for the clarification, John Scott).
53% of the browsers are Internet Explorer - a number that I suspect gradually declines over time
People found the site searching for 'ORA-00604' more than they found it by searching for 'tom kyte'. As a shareholder of Oracle, this statistic concerns me.
The lion's share of visits comes from the United States, followed by India, United Kingdom, Germany and Canada.

All in all, though, I'm quite happy with these numbers, and how it shows that the existing hardware on apex.oracle.com and this large database (with over 10,000 workspaces/users/tablespaces) was able to absorb the additional load with no problem.

asktom.oracle.com Google Analytics - 20091102

Certification Exam for Oracle Application Express

2009-10-21T22:35:00.003-04:00

Oracle is in the process of preparing a certification exam for Oracle Application Express. Passing this exam will qualify someone as an "Oracle Application Express Developer Certified Expert." This certification is intended for intermediate to advanced APEX Developers who have at least 6 months experience using Oracle Application Express.

Participants for the very first pilot exam are being recruited now. Keep in mind - the feedback and recommendations from the participants of this exam will help shape this course, so some feedback will be expected. More information can be found at:

http://www.oracle.com/goto/apexpilot

Going to Oracle OpenWorld 2009?

2009-10-09T09:46:00.003-04:00

Are you going to Oracle OpenWorld 2009? If so, I encourage you to look us up. The primary Application Express location will be in the DemoGrounds in Moscone West, stations W1, W2 and W3. As you walk into the DemoGrounds, this will be in the front on the left side. David has a map here.

A large majority of the Application Express product development team will be there, including Mike, Christina, David, Marc, Scott, Jason, Patrick, Hilary and Anthony. This is always a very rewarding event for us, as we get to learn directly from our users how they're using Application Express and what they want in the future. We'll also give you a demonstration of what is coming in the future.

Please stop by - we would love to meet you.

AnyChart AnyMap updated in Application Express 3.2.1

2009-09-18T12:57:00.006-04:00

As John Allen pointed out in the APEX discussion forum on OTN, when using the recently bundled and licensed AnyMap files with Application Express 3.2.1, a license message of "Your license does not include the AnyMap extension" was displayed on the rendered map.

This problem is now corrected in Application Express 3.2.1. The full download from OTN has been updated with this new file as well as the Application Express 3.2.1 patch set on Oracle MetaLink (Patch Number 8548651). The file that has been updated is /images/flashchart/swf/AnyChart.swf, and this would need to be replaced in your current images directory for Application Express 3.2.1. This has also been corrected on http://apex.oracle.com.

If you have already installed/upgraded to Application Express 3.2.1 and you want to get this new file:

Download the Application Express 3.2.1 patch set from MetaLink and apply it to your Application Express 3.2.1 instance. If you're running the XDB HTTP Protocol Server/embedded PL/SQL Gateway, all you'll really need to do is run the SQL script apxldimg.sql to restage all of the files in the /images/ directory in XDB. If you're using Oracle HTTP Server, then you can just simply copy the updated /images/flaschart/swf/AnyChart.swf file to your existing /images/ directory.
If you don't have access to MetaLink, then you'll need to download the full Application Express 3.2.1 distribution and put this new AnyChart.swf file in place - either in XDB or in your existing /images/ directory.

Unfortunately, Oracle Database 11gR2 11.2.0.1 will continue to ship with this previous AnyChart.swf file, and if you wish to use the maps or Gantt charts, you'll need to download the Application Express 3.2.1 patch set from Oracle MetaLink and apply it.

There is no declarative interface in Application Express 3.2.1 to create maps or Gantt charts. You'll need to understand their respective APIs to be able to use these components in your Application Express application. But the maps and Gantt charts are included in the license for Application Express 3.2.1. The declarative interface to maps and Gantt charts is coming in Application Express 4.0, and you'll be able to see this in action at Oracle Open World 2009.

Who uses apex.oracle.com (an update)

2009-09-18T10:40:00.008-04:00

In February 2008, I posted "Who uses apex.oracle.com?". I thought it was time for a quick update.

I have left the same Google Analytics code in place. And keep in mind - this has only been tracking users who hit the login page on apex.oracle.com and nowhere else - not within Application Express after logging in, not those users who go directly to Carl Backstrom's or Denes Kubicek's or Mark Lancaster's sample applications, nor those users who go directly to ProMED Mail. It only tracks those who go to the login page.

apex.oracle.com Google Analytics - 20090918

So what can we conclude?

United States still holds the top spot - not surprisingly since we tend to talk about APEX a little more (as a lot of us live and work in the U.S.)
India has surpassed the United Kingdom
Colombia shot up from 22 to 9.
Romania moved from 8th to 4th - a strong showing.
There were previously 122 distinct countries that touched apex.oracle.com. Now it's up to 152 distinct countries.
Some dude in New Caledonia checked out Application Express and never came back.

In hindsight, I should have left this Analytics JavaScript code in place in the System Message itself, so I could track who actually logs in and uses Application Express - I'll add it back now. But this explains why you see a difference with statistics from February 2008 with the Pages/Visit and Average Time on Site - I've only been tracking the login page.

Oracle Multimedia DICOM demonstration application available

2009-09-18T09:37:00.004-04:00

I won't pretend to be an expert in DICOM - Digital Imaging and Communications in Medicine. But I do know that the Oracle Multimedia team are experts in this field, and they recently released a new packaged application which demonstrates the use of DICOM Medical Image Support in the Oracle Database 11g, all from within an Application Express application. This just further illustrates my point that Oracle Application Express, instead of insulating you from the database via multiple layers and stacks and tiers, enables you to readily exploit the native functionality of the Oracle Database, like Oracle Multimedia and the DICOM features.

From Melli, the Oracle Multimedia Product Manager:

The DICOM sample application shows how the database can be used to store and manage DICOM medical images and illustrates how quickly Oracle technology (DICOM functionality and Application Express) can be used to build an archive to manage medical images. With this archive application, clinicians can easily browse the images, share images with other clinicians, and search images.

The DICOM sample application is demonstrated in a browser. Because a DICOM viewer is not available, full-size JPEG images and JPEG thumbnail images are created using Oracle Multimedia DICOM features. These images are stored in a main archive table, along with the original DICOM images. Additionally, all the meta data from the DICOM images is extracted from the DICOM images to enable searching based on DICOM meta data.

The DICOM Image Archive Demonstration application can be downloaded from the Oracle Application Express Packaged Applications. You can also preview the DICOM Image Archive Demonstration as well as the DICOM Image Archive Administration applications, all running on (you guessed it) apex.oracle.com.

Ask Tom moved to apex.oracle.com

2009-09-18T09:01:00.004-04:00

For those who are familiar with Tom Kyte and his site Ask Tom, this is now running on apex.oracle.com. Don't believe me? Then compare http://asktom.oracle.com and http://apex.oracle.com/pls/otn/f?p=100:1.

This turned out to be a fairly painless exercise. I actually moved the application about 4 months ago, created the workspace, created the ASKTOM database user, did an initial database import of his database objects and data, imported his application, changed all of the references to images and CSS's in a custom /i/asktom_i directory to be Shared Components of the application, and then worked with Global IT to setup the proper definition of this new Virtual Host and other Web Server configuration. Then it was just a simple matter of timing, to coordinate the DNS switch (which Global IT handled) and the Data Pump export and import (which Tom handled - I tried to think of someone more qualified and could not).

The switch from the old server to the new application on apex.oracle.com happened yesterday at 16:48P EDT (GMT - 04:00). Since then, roughly 16 hours ago, we've had 24,701 page views on the AskTom application. And Tom went so far as to describe the performance as "snappy".

I warned Tom that there is an ongoing issue with some users and the network performance to apex.oracle.com, and that the new AskTom would be inheriting this issue, but so far, no issues have been reported. And for the person who will undoubtedly call me out and say "apex.oracle.com is not intended for production applications", that is a correct statement - if you're not ProMED or AskTom and you don't work in my group, consider it only your playground and demonstration instance.

Minimum database version for Application Express 4.0

2009-09-10T13:38:00.003-04:00

Today, Anthony Rayner sent me a message, describing the issue he had installing a recent build of Application Express 4.0 on his database. He ran into all sorts of compilation errors. As it turns out, Anthony was using Oracle Database version 10.2.0.1. And that won't work with Application Express 4.0.

Starting with Oracle Application Express 4.0 (most likely due out some time in 2010), the minimum database version will be 10.2.0.3. Database version 10.2.0.2 or less will not work. Application Express 4.0 cannot be installed and function and be supported in the earlier database versions - hacking the installation scripts won't work this time.

I say this with one caveat, though. Application Express 4.0 will still work with XE. Even though the database version of XE is 10.2.0.1, it's not really the same software as 10.2.0.1 of the released database software (i.e., Standard Edition). XE is really 10.2.0.1+.

Why I use the Resource Manager on apex.oracle.com

2009-08-26T13:37:00.005-04:00

A few weeks ago, I blogged about the use of the Oracle Database Resource Manager and apex.oracle.com, and how it is essential to keep this service up and running.

Well, today, I stumbled across 10 active database sessions, all from the same user, all running an anonymous PL/SQL block issued within SQL Commands on apex.oracle.com. All 10 sessions had been running for hundreds of seconds and all 10 were in the APEX_LOW resource consumer group. By using the built-in-to-APEX Utilities -> Database Monitor -> Sessions reports, I was able to determine exactly what was executing in these sessions.

What's wrong with this user's code?


DECLARE
i NUMBER(3):=1;
BEGIN
   WHILE (i<5) LOOP
      IF( mod(i,2)=0)  THEN       
         DBMS_OUTPUT.PUT_LINE(i);
      END IF;
   END LOOP;   
END;

I am NOT suggesting you run this on apex.oracle.com nor your own instance of Application Express.

Application Express 3.2.1 patch set applied to apex.oracle.com

2009-08-25T08:55:00.015-04:00

This past weekend, the forthcoming Application Express 3.2.1 patch set was applied to apex.oracle.com (and apex.oraclecorp.com, if you're an Oracle employee and user of the internal instance). The APEX 3.2.1 Patch Set Note lists the bugs fixed in this patch set.

In addition to the bugs fixed in this patch set, a few interesting points:

The online help (the popup window of the brows-able documentation) is available in Japanese.
AnyChart AnyGantt Flash Gantt and AnyChart AnyMap files are included in this patch set. Even though these aren't directly integrated into the Application Express declarative environment, they are included in the license of Application Express and available for your use within an Application Express application.
An updated version of FCKEditor is included, fixing a number of bugs. However, one user has already reported a change in behavior, with the default behavior of the Enter key resulting in a '<p>' versus the previous '<br />'.
This version of Application Express, 3.2.1.00.10, is the identical version that will be bundled with Oracle Database 11gR2.

For those customers running Oracle Database 11gR1 11.1.0.7, you'll need to pay special attention to the note about applying the APEX 3.2.1 patch set.

As with all past Application Express patch sets, the Application Express 3.2.1 patch set will be available for download on Oracle MetaLink. The full distribution of APEX 3.2.1 will be available for download on OTN. These should be available within one day.

Neues Application Express Buch - auf Deutsch!

2009-08-20T14:05:00.003-04:00

Im November 2009 soll das neue Buch "Oracle APEX und XE in der Praxis" von Dietmar Aust, Denes Kubicek und Jens-Christian Pokolm herauskommen. Ich kenne Dietmar schon seit den Anfangstagen der Oracle XE Datenbank, wo er innerhalb kurzer Zeit ein Experte für diese wurde. Und Denes Kubicek ist ein bekannter Experte für Oracle Application Express, welcher letztes Jahr mit dem "Oracle APEX Developer of the Year" ausgezeichnet wurde.

Ich freue mich schon auf das Buch (und natürlich auch auf eine signierte Ausgabe des Buches!).

--------

Coming in November 2009 is a new book from Dietmar Aust, Denes Kubicek and Jens-Christian Pokolm, entitled "Oracle APEX und XE in der Praxis". I have known Dietmar since the early days of Oracle Database XE, where he quickly became a subject matter expert. And Denes Kubicek is a recognized expert of Oracle Application Express and was last year's Oracle APEX Developer of the Year.

I look forward to this book (and getting a signed copy of this book!).

Forthcoming book about Application Express

2009-08-20T08:41:00.003-04:00

Today, Packt Publishing announced their forthcoming book, "Oracle Application Express 3.2 – The Essentials and More", authored by my good friend Arie Geller and Matthew Lyon. The expected availability is January 2010.

Congratulations, Arie. The end is in sight!

Application Express & YubiKey

2009-08-13T15:02:00.004-04:00

Roger Cohen from APEXtras was kind enough to introduce me to two-factor authentication using YubiKey and Application Express.

Now - I'll be honest, I had never heard of YubiKey before, but I am familiar with two-factor authentication. I am a satisfied user of KeePass, and I am able to maintain both a password and a separate key file (in my case, on a Flash Drive) for access to my encrypted KeePass passwords database. I need both my password and the keyfile to access the passwords database. I lose one or both and I can't get in.

Roger gives a very good description of YubiKey here, what it's good for and why you would want to consider it. But more importantly, Roger has a working demonstration of APEX and YubiKey authentication live on apex.oracle.com. Granted, you'll need a YubiKey for this demonstration to work. Lastly, the folks from APEXtras were kind enough to post an explanation of the logic and all of the source code for the custom authentication for this solution.

Oracle Database Resource Manager and Oracle Application Express

2009-08-12T10:46:00.023-04:00

At ODTUG Kaleidoscope this year, I gave a presentation on using the Oracle Database Resource Manager with Oracle Application Express. The Oracle Database Resource Manager enables an administrator to control the allocation of hardware resources within an Oracle database - something that an OS-based process scheduler cannot accomplish. The actual PowerPoint presentation is here.

I often talk about the scalability of Oracle Application Express on apex.oracle.com, which runs on fairly modest hardware. However, I fully believe that this database and server would eventually crumble if it were not for our use of the Oracle Database Resource Manager. Unlike a database instance which is running a handful of tuned applications, apex.oracle.com is a free-for-all. As long as you have a workspace, you can run whatever SQL you like from SQL Commands (I'm not recommending this), or, you could inadvertently write some pretty inefficient SQL or PL/SQL in your application. Without any controls in place, a user could easily and quickly monopolize the CPU resources on apex.oracle.com. And for a site that gets between 3.5 million and 6 million page views per week, page view requests could quickly back up and overwhelm the database server.

So how do we manage this chaos? Via the Oracle Database Resource Manager. Rather than explain the syntax of the Resource Manager, I think it's much simpler to convey the plan that is in place right now on apex.oracle.com and then comment on it.


-- apex.oracle.com resource plan

-- Section 1
begin
  dbms_resource_manager.clear_pending_area();
  dbms_resource_manager.create_pending_area();

  dbms_resource_manager.create_consumer_group(
      consumer_group => 'APEX_HIGH',
      comment        => 'All APEX sessions start in this group.');
  dbms_resource_manager.create_consumer_group(
      consumer_group => 'APEX_MEDIUM',
      comment        => 'APEX sessions are switched to this group after 10 seconds.');      
  dbms_resource_manager.create_consumer_group(
      consumer_group => 'APEX_LOW',
      comment        => 'Any sessions in this group have been executing for more than 120 seconds');
    
  dbms_resource_manager.submit_pending_area();

end;
/


-- Section 2
begin
    dbms_resource_manager_privs.grant_switch_consumer_group ( 
        grantee_name => 'APEX_PUBLIC_USER', 
        consumer_group => 'APEX_HIGH', 
        grant_option => FALSE );
    dbms_resource_manager_privs.grant_switch_consumer_group ( 
        grantee_name => 'APEX_PUBLIC_USER', 
        consumer_group => 'APEX_MEDIUM', 
        grant_option => FALSE );
    dbms_resource_manager_privs.grant_switch_consumer_group ( 
        grantee_name => 'APEX_PUBLIC_USER', 
        consumer_group => 'APEX_LOW', 
        grant_option => FALSE );
end;
/

begin
   -- Section 3
  dbms_resource_manager.clear_pending_area();
  dbms_resource_manager.create_pending_area();
  dbms_resource_manager.create_plan( plan => 'APEX_ORACLE_COM_PLAN', comment => 'APEX Plan');


    
  -- Plan Directives Section      
  dbms_resource_manager.create_plan_directive(
      plan => 'APEX_ORACLE_COM_PLAN',
      group_or_subplan => 'APEX_HIGH',
      comment => 'All APEX sessions start in this group.',
      mgmt_p1 => 70,
      switch_group => 'APEX_MEDIUM',
      switch_time => 10,
      switch_for_call => TRUE,
      switch_estimate => FALSE );

  dbms_resource_manager.create_plan_directive(
      plan => 'APEX_ORACLE_COM_PLAN',
      group_or_subplan => 'APEX_MEDIUM',
      comment => 'APEX sessions are switched to this group after 10 seconds.',
      mgmt_p1 => 8,
      switch_group => 'APEX_LOW',
      switch_time => 120,
      switch_for_call => TRUE,
      switch_estimate => FALSE );


    
  dbms_resource_manager.create_plan_directive(
      plan=> 'APEX_ORACLE_COM_PLAN',
      group_or_subplan => 'APEX_LOW',
      comment => 'Any sessions in this group have been executing for more than 120 seconds',
      mgmt_p1 => 2,
      switch_group => 'CANCEL_SQL',
      switch_time => 1800,
      switch_for_call => TRUE,
      switch_estimate => FALSE );
    

  -- Section 4
  dbms_resource_manager.create_plan_directive(
      plan=> 'APEX_ORACLE_COM_PLAN',
      group_or_subplan => 'OTHER_GROUPS',
      comment => 'The mandatory group',
      mgmt_p1 =>10);
    
  dbms_resource_manager.create_plan_directive(
      plan=> 'APEX_ORACLE_COM_PLAN',
      group_or_subplan => 'ORA$AUTOTASK_SUB_PLAN',
      comment => 'Sub plan for maintenance activity',
      mgmt_p1 => 10 );
    
  -- Section 5
  dbms_resource_manager.set_initial_consumer_group(
      user => 'APEX_PUBLIC_USER',
      consumer_group => 'APEX_HIGH');

  dbms_resource_manager.submit_pending_area();

end;
/



-- Section 6
begin
  dbms_scheduler.set_attribute( name => 'MONDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
  dbms_scheduler.set_attribute( name => 'TUESDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
  dbms_scheduler.set_attribute( name => 'WEDNESDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
  dbms_scheduler.set_attribute( name => 'THURSDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
  dbms_scheduler.set_attribute( name => 'FRIDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
  dbms_scheduler.set_attribute( name => 'SATURDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
  dbms_scheduler.set_attribute( name => 'SUNDAY_WINDOW',
      attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
end;
/


-- Section 7
alter system set resource_manager_plan = 'APEX_ORACLE_COM_PLAN' scope=both;

Section 1 - Create three consumer groups with the names APEX_HIGH, APEX_MEDIUM and APEX_LOW. These group names are arbitrary. A consumer group is simply a collection of users.

Section 2 - Remember that the Oracle HTTP Server and mod_plsql connect to the database as user APEX_PUBLIC_USER (if you're using the embedded PL/SQL Gateway, this is user ANONYMOUS). From a database perspective, all it sees are a bunch of database sessions, connecting as database user APEX_PUBLIC_USER. So in Section 2, we are granting the ability to switch among these consumer groups to database user APEX_PUBLIC_USER.

Section 3 - Create the plan directives which controls how much of the machine resources is dedicated to a particular consumer group, and how long they are allowed to remain in this group before switching to another group. So if a session is in the APEX_HIGH group, the APEX_HIGH group is entitled to 70% of the CPU resources. If the top-level SQL call is executing for more than 10 "on-CPU" seconds, then they get switched to the APEX_MEDIUM group. The APEX_MEDIUM group gets only 8% of the available CPU resources, for up to 120 seconds. After 120 seconds, the session gets switched to the APEX_LOW group. The APEX_LOW group has only 2% of the CPU resources available. After 1800 seconds (which is a *long* time), if the top-level SQL call is still executing, then it will be canceled. The session will not be killed, but the top-level SQL call will be canceled.

Section 4 - Directives are created for two other pre-defined and built-in groups, namely, OTHER_GROUPS and ORA$AUTOTASK_SUB_PLAN. The ORA$AUTOTASK_SUB_PLAN is used for all of the automated maintenance tasks of the database (e.g., statistics collection). OTHER_GROUPS is used for any other session not already a part of a group in the active Resource Manager plan. This would be the group for anyone connecting directly to the database, background database jobs not running as APEX_PUBLIC_USER, etc.

Section 5 - Make APEX_HIGH, our newly created consumer group, the default consumer group for user APEX_PUBLIC_USER. Then, submit the pending area for validation.

Section 6 - The Oracle database (at least for 11gR1 and 11gR2) ships with a predefined scheduler plan for maintenance tasks. Modify the resource plan in effect during these maintenance windows. If we don't, then the DEFAULT_MAINTENANCE_PLAN would be in effect and not our newly created Resource Manager Plan APEX_ORACLE_COM_PLAN. And what that means is we'd be back to a free-for-all during this maintenance window, with anyone being able to monopolize the server.

Section 7 - Change the settings of the database to use our newly created plan.

There are tools available in Enterprise Manager to create and monitor resource plans, so you don't have to commit the syntax of all of these PL/SQL packages to memory. Also, there are a large number of database views that can be used to monitor the Resource Manager, including:

V$RSRC_PLAN – Currently active resource plan

V$RSRC_CONSUMER_GROUP – Cumulative amount of CPU stats

V$RSRC_PLAN_HISTORY – History of resource plan, when enabled, disabled or modified

V$RSRC_CONS_GROUP_HISTORY – History of consumer group statistics

V$RSRCMGRMETRIC – Information about resources consumed and wait times per consumer group

V$RSRCMGRMETRIC_HISTORY – History of Resource Manager metrics

I granted privileges on these SYS-owned views to another database user that was mapped to an APEX workspace (actually, my workspace on apex.oracle.com). Then, I was able to easily build an APEX application using Interactive Reports on top of these views and monitor how well we were doing.

The Oracle Database Resource Manager is only available as a part of the Oracle Database Enterprise Edition. It is not available with XE, Standard Edition One, or Standard Edition of the Oracle Database. But for those customers that are already using Oracle Application Express on the Oracle Database Enterprise Edition, and they're attempting to consolidate a number of groups and applications onto a single instance, the Oracle Database Resource Manager can be easily used to prevent any one user or application from monopolizing the server.

BugDB Reporting

2009-06-11T09:16:00.004-04:00

Sorry to do this on a public blog, but this is a posting which is only relevant to Oracle employees.

It seems a lot of teams are looking to create an analysis application on top of the BugDB, to perform their own analysis of the BugDB data for their own product. There is a right way and a wrong way to get at this data. The wrong slow way to get at this data is to build a report on top of a query across the database link to the BugDB and query this data each and every page view in your application. The more efficient way to get at this data is to create a local table in your schema and then refresh from the BugDB into this local table, on demand. This results in much less load on the BugDB database itself, and is also a much faster application for your end users.

I actually cooked up something for someone else in Server Technologies. It will create all the necessary database objects to refresh from the BugDB and it also includes an Interactive Report on top of the primary BugDB table RPTHEAD. Anyone is welcome to use this application as a starting point. It’s also hooked up with SSO authentication, so if you import this into your workspace on the internal instance of Application Express at http://apex.oraclecorp.com, you’ll be ready to go with internal single sign-on. All you need to do is:

Download and unzip the application export BugReports_installer.sql from: http://general.us.oracle.com/~jkallman/bugreports/BugReports.zip
Create a database link to the BugDB named ‘BUGDB.WORLD’ in your schema. https://bug.oraclecorp.com/help/ConnectToBugHelp.html#dblink
Import and install the application export BugReports_installer.sql into your workspace (presumably on apex.oraclecorp.com).
After installation, you’ll be prompted to Install Supporting Objects. You should simply click Next > through the wizard. Prior to this step, the database link BUGDB.WORLD must exist.

That’s all there is to it! Run the application, navigate to the Administration tab, choose your product and timeframe, click Refresh, and then click the Bugs tab to view the Interactive Report on top of your local data.

It may seem like a simple application, but with the Interactive Report on top of the BugDB RPTHEAD, each end user can create a variety of customized reports and save them. You can read about Interactive Reports here: http://www.oracle.com/technology/obe/apex/apexusr31/apexusr31.htm

Who says Application Express can't scale?

2009-06-09T11:21:00.004-04:00

Over a year ago, I wrote about who is using Oracle Application Express on http://apex.oracle.com. At the time, I included some weekly statistics.

During our team meetings every week, I report on the overall usage of http://apex.oracle.com. I just wanted to report on some astounding numbers:


Total Page Views Distinct Applications Distinct Users
---------------- --------------------- --------------
       6225566                  3268           2778


Total Workspaces
----------------
         11643


Total Applications
------------------
           39849


Workspaces Approved
-------------------
              316

That's 6,225,566 page views in the past week, and this is still running on the same Dell PowerEdge 1950.

Now granted - the ability to sustain this type of load throughout the week deserves some credit to Kris Rice, who spent a fair amount of time analyzing Application Express on apex.oracle.com and identifying some major problem areas in performance. As well, he brought in some experts to perform OS tuning. The recent purge on apex.oracle.com removed close to 10,000 workspaces, schemas, tablespaces and datafiles. Lastly, a couple months ago, I implemented a Resource Manager plan which prevents someone from monopolizing the entire server. I'll be presenting a detailed discussion of Resource Manager and apex.oracle.com at ODTUG Kaleidoscope 2009.

A large number of these page views are from the bots of Search Engines, especially since ProMED Mail is run on apex.oracle.com. But a page view and execution of the engine is a page view and execution of the engine. All in all, that's a large load on a relatively cheap piece of hardware.

OraTweet - Micro-blogging with Oracle Application Express

2009-06-02T09:47:00.005-04:00

About a year ago, Carl Backstrom was telling me how he was helping another gentleman in Oracle, Noel Portugal from the CRM On Demand team, with a micro-blogging site developed with Oracle Application Express. Carl game me a demonstration of OraTweet, tried to explain Twitter, tweets, and micro-blogging. I just didn't get it, at the time.

Today, micro-blogging appears to be all the rage. Dwight Howard from the Orlando Magic basketball team is on Twitter. CNBC's Fast Money television show is on Twitter. I am even on Twitter, but I really don't use it, as you can tell.

Noel's application, OraTweet, has been in use within Oracle for the past year. As Noel states on the oratweet.com site: "Teams around Oracle are finding ways to communicate with their teams while reducing email overload. The results have been amazing. Global team members are connecting and sharing information. Users have started to develop stand alone clients to consume OraTweet’s timeline. We have also integrated the OraTweet timeline to Oracle Connect, our own internal social network. Overall information is flowing faster in a lightweight format."

Noel Portugal has developed an excellent Web site which explains OraTweet, lets you submit feedback, and provides a link to download the entire source code for OraTweet: http://oratweet.com/

See you in Monterey?

2009-05-29T10:45:00.005-04:00

Time is running out for the Oracle Development Tools User Group Kaleidoscope Conference in Monterey, California on 21-June-2009 through 25-June-2009. The lower cost advanced registration ends next Tuesday, 02-June-2009. The folks from ODTUG were kind enough to provide a template to use for a justification letter for your employer.

It all begins with the Application Express Symposium on Sunday, of which over 160 people are registered. Other than the opening remarks by some joker from Oracle, all other presentations at the Symposium are from customers and the community. And then there is a full agenda for the remainder of the week if you're interested in Oracle Application Express.

This is an outstanding, well-organized and well-run conference, giving customers the opportunity to interact with some of the greatest Application Express experts from around the world. This is a conference where you can listen, learn and discuss the solutions created by successful Oracle customers. Oracle is invited to participate, but this conference is run by ODTUG.

Disclaimer: I am not associated with this user group nor is there any financial incentive to me for the number of people in attendance.

Oracle Single Sign-On and apex.oracle.com

2009-04-20T09:20:00.003-04:00

Application Express on apex.oracle.com is now registered as a Partner Application for Oracle Single Sign-On. This means that you can now create demonstration and sample applications, hosted on apex.oracle.com, and have the users authenticate via the Oracle Single Sign-On Login Server.

Who is registered on Oracle.com? Well, if you have ever asked or answered a question on the wildly popular Oracle Application Express discussion forum on OTN, then you already have an account.

To enable SSO authentication to Oracle.com in your application on apex.oracle.com, simply follow these steps:

Shared Components -> Authentication Schemes
Click Create button
Choose “Based on a pre-configured scheme from the gallery” and click Next
Choose “Oracle Application Server Single Sign-On (Application Express Engine as Partner App)” and click Next
Give it a name like SSO and click Create Scheme
In the subsequent report of authentication schemes, click the “make current” link for your newly created SSO one
Click the Make Current button on the confirmation page
Go get a beer (or coffee or tea) to celebrate

It's that simple.

To see this in action, here's a very brief sample application with a public and non-public page, using SSO authentication.

Cleaning Up

2009-04-08T21:22:00.018-04:00

The architecture of Application Express is such that the database objects associated with a specific release all exist in a single schema. For the original Oracle HTML DB 1.5, all of the database objects existed in schema FLOWS_010500. As of the most recent version, Oracle Application Express 3.2, all of the database objects reside in schema APEX_030200.

When a new version of Application Express is installed, the following three actions take place:

The new version is installed into a new schema (this happens the same whether it's a new installation or an upgrade installation).
The meta data from the previous version is copied and sometimes transformed into the new schema.
All of the public synonyms which were pointing at the "old" version are now redirected to the new version of Application Express.

The beauty of this architecture is if an error occurs during installation or during upgrade, it's easy to revert back to the previous installation, as documented here. Both an APEX 3.1 installation (in FLOWS_030100) and an APEX 3.2 installation (in APEX_030200) can exist in the Oracle database at the same time, and with only one version active. Understand, though, if you choose to rollback and remove the newer version of Application Express, all of the changes made in the newer version (instance settings, application changes, new workspaces, etc.) will not be propagated back to the old version of Application Express if you switch schemas.

Once you have successfully upgraded your instance of Application Express, tested all of the upgraded applications, and deem the upgrade successful, there is no reason to maintain the old version of Application Express. Not only does it take up space in your database, it leaves a fairly privileged database account in the database, and this could pose a security risk if compromised.

For apex.oracle.com, which currently has over 20,000 workspaces, I always install the new version of Application Express into it's own tablespace. I do not install Application Express into the default SYSAUX tablespace. Application Express 3.1 was installed into tablespace APEX_REL31. When I recently upgraded to Application Express 3.2, and after we ran successfully for a couple days, it was easy to clean up the old Application Express 3.1 by issuing:

DROP USER FLOWS_030100 CASCADE;
DROP TABLESPACE APEX_REL31 INCLUDING CONTENTS AND DATAFILES;

That's it! The privileged database user from the previous version is gone. All of the space on disk is reclaimed. And the newer version of Application Express 3.2 in APEX_030200 hums along just fine.

Note: There is one schema installed with Application Express, FLOWS_FILES, which persists across version upgrades. The sole purpose of this schema is to maintain uploaded files. You should never manually remove this schema unless you want to remove every facet of Oracle Application Express from your database. And if you really wanted to remove every trace of Application Express, I'd recommend using the apxremov.sql script anyway.

Considerations when dropping on Database 11g

If you're using Application Express with Oracle Database 11g (11.1.0.6 or greater), undoubtedly you had to enable Network Services so Application Express could send e-mail, use Web Services, etc. If you didn't do this, then you'll encounter errors like "ORA-24247: network access denied by access control list (ACL)" when APEX attempts to send e-mail or access any other network resource.

One important caveat to the instructions above - if you created an ACL for FLOWS_030100 (Application Express 3.1) and granted connect privileges to FLOWS_030100, after you upgrade to Application Express 3.2 , grant these same connect privileges on the existing ACL to APEX_030200, and you drop database user FLOWS_030100, the Network ACL will contain a dangling reference to FLOWS_030100 and will be invalid. What this means is that if you're running along just fine with an upgraded Application Express 3.2 and you drop FLOWS_030100, your delivery of e-mail may no longer work. Users will see error messages like "ORA-24247: network access denied by access control list (ACL)" in APEX_MAIL_LOG.MAIL_SEND_ERROR.

This situation happened to me over the weekend. After Application Express 3.2 was running for a month on our internal instance, the old FLOWS_030100 database user was finally dropped. Numerous issues were raised on Monday morning by workspace owners who said they could no longer send e-mail and they were seeing the error message "ORA-24247: network access denied by access control list (ACL)" in APEX_MAIL_LOG.MAIL_SEND_ERROR.

The only way I've discovered to rectify this situation is to manually drop the ACL reference to FLOWS_030100. I connected as SYS in SQL*Plus and ran:

exec dbms_network_acl_admin.delete_privilege('power_users.xml','FLOWS_030100',TRUE,'connect');

This underlying database issue, with the ACL privileges not being dropped when a database user is dropped, was filed as Bug 7493477 and is targeted to be fixed in Database 11gR2.

Oracle Application Express in the cloud

2009-03-06T16:01:00.002-05:00

You may have heard a thing or two about cloud computing. Jason Straub, from the Oracle Application Express development team, recently blogged about using Oracle Application Express in the cloud - and how you can run APEX in the cloud for as cheap as 20 cents USD / hour.

http://jastraub.blogspot.com/2009/03/test-drive-oracle-application-express.html

There's a little bit of setup to use the Amazon.com Elastic Compute Cloud, but once that's behind you, you can easily stand up your own Oracle database in the cloud for a tiny amount of money. And, in my opinion, this just further demonstrates how the browser-based Oracle Application Express hosted development and deployment environment is so well suited to cloud computing.

Oracle Application Express 3.2 released

2009-02-27T16:46:00.003-05:00

Today, Oracle Application Express 3.2 was made available for download off of the Oracle Technology Network.

If you go to http://apex.oracle.com, you'll find links to:

The link to download Oracle Application Express 3.2
The list of new features in Oracle Application Express 3.2
New Oracle By Examples, including a an Oracle Forms to Oracle APEX OBE and a soon-to-be-released security OBE.

Thanks to the Oracle Application Express community for your continued support, ideas and enthusiasm.

Make all of your APEX applications run a bit faster

2009-02-20T09:10:00.026-05:00

See the important update below

Interested in making your APEX applications run faster? I know this seems like an impossible and astonishing feat, and you'll soon be approaching page view execution times of zero, but you can squeeze even a little more throughput and scalability with this one small exercise. And this shouldn't cost you an extra cent.

As a lot of people know already, Oracle Application Express is essentially one big SQL and PL/SQL program. "Porting" of Oracle Application Express to other platforms is not necessary. It installs via SQL*Plus. It runs where PL/SQL does. And PL/SQL, truly, is a write-once-run-everywhere platform.

So how do you make a PL/SQL program run faster? Through native compilation of PL/SQL, of course. When you compile a module in PL/SQL, you are converting it to an intermediate form named system code (or bytecode). At runtime, this system code is interpreted. Execution of this program would be much faster if it were compiled natively and the interpretation step was bypassed altogether. This is analogous to the old days of taking an interpreted BASIC program and compiling it to a native program.

An excellent description of PL/SQL native compilation can be found in Oracle Database PL/SQL Language Reference. When PL/SQL native compilation was introduced in Oracle Database 9iR1 and 9iR2, I found it to be complicated and involved, and I think I was successful getting a small program to ncomp once (and only once). Here is the explanation from some poor guy who figured out all the steps to do this in 9iR2 on Windows. But in Oracle Database 11gR1, this is downright trivial.

My test below was done in an Oracle Database 11gR1 11.1.0.6 on Oracle Enterprise Linux on VMWare Server on a Windows Vista x-64 host. With all those layers of software, the performance difference at runtime could still be easily observed. Also, I did this with the soon-to-be-released Application Express 3.2. Wherever you see APEX_030200, replace it with the database user of your specific APEX release (e.g., APEX 3.1 = FLOWS_030100).

The database view DBA_PLSQL_OBJECT_SETTINGS provides information about the compiler settings for all stored objects in the database. Connect as SYS via SQL*Plus or SQL Developer and run the following query (remembering again to replace 'APEX_030200' if you're not running Application Express 3.2):

On my instance this returned:


COUNT(*) OBJECT_TYPE         PLSQL_OPTIMIZE_LEVEL PLSQL_CODE_TYPE
---------- ------------------- -------------------- --------------------
      12 FUNCTION                               2 INTERPRETED
     370 PACKAGE                                2 INTERPRETED
     362 PACKAGE BODY                           2 INTERPRETED
      19 PROCEDURE                              2 INTERPRETED
       1 TABLE                                  2 INTERPRETED
     366 TRIGGER                                2 INTERPRETED
       4 TYPE                                   2 INTERPRETED

7 rows selected.

All PL/SQL objects are interpreted and have a PL/SQL optimization level of 2. You can alter the PL/SQL compiler optimization level via PLSQL_OPTIMIZER_LEVEL, but I encountered runtime errors in Application Express when I natively compiled with an optimizer level of 3. I don't know why, but I'm saving that for another day.

Recompiling all of these objects via native compilation can be done with three easy statements. Note: You should not do this while the APEX applications are actively being used, as these steps will recompile all of the objects in the schema and you could encounter object contention issues. Connect as SYS in SQL*Plus and run:

That's it! If you execute the query above, you should now see something like:

COUNT(*) OBJECT_TYPE         PLSQL_OPTIMIZE_LEVEL PLSQL_CODE_TYPE
---------- ------------------- -------------------- --------------------
      12 FUNCTION                               2 NATIVE
     370 PACKAGE                                2 NATIVE
     362 PACKAGE BODY                           2 NATIVE
      19 PROCEDURE                              2 NATIVE
       1 TABLE                                  2 NATIVE
     366 TRIGGER                                2 NATIVE
       4 TYPE                                   2 NATIVE

7 rows selected.

If you encounter errors and you want to revert back to what you had, run:

alter session set plsql_optimize_level = 2;
alter session set plsql_code_type = interpreted;
exec dbms_utility.compile_schema('APEX_030200');

But I think you'll be pleasantly surprised with the results and have no desire to revert back. In Database 11gR1, this has become a downright trivial exercise. And faster page views means greater throughput which means greater scalability on equivalent hardware. That's both green and economical.

Lastly, you might wonder if the hosted instance of Application Express at http://apex.oracle.com is running with natively compiled PL/SQL. It's not, but after we formally release Application Express 3.2, it will be. There's no reason not to.

Important Update

08-APR-2009: There's nothing like actually using and testing these features on a large-scale system. A few weeks ago, I had natively compiled the APEX engine on apex.oracle.com. But just this past week, we had to switch this back to interpreted. Some unexplained ORA-600 errors were being encountered which is being actively researched by the database development team.

Carl Backstrom - Oracle ACE

2009-02-17T20:13:00.003-05:00

Thanks to the work of Sharon on our team, she was able fulfill one of the desires Carl Backstrom had expressed a couple times.

The Oracle ACE program was "designed to recognize and reward members of the Oracle Technology and Applications communities for their contributions to those communities. These individuals are technically proficient (when applicable) and willingly share their knowledge and experiences." This really epitomized Carl - he was always ready and willing to share his vast knowledge with any one.

Well, as luck would have it, the folks at the Oracle Technology Network decided that Oracle employees could no longer be awarded the ACE designation. And that bothered Carl a little bit, as he was a very active contributor to the community every day. It just would have been nice to receive that designation.

Thanks to Sharon's suggestion, this has now been achieved: http://forums.oracle.com/forums/profile.jspa?userID=354238

apex.oracle.com upgraded to Application Express 3.2

2009-02-11T16:03:00.003-05:00

Today (Wednesday, 11-FEB-2009) I upgraded apex.oracle.com to Application Express 3.2.0.00.21. As our long time customers know, this is one of the last milestones in our cycle prior to production release.

You can go here for a brief introduction to the new features in Application Express 3.2. As you'll see, there are two primary themes - Forms Conversion and security. The Application Express 3.2 documentation is not staged yet, but the online help/documentation is current for APEX 3.2.

If you encounter odd behavior or you feel that something was not properly upgraded in your application, please feel free to report it on the APEX OTN discussion forum. We will watch this closely.

Thank you for all of your support.

Change is Coming....

2008-11-21T09:38:00.005-05:00

Change is coming...and no, I'm not referring to the forthcoming change in Washington. I'm referring to Oracle Application Express.

Since the first supported release of Application Express (Oracle HTML DB 1.5), Application Express has been delivered as a supported feature of the Oracle Database, supporting database releases 9.2.0.3 and higher. So even though Oracle HTML DB 1.5 was delivered as a feature of the Oracle Database Release 10gR1, a customer could actually download it from the Oracle Technology Network, install it in their Oracle Database 9iR2 9.2.0.3, and be in a supported configuration.

For the forthcoming release of Oracle Application Express 3.2, which introduces Oracle Forms Conversion, the minimum database version will continue to be 9.2.0.3. But for Oracle Application Express 4.0, the minimum database version will be Oracle Database 10gR2 10.2.0.x - possibly even 10.2.0.4.

What's up, DOAG?

2008-11-13T21:55:00.003-05:00

It's pronounced "what's up, dog?"...or if you're a Cleveland Browns fan like I am, it's pronounced "what's up, Dawg?" (my thanks to Sergio for this play on words).

The conference of the German Oracle User's Groups, Deutsche Oracle-Anwendergruppe 2008 Konferenz + Ausstellung (DOAG), is happening Monday 01-DEC-2008 through Wednesday 03-DEC-2008 in Nürnberg, Germany. Here is the conference program in German and English. There are a fair number of presentations about Oracle Application Express, including mine about what's coming new in Oracle Application Express in 2009.

I'm looking forward to the entire conference. Maybe some of the local attendees can take us on a walking tour of the Nürnberger Christkindlesmarkt.

Carl Backstrom Memorial Announcement

2008-11-01T18:55:00.005-04:00

Please join the family in celebrating the life of Carl Backstrom

on Thursday, the sixth of November

two thousand and eight

at one o'clock in the afternoon

Orange Terrace Park

20010 Orange Terrace Park Parkway

Riverside, CA 92508

In lieu of flowers the family has set up a Memorial Fund
in behalf of Carl's daughter, Destany.

Donations to Carl's Memorial Fund can be made several ways:

Domestic wire transfers
Account Number 152460903
Citibank ABA Number 322271724
International wire transfers SWIFT Code: CITI US 33
Checks
Make payable to Susan Bailey (Carl's Mother)
Address: 3395 S. Jones Blvd #403
Las Vegas, NV 89146

“And in the end, it's not the years in your life that count. It's the life in your years.” - Abraham Lincoln

Carl Backstrom Memorial Annoucement

Get your own at Scribd or explore others:

Open Source Web Design

2008-11-01T08:53:00.006-04:00

Maybe everyone else on the planet knew about OSWD other than me. Regardless, here goes...

My brother-in-law, Matt Wagner, recently asked me to help him with his Web site. This was an interesting proposition, because I have not invested that much time learning about proper Web design (I never had to - I'm a database guy and we always had Carl and Marc for that kind of stuff). So I took this as an opportunity to actually learn something and I embraced the challenge. My brother-in-law already had his domain name, and he had a rough idea about the layout and the content that he wanted to put on his Web site, but he didn't know the first thing about HTML or how to propagate this information out to GoDaddy.

The last conversation I had with Carl was a week ago and I wanted to get his feedback about what I had done with Matt's Web site. Here is what pointed Carl to. I was so proud of myself, having figured out how to make some practical use of styles and also my over-the-top use of the effects from MooTools. Let me tell you - Carl laughed and laughed. He said the colors were odd, there was no contrast with the font and the background, the effects were funny, and he encouraged me not to use Serif fonts ("just not in style"). He also told me, with a chuckle, that I should start practicing jQuery and forget MooTools.

Carl did point me to the Open Source Web Design site and told me to pick one. For someone like me, who is artistically and graphically challenged, this site is a wealth of excellent templates and ideas. Needless to say, my second attempt at this, which we're continuing to iterate upon, is much, much better.