/* Mostly about Oracle APEX, ORDS, PL/SQL and a bit of Linux in between */
When working with Oracle APEX, the syntax of generating links via APEX_UTIL.prepare_url is dependent on the app session and whether Friendly URLs are enabled or not. Based on the session context, the function will generate absolute or relative URLs. These differences become apparent, for example, when prepare_url is called from outside your own APEX application, e.g. via a Scheduler Job. In this post, I’ll walk you through a robust PL/SQL solution that ensures reliable, absolute URLs regardless of the execution context — and explain why understanding this nuance is crucial for predictable link behavior.
When generating a link to an Oracle APEX page — such as for use in an email — it’s essential to ensure that the link includes the full, absolute web address. APEX offers two functions for this: APEX_PAGE.get_url and APEX_UTIL.prepare_url. In my example, I will consume APEX_UTIL.prepare_url to make it possible to rejoin sessions and create links with different types of checksums included. You will need to configure some options on application and page level (e.g. Page Access Protection and Session Management) to use checksums and to allow users to rejoin sessions.
Recently, I stumbled on a little challenge: sending emails including links to my APEX applications using different proxy domains. Since my app is deployed across multiple customers – each with their own APEX workspace and custom proxy domain – I needed to generate custom links. By using the proxy domains, customers can only access their APEX apps but not the ORDS Landingpage and APEX Workspace Login. On the other hand, the direct APEX instance URL provides access to all resources and can only be used by developers. The goal was to generate the link and consider both URLs depending on the purpose the link is created. Another problem was that the link generation should work in every context (inside and outside of an HTTP context). Since prepare_url does only return an absolute link that includes the server’s host address (FQDN) in case Friendly URLs are enabled in the APEX application and we are not within an HTTP context, it was necessary to modify the link and add the domain after APEX had generated it, in case it was missing.
To add a bit of flexibility and keep things dynamic, I went with the following approach:
In my example, I have saved the proxy URLs in a table named “customer_proxies”. You need to include the SCRIPT_NAME path (“APEX_PATH”) for the proxy (e.g. https://customer1.example.com/ords/) as well as for the APEX instance (e.g. https://apex.example.com:8443/ords/) as well. With a proxy, the correct URL that needs to be saved depends on any rewrite rules you have defined on the proxy, so custom adjustments of the code below may be necessary. The Oracle APEX instance URL is a static value and needs to be set in the APEX internal workspace (instance admin) under Manage Instance > Instance Settings > Email > Oracle APEX Instance URL.
It’s important to note that the syntax of the resulting URL depends on the Friendly URLs setting of the APEX application in whose session context we are in. If you are in a session of an app with Friendly URLs activated, APEX will always generate a Friendly URL for you.
/** generates a fully qualified link to an APEX page with page items and checksum included
* @param pi_app_id specifies the app id
* @param pi_app_page_id specifies the app page id
* @param pi_app_user specifies the app user to be taken in case no APEX session exists yet
* @param pi_customer_id specifies a row in our customer proxy domain mapping table
* @param pi_checksum_type specifies the checksum type (NULL, PUBLIC_BOOKMARK, PRIVATE_BOOKMARK, SESSION)
* @param pi_page_item_names specifies the page item names (1...n)
* @param pi_page_item_values specifies the corresponding page item values (1...n)
* @return varchar2 - the generated link
**/
function generate_page_link
(
pi_app_id in number
, pi_app_page_id in number
, pi_app_user in varchar2 default sys_context('apex$session','app_user')
, pi_customer_id in number default null
, pi_checksum_type in varchar2 default null
, pi_page_item_names in APEX_T_VARCHAR2 default null
, pi_page_item_values in APEX_T_VARCHAR2 default null
) return varchar2
as
l_instance_url varchar2(500);
l_proxy_url varchar2(500);
l_script_name varchar2(100);
l_session_id number;
l_page_items varchar2(1000);
l_prepared_url varchar2(4000);
l_link varchar2(4000);
begin
-- basic validation for input parameters
if pi_app_id is null
or pi_app_page_id is null
or ( pi_page_item_names is not null and pi_page_item_values is not null
and pi_page_item_names.count != pi_page_item_values.count)
then
raise_application_error(-20001, 'Some input parameters are missing or given page item names and values do not contain the same number of elements.');
end if;
-- if this function is not called within an active APEX app session (e.g. Scheduler Job), we need to create it to be able to generate the link with checksum
if pi_checksum_type is not null
and APEX_CUSTOM_AUTH.get_session_id is null
then
APEX_SESSION.create_session(
p_app_id => pi_app_id,
p_page_id => pi_app_page_id,
p_username => pi_app_user
);
l_session_id := APEX_CUSTOM_AUTH.get_session_id;
end if;
-- try to get a proxy domain that is used by clients to access the app
-- in my example, each customer has their own proxy domain to access their own APEX application
-- example: https://customer1.example.com/ords/
begin
select proxy_url
into l_proxy_url
from customer_proxies
where customer_id = pi_customer_id;
exception
when no_data_found then
l_proxy_url := null;
end;
-- get the direct Oracle APEX instance url
-- the Oracle APEX instance url needs to be set in the internal workspace (instance admin)
-- it is used if no proxy url was found
-- example: https://apex.example.com:8443/ords/
l_instance_url := APEX_MAIL.get_instance_url;
-- create a comma-separated string of page_item_name:page_item_value pairs for the link
if pi_page_item_names is not null and pi_page_item_values is not null
then
for i in 1 .. pi_page_item_names.count
loop
l_page_items := l_page_items || pi_page_item_names(i) || ':' || UTL_URL.escape(pi_page_item_values(i));
if i < pi_page_item_names.count
then
l_page_items := l_page_items || ',';
end if;
end loop;
end if;
-- function results of APEX_UTIL.prepare_url are dependent on the app's friendly url configuration
-- we need to consider two points in case friendly urls are used in the current app session:
-- * if we are not in an HTTP context (there are no CGI variables), APEX_UTIL.prepare_url will return an absolute url including the APEX instance url (domain name)
-- * in case we are in an HTTP context, the url generated with APEX_UTIL.prepare_url will start with the SCRIPT_NAME path ("APEX_PATH"), so we need to make sure to not have it twice
-- technically, it is possible to control whether friendly urls are generated by manually setting APEX_APPLICATION.g_friendly_url to true or false before calling APEX_UTIL.prepare_url
-- note: the function uses the static instance url (APEX_MAIL.get_instance_url) if it generates an absolute url and does not evaluate dynamic CGI environment variables such as HTTP_HOST in case we access ORDS via a proxy
--
-- examples:
-- g_friendly_url = true - no HTTP context - https://apex.example.com:8443/ords/r/workspace_path_prefix/app_alias/page_alias?p10_item_value=xyz&clear=10&cs=ABCDEF123456
-- - HTTP context - /ords/r/workspace_path_prefix/app_alias/page_alias?p10_item_value=xyz&clear=10&cs=ABCDEF123456
-- g_friendly_url = false - - f?p=1234:10:::NO:10:P10_ITEM_VALUE:xyz&cs=ABCDEF123456
l_prepared_url := APEX_UTIL.prepare_url(p_url => 'f?p=' || pi_app_id ||
':' || pi_app_page_id ||
'::::' || pi_app_page_id ||
':' || l_page_items
,p_checksum_type => pi_checksum_type
,p_plain_url => true);
-- absolute url given
if regexp_like(l_prepared_url, '^' || l_instance_url)
then
-- customer proxy url exists, so we can replace the instance url with the proxy url
if l_proxy_url is not null
then
l_link := replace(l_prepared_url, l_instance_url, l_proxy_url);
-- customer proxy url does not exist and the given url already includes the instance url, take it "as is"
else
l_link := l_prepared_url;
end if;
-- given url is relative
-- the base url (proxy or instance url) needs to be added
else
-- get value of CGI variable SCRIPT_NAME (HTTP context)
begin
l_script_name := OWA_UTIL.get_cgi_env('SCRIPT_NAME');
exception
when others then
l_script_name := null;
end;
-- SCRIPT_NAME path is included in given url
if l_script_name is not null
and regexp_like(l_prepared_url, '^' || l_script_name)
then
if l_proxy_url is not null
then
-- if we have a proxy, remove trailing slash and SCRIPT_NAME path from given url to make sure we do not run into conflicts with proxy rewrite rules
l_proxy_url := substr(l_proxy_url, 1, length(l_proxy_url) - 1);
l_prepared_url := regexp_replace(l_prepared_url, '^' || l_script_name, '');
else
-- in case we are using the instance url, we need to remove the SCRIPT_NAME path
l_instance_url := regexp_substr(l_instance_url, '^https?://([^/]+)', 1, 1, null, 0);
end if;
end if;
-- priorize proxy url, take instance url as fallback
l_link := coalesce(l_proxy_url, l_instance_url) || l_prepared_url;
end if;
-- clean up session if it was created before
if l_session_id is not null
then
APEX_SESSION.delete_session(p_session_id => l_session_id);
end if;
return l_link;
end generate_page_link;We can generate the following URLs for example:
The key benefits of this approach are: