Nice URL in a Public Facing Oracle APEX Application

UPDATE: Try out Morten Bråtens excellent post on creating a REST web service with PL/SQL for the simplest answer to beautifying APEX URLs. The main difference between our solutions is that he uses a PL/SQL-procedure to actually do the rewrite, and I use an Apache map file. If I only had known... Ah, well, you live, you learn.

The Oracle APEX URL construct can be a bit cryptic for the uninvited, people and spiders alike. Here is a (a, not THE) technique to alleviate this. It is loosely based on a similar solution I made for an ancient Oracle Portal solution quite a few years back. You need an Apache (OHS/OAS or regular Apache) in front for this to work. The API is tested for Apache 2.2 and Oracle XE/11g, but it should run equally well on OHS/OAS (Apache 1.3-2.0).

Why Do You Need Nice URLs?
This is the place to start; Why would you consider rewriting the native APEX URL? There are a few good reasons why, but generalizations won't work here. The question is why do YOU need this for YOUR application? Consider it, and reconsider it, because there are no silver bullets here. Any path you choose will inevitably cause more work, add complexity and even more gray hairs when your carefully thought out assumptions suddenly changes. Not to mention adding overhead to your webserver when processing requests. Perhaps you will even start loosing hair for all I know. The more sophisticated application, the more work added for beautifying the URLs.

For an intranet environment I can see no good reasons to do this. None.

The Alternatives
There are already some designs out there to help you on your way. Patrick Wolf has a list of the more popular ones. Kris Rice's post Better APEX URLs also uses a map file. The <iframe> solution is also worth considering, depending on your need. There are also some very creative Apache rewrite solutions described in the OTN APEX-forum.

My Attempt
Let's just call it that, an attempt. It did not turn out as streamlined as I had hoped for, but for my need (and the effort I was prepared to put into it), it suffices.

I wanted to create content based nice URLs, and the content in question here resides in an Oracle database table. I also wanted to control the output from the database to reduce the number of moving parts.

This is how it works:

• You can define a "page type" based on a table with a primary key, and create mapping between APEX URL and a nice URL based on that
• You can manually create mapping between APEX URL and a nice URL to fully control both APEX and nice URL
• The APEX to nice URL mapping will be written to an Apache map file
• Apache checks the content of the map file for a potential rewrite
• If the map file changes, Apache will cache the new version automagically

See examples below the first script to get a feel for it. The rewrites will not get in the way of the normal APEX URLs, so it will not break existing functionality.

The Database Code
The following script will create two tables, one to hold the mapping between source table (where content resides) and the APEX page(s) where content will be displayed. The other table is where the actual nice to APEX URL-mapping resides, the content of this table is what gets written to the Apache rewrite map file.

The script will also create a directory where the URL map file will be written, and a package to handle all the in-betweens. The schema user must have the appropriate privileges to create these objects for the script to succeed. Replace path to APACHECONF directory to desired location.

-- change path to suite your environment
create or replace directory APACHECONF as 'C:\Oracle\OraXE\Apache\conf'
/

create table app_page_type (
   page_id              number
,  source_table         varchar2(32)
,  source_table_pk_col  varchar2(32)
,  url_path             varchar2(255)
,  pk_page_parameter    varchar2(255)
,  name_column          varchar2(32)
)
/

create unique index app_page_type_uk
   on app_page_type (page_id, source_table)
/   

create table app_page_url_mapping
(
   page_id             number not null
 , source_table        varchar2(32)
 , source_table_id     number
 , apex_url            varchar2(4000) not null
 , nice_url            varchar2(4000) not null
 , nice_title          varchar2(255) 
)
/

create unique index app_page_url_mapping_uk
   on app_page_url_mapping (page_id, source_table, source_table_id)
/   

create or replace package app_page_p
as
   -- create url-friendly construct, 255 chars long
   -- based on a name.
   function get_nice_name (
      p_name               in     varchar2
   ,  p_source_table_id    in     varchar2 default null
   ,  p_url_end            in     varchar2 default '.html'
   ) return varchar2;
   -- get nice url based on source table, can be used
   -- directly from APEX application
   function get_nice_url (
      p_page_id            in    number
   ,  p_source_table       in    varchar2
   ,  p_source_table_id    in    varchar2
   ) return varchar2;
   -- get nice url from APEX page_id, will only return
   -- mappings without source table defined
   function get_nice_url (
      p_page_id            in    number
   ) return varchar2;
   -- create mapping based on page type and source table
   procedure create_mapping (
      p_source_table       in       varchar2
   ,  p_source_table_id    in       varchar2
   ,  p_name               in       varchar2 default null);
   -- create complete manual mapping  
   procedure create_manual_mapping (
      p_page_id            in       number
   ,  p_apex_url           in       varchar2
   ,  p_nice_url           in       varchar2
   ,  p_nice_title         in       varchar2 default null);
   --remove mapping when source is removed  
   procedure remove_mapping (
      p_source_table       in       varchar2
   ,  p_source_table_id    in       number
   );
   -- remove manual mapping to page_id
   procedure remove_manual_mapping (
      p_page_id            in       number
   );
   -- synchronize map file with mapping table
   procedure synchronize_mapfile;
end;
/
          
create or replace package body app_page_p
as
   -- global variables
   -- change variables to suite your environment
   g_app_id       number        := 1000; --APEX app_id for public application
   g_app_name     varchar2(255) := 'My test app'; --descriptive name of application
   g_dad_path     varchar2(255) := '/pls/apex'; --dad or location
   g_map_file     varchar2(255) := 'map.txt'; --name of map file
   g_tmp_map_file varchar2(255) := 'map_tmp.txt'; --name of temporary map file
   g_map_dir      varchar2(255) := 'APACHECONF'; --name of directory
   -- get nice url from source_table
   function get_nice_url (
      p_page_id            in    number
   ,  p_source_table       in    varchar2
   ,  p_source_table_id    in    varchar2
   ) return varchar2 is
      l_ret    varchar2(255);
   begin
      select tab.nice_url
        into l_ret
        from app_page_url_mapping tab
       where tab.page_id = p_page_id
         and tab.source_table = p_source_table
         and tab.source_table_id = p_source_table_id;
      return l_ret;
   exception
      when no_data_found
      then
         return null;
   end;
   -- get nice url from page_id, manually created
   function get_nice_url (
      p_page_id            in    number
   ) return varchar2 is
      l_ret    varchar2(255);
   begin
      for r in (select tab.nice_url
                  from app_page_url_mapping tab
                 where tab.page_id = p_page_id
                   and tab.source_table_id is null)
      loop
         l_ret := r.nice_url;
         exit;
      end loop;
      return l_ret;
   exception
      when no_data_found
      then
         return null;
   end;
   -- create url-friendly construct, 255 chars long
   function get_nice_name (
      p_name               in     varchar2
   ,  p_source_table_id    in     varchar2 default null
   ,  p_url_end            in     varchar2 default '.html'
   ) return varchar2 is
      l_ret    varchar2(4000) := p_name;
      l_sub    number;
   begin
      -- special national translation (excerpt)
      l_ret := translate(lower(l_ret), 'æøå ', 'aoa_');
      l_ret := regexp_replace(l_ret, '([^[:alnum:]|_])', '', 1, 0, 'i');
      l_sub := nvl(length(to_char(p_source_table_id)||p_url_end),0);
      l_ret := substr(l_ret, 1, 255-l_sub);
      l_ret := l_ret||p_source_table_id||p_url_end;
      return l_ret;
   end;
   -- lookup column value for source_table and source_table_id  
   function get_source_name (
      p_source_table       in       varchar2
   ,  p_source_table_id    in       number
   ,  p_source_pk_col      in       varchar2
   ,  p_source_name_col    in       varchar2
   ) return varchar2
   is
      l_sql    varchar2(4000) :=   'select '||p_source_name_col||' pk_col'||chr(10)
                                 ||'  from '||p_source_table||chr(10)
                                 ||' where '||p_source_pk_col||' = :1';
      l_ret    varchar2(4000);
   begin
      execute immediate l_sql into l_ret using in p_source_table_id;
      return l_ret;
   end;
   -- create the actual mapping
   procedure create_mapping (
      p_source_table       in       varchar2
   ,  p_source_table_id    in       varchar2
   ,  p_name               in       varchar2 default null
   ) is
      l_orig_name     varchar2(4000);
      l_nice_name     varchar2(255);
      l_apex_url      varchar2(4000);
      l_nice_url      varchar2(4000); 
      l_nice_title    varchar2(4000);
   begin
      -- delete previous entries
      delete from app_page_url_mapping
       where source_table = p_source_table
         and source_table_id = p_source_table_id;  
      for r in (select pt.*
                  from app_page_type pt
                 where pt.source_table = p_source_table)
      loop
         if p_name is null
         then
            l_orig_name := get_source_name(r.source_table, p_source_table_id, r.source_table_pk_col, r.name_column);
         else
            l_orig_name := p_name;
         end if; 
         l_nice_name := get_nice_name(l_orig_name, p_source_table_id, '.html');
         l_apex_url  := g_dad_path||'/f?p='||g_app_id||':'||r.page_id||':0::::'||r.pk_page_parameter||':'||p_source_table_id;
         l_nice_url  := r.url_path||'/'||l_nice_name;
         l_nice_title := substr(g_app_name||' - '||l_orig_name, 1, 255);
         -- insert new entry
         insert into app_page_url_mapping (
            page_id
         ,  source_table
         ,  source_table_id
         ,  apex_url
         ,  nice_url
         ,  nice_title)
         values (
            r.page_id
         ,  r.source_table
         ,  p_source_table_id
         ,  l_apex_url
         ,  l_nice_url
         ,  l_nice_title);
      end loop; 
      -- synchronize url-mapping file
      synchronize_mapfile;      
   end;
   -- create manual mapping  
   procedure create_manual_mapping (
      p_page_id            in       number
   ,  p_apex_url           in       varchar2
   ,  p_nice_url           in       varchar2
   ,  p_nice_title         in       varchar2 default null  
   ) is
   begin
      -- educated guess on delete
      delete from app_page_url_mapping
       where page_id = p_page_id
         and source_table is null;
      -- insert new entry
      insert into app_page_url_mapping (
         page_id
      ,  apex_url
      ,  nice_url
      ,  nice_title)
      values (
         p_page_id
      ,  p_apex_url
      ,  p_nice_url
      ,  p_nice_title);
      -- synchronize map file
      synchronize_mapfile;
   end;
   procedure remove_mapping (
      p_source_table       in       varchar2
   ,  p_source_table_id    in       number
   ) is
   begin
      delete from app_page_url_mapping
       where source_table = p_source_table
         and source_table_id = p_source_table_id;
      synchronize_mapfile;
   end;
   procedure remove_manual_mapping (
      p_page_id            in       number
   ) is
   begin
      delete from app_page_url_mapping
       where page_id = p_page_id
         and source_table is null;
      synchronize_mapfile;
   end;
   -- normal write to file procedure
   procedure write_to_file (
      p_file_name       in          varchar2
   ,  p_directory       in          varchar2
   ,  p_content         in          clob
   ) is 
      l_file          utl_file.file_type;
      l_buffer        raw(32000);
      l_amount        binary_integer := 32000;
      l_pos           integer := 1;
      l_blob          blob;
      l_blob_left     number;
      l_blob_length   number;
      l_file_content  blob;
      l_src_offset    integer := 1;
      l_dest_offset   integer := 1;
      l_lang_ctx      integer := dbms_lob.default_lang_ctx;
      l_warn          integer;
   begin
      -- converting from clob to blob
      dbms_lob.createtemporary(l_file_content, false);
      dbms_lob.converttoblob(l_file_content, p_content, dbms_lob.getlength(p_content), l_dest_offset, l_src_offset, 1, l_lang_ctx, l_warn);
      l_blob_length := dbms_lob.getlength(l_file_content);
      l_blob_left := l_blob_length;
      -- open the destination file.
      l_file := utl_file.fopen(p_directory,p_file_name,'WB', 32760);
      -- if small enough for a single write
      if l_blob_length < 32760 then
         utl_file.put_raw(l_file,l_file_content);
         utl_file.fflush(l_file);
      else -- write in pieces
         l_pos := 1;
         while l_pos < l_blob_length
         loop
            dbms_lob.read(l_file_content,l_amount,l_pos,l_buffer);
            utl_file.put_raw(l_file,l_buffer);
            utl_file.fflush(l_file);
            -- set the start position for the next cut
            l_pos := l_pos + l_amount;
            -- set the end position if less than 32000 bytes
            l_blob_left := l_blob_left - l_amount;
            if l_blob_left < 32000 then
               l_amount := l_blob_left;
            end if;
         end loop;
      end if;
      utl_file.fclose(l_file);
      dbms_lob.freetemporary(l_file_content);
   exception
     when others then
       dbms_lob.freetemporary(l_file_content);
       -- close the file if something goes wrong.
       if utl_file.is_open(l_file) then
         utl_file.fclose(l_file);
       end if;
       raise;
   end;
   -- synchronize map file
   procedure synchronize_mapfile
   is
      l_map    clob;
   begin
      l_map :=          '#       Generated file, manual changes will suddenly disappear'||chr(10);
      l_map := l_map || '#       Last generated: '||to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss')||chr(10);
      for r in (select *
                  from app_page_url_mapping)
      loop
         l_map := l_map||r.nice_url||' '||r.apex_url||chr(10);
      end loop;
      write_to_file(g_tmp_map_file, g_map_dir, l_map);
      -- if everything goes well, we rename and overwrite
      utl_file.frename(g_map_dir, g_tmp_map_file, g_map_dir, g_map_file, true); 
   end; 
