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