end;
/

Configuring Apache
Apache will handle the actual rewrites based on the generated map file. Include the following in your httpd.conf (after backing it up)

## Rewrite URLs that exists in map.txt
  RewriteMap nicetoapex txt:c:/oracle/oraxe/apache/conf/map.txt
  RewriteCond %{REQUEST_URI} /(([^/]+)(/.*)*)$
  RewriteCond %2 =articles.html [OR]
  RewriteCond %2 =article [OR]
  RewriteCond %2 =albums.html [OR]
  RewriteCond %2 =album
  RewriteCond ${nicetoapex:%{REQUEST_URI}|NOT_FOUND} !NOT_FOUND
  RewriteRule ^(/.*) ${nicetoapex:$1}%{QUERY_STRING} [P]

Note the forward slashes in the map file path (even for Windows installs). The code must be included directly into your VirtualHost-directive (if you have one), and not your default APEX Location-directive or some such. The reason is that the rewrite expects the nice-URL, not something that looks like '/pls/apex'.

Also note that I have included some criteria for when the map file will be checked, this is to exclude any number of requests not related directly to APEX pages. This is a very manual step, but can be minimized if you decide to prefix all your nice URLs with a common string. If you prefix your URLs you also will not force Apache to go through all the conditions before the attempted rewrite. A prefix kind of defeated the purpose of nice URLs for me, so I will update and reload Apache when changes are needed. A bit masochistic, I know...

The very cryptic "!NOT_FOUND" line above simply states that if the URL is not part of the map file, then don't rewrite (${nicetoapex:$1} will then have no value). There are probably easier ways to achieve this, but my knowledge of Apache is limited to what google serves me at the first page of the search result...

Apache will cache a copy of the map file for every start/restart/reload, and if the file is updated (mtime changed) it will re-read the file automagically. That is a nice feature, and saves you a ton of hassle.

You are not home free yet, APEX submits pages with relative paths (which is quite natural), but that will break your nice URL hierarchy. To fix this add:

## Handle calls to wwv_flow.accept
  RewriteCond %{REQUEST_URI} ^/wwv_flow.accept
  RewriteRule ^/(.*) /pls/apex/$1 [P]

This will rewrite all requests ending with wwv_flow.accept and point it to your dad-location. Depending on your application, it may be necessary to include more rewrite conditions for this rewriterule (like wwv_flow.show).

For good measure you can also include a rewrite to handle the default "start page" of your site with the following:

## Redirect server home page to Apex application
  RewriteRule ^/$ http://localhost:8080/pls/apex/f?p=1000:1:0 [P,L]
  RewriteRule ^/index.html$ http://localhost:8080/pls/apex/f?p=1000:1:0 [P,L]

Restart Apache for every change of httpd.conf for the changes to take effect.


Mapping URLs From Table Content
First off you have to define a relationship between the APEX-pages showing the content, and the database table where the content resides. The table APEX_PAGE_TYPE holds this information.

insert into app_page_type (
     page_id
   , source_table
   , source_table_pk_col
   , url_path
   , pk_page_parameter
   , name_column)
  values   (
     3                -- APEX page_id
   , 'ARTICLES'       -- Source Table
   , 'ARTICLES_ID'    -- Source table primary key
   , '/article'       -- Desired URL-prefix
   , 'P3_ARTICLE_ID'  -- APEX page item id that holds the primary key
   , 'TITLE')         -- Source table column that content title
/
commit
/

In the example above I want to generate a nice URL for articles in the ARTICLES-table. APEX page_id=3 will show the content, based on the value of page item P3_ARTICLE_ID. This bit you only have to do once for each page type/source table association.

When I insert/update a row in the ARTICLES-table with ARTICLE_ID=0, I can generate a nice URL with the following statements:

begin
   app_page_p.create_mapping( 'ARTICLES' -- source table name
                            , 0);        -- source table id
end;
/
commit
/

This is the code you normally would run following a publication of an article. If the TITLE-column has value 'APEX 4.0 - It is finally here!', your map-file should now contain the following:

#       Generated file, manual changes will suddenly disappear
#       Last generated: 03.02.2010 22:19:22
/article/apex_40__it_is_finally_here0.html /pls/apex/f?p=1000:3:0::::P3_ARTICLE_ID:0

You can have more than one page type associated with the same database table, and URLs for all pages will be generated from the statement above. If all works now, you should be able to point your browser to http://<yoursite>/article/apex_40__it_is_finally_here0.html

Manually Mappig URLs
Some APEX pages will probably not be directly linked to a database table, to accommodate this, the API offers a possibility to manually create page mappings with the following statements (note, there is no need for a page type to be defined first, as there is no content table to associate with that APEX page):

begin
   app_page_p.create_manual_mapping(2                          -- APEX page_id
                                   , '/pls/apex/f?p=1000:2:0'  -- APEX URL
                                   , '/articles.html'          -- Nice URL
                                   , 'Articles');              -- Nice name
end;
/
commit
/

If you ran this after the previous example, your map file should look like this:

#       Generated file, manual changes will suddenly disappear
#       Last generated: 03.02.2010 22:21:01
/articles.html /pls/apex/f?p=1000:2:0
/article/apex_40__it_is_finally_here0.html /pls/apex/f?p=1000:3:0::::P3_ARTICLE_ID:0

And likewise you should be able to see http://<yourhostname>/articles.html in a browser.

Pitfalls, Improvements and Considerations
What is the first thing you noticed about the API? No APP_ID-parameters! This version does not support more than one public application, but extending the code to accommodate this should be fairly trivial if the need should arise.

The second observation is the lack of support for more than one primary key column. The API must be modified to support this.

The third observation is the lack of APEX parameter support. Including RP or other such parameters will probably get onto your wish-list pretty soon. The API can be altered to achieve this, either through generating more mappings, or attaching parameters in a normal form to the nice-URL, and rewriting it to the correct APEX syntax in the Apache.

In the current version, there is no support for URL-hierarchy. If you want to incorporate an URL-path based on an hierarchy defined by the mapping API, you have to extend the API, or map it yourself by APP_PAGE_TYPE and APP_PAGE_URL_MAPPING.

You may also consider using the Apache httxt2dbm-utility to create a binary format DBM-file of the original map file, which is considerably faster than mapping with a txt-file. Adding a dbms_scheduler job to execute an external program should do the trick. Apache rewrite syntax is almost identical, you just have to tell it the map-type has changed.

I seriously wish there was an easy way to just delete a line from an existing file. I for one, could not conjure up a good way to do this from PL/SQL, hence the complete rewrite of the file. Maybe Perl could do a better job of it? Any old how, this portion of the API is well suited for a more asynchronous execution. In a normal production environment, the Apache is located in a DMZ, and not on the same server as the database. In that case the map file has to be moved from the database server to the webserver. The API does not support moving files through FTP/SFTP, but you can check out http://www.orafaq.com/scripts/plsql/ftpclient.txt for a plain PL/SQL FTP-solution, or be creative with an external procedure.

A Small Bonus
If you want to set the APEX page title according to the source table, you can modify the page template and include a function to return the nice_name column of the APP_PAGE_URL_MAPPING-table. The function will then be common for all pages in your application. The title of your webpage continually crops up as one of the more important tags for certain search engines.

In Conclusion
If you decide to use the API, then I suggest you start thinking about how to design your applications to use it, especially considering branching, tabs and other native APEX components that gravitate towards the normal APEX URL construct. If your application branches out with the old URL, there would be little point in attempting to rewrite the URLs. As I stated earlier, there are no silver bullets, and there will be more work than just using native APEX URLs.

If you are aiming at more sophisticated public applications, I would seriously consider NOT using it. This API (in it's present state at least) is aimed at more simple applications.

Oh, and did I mention you use this code at your own risk? Well, you do!

Enjoy :-)