Search engine optimization (SEO) is a complex combination of website qualities that combine to improve the site’s ranking by search engines. As we all should hope, the most important factor is content. The goal of search engines is to display the most relevant, compelling and current content to users. Accordingly, the most effective use of time-related to SEO is to ensure that you are providing the best content you can. Once you have met that very high bar, it is time to consider the many other factors that affect search engine rankings. A good place to start, particularly with APEX applications, is to create and register a Google Sitemap.
Why register a Google Sitemap?
Google’s default crawling algorithm is likely to miss a lot of content within an APEX application. The APEX URL format consists of the pattern https://[host]/[ords_mapping]/f?p=…….. This is essentially a single location with a parameter. Google may continue to crawl subsequent links but it often will not. This URL format often represents an extensive list of catalogue options or other content that search engines discount as less relevant. Registering a sitemap lets Google (and others) know that you want the pages to be indexed and shown in search results.
The range of APEX applications is vast and SEO may not apply to a significant portion. I’ll focus on the most likely scenario for this example: a public application (or application with significant public portions) that has some pages that are parameterized. There are techniques to index content behind paywalls and logins. These techniques may show up in a subsequent post. Additionally, it is difficult to index modal APEX pages. I have developed a technique for this which may also show up as a subsequent post.
The general process follows:
Create a sitemap in the Google format
Create a REST service to make the sitemap available via a URL
Register your sitemap
Wait for all the new traffic 🙂
Create a Sitemap in the Google Format
You can simply follow the instructions in this blog post to get a functional Google sitemap. To fully understand Google sitemaps, and get started on Search Engine Optimization, you should start at the link below.
https://support.google.com/webmasters/answer/183668?hl=en
This blog post provides code and instructions to create a basic XML sitemap in the Google format:
<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<url>
<loc>http://www.example.com/foo.html</loc>
</url>
</urlset>I utilize a package to generate the XML and a REST service to expose it via a URL. The package below has two procedures that demonstrate different aspects of a sitemap.
PAGE_XML can be used with any APEX application. It does not require any non-APEX tables or any specific pages. Below is an example of XML generated by this procedure.
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<url>
<loc>https://insum.talan.com//apex/f?p=373373:1:0</loc>
</url>
<url>
<loc>https://insum.talan.com//apex/f?p=373373:3:0</loc>
</url>
<url>
<loc>https://insum.talan.com//apex/f?p=373373:5:0</loc>
</url>
...
<url>
<loc>https://insum.talan.com//apex/f?p=373373:12:0</loc>
</url>
<url>
<loc>https://insum.talan.com//apex/f?p=373373:44:0</loc>
</url>
</urlset>CATALOG_XML requires an additional table, MY_CATALOG, and the application is expected to have PAGE 123456 as a detail page that does not have session state enabled. This code provides examples of how to add parameterized pages to your sitemap. Below is an example of XML generated by this procedure.
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<url>
<loc>https://insum.talan.com//apex/f?p=373373:1:0</loc>
</url>
<url>
<loc>https://insum.talan.com//apex/f?p=373373:3:0</loc>
</url>
<url>
<loc>https://insum.talan.com//apex/f?p=373373:5:0</loc>
</url>…
<url>
<loc>https://insum.talan.com//apex/f?p=373373:12:0</loc>
</url>
<url>
<loc>https://insum.talan.com//apex/f?p=373373:44:0</loc>
</url>
<url>
<loc>https://insum.talan.com//apex/f?p=373373:123456:0::::P123456_ID:1</loc>
</url>
<url>
<loc>https://insum.talan.com//apex/f?p=373373:123456:0::::P123456_ID:2</loc>
</url>
<url>
<loc>https://insum.talan.com//apex/f?p=373373:123456:0::::P123456_ID:3</loc>
</url>…
<url>
<loc>https://insum.talan.com//apex/f?p=373373:123456:0::::P123456_ID:241</loc>
</url>
<url>
<loc>https://insum.talan.com//apex/f?p=373373:123456:0::::P123456_ID:242</loc>
</url>
</urlset>The package spec and body are below. You’ll want to comment out CATALOG_XML or modify it to meet your table structure needs. This package needs to reside in the APEX workspace database schema because it relies on APEX workspace views. In this example, I am using the schema ANTON. (Note: I may use the terms “schema” and “user” interchangeably to reference a classic Oracle database user account.)
create or replace package MY_SITEMAP as
-- This procedure creates a sitemap that includes all public pages
-- in the application with p_app_id with the following exceptions:
-- - pages that have a build set that is not "Include"
-- - modal pages
-- - pages without URL access, e.g. page 0
-- - page 101 because it is typically the login page
procedure page_xml(p_app_id in number);
-- This procedure is specific to an application that utilizes the table "MY_CATALOG."
--
--
-- This procedure creates a sitemap that includes all public pages
-- in the application with p_app_id with the following exceptions:
-- - pages that have a build set that is not "Include"
-- - modal pages
-- - pages without URL access, e.g. page 0
-- - page 101 because it is typically the login page
--
-- Additionally, this procedure will generate sitemap entries for a specific
-- parameterized page (page 123456).
procedure catalog_xml;
end;
/create or replace package body MY_SITEMAP as
-- see package spec for usage
procedure page_xml (p_app_id in number) as
l_xml xmltype;
begin
begin
select
xmlElement("urlset", xmlattributes('http://www.sitemaps.org/schemas/sitemap/0.9' as "xmlns")
, xmlAgg(
xmlElement("url",
xmlElement("loc", APEX_UTIL.HOST_URL (p_option => null) || '/apex/f?p=' || aap.application_id ||':'|| aap.page_id ||':0') the_xml
)
)
) the_xml
into l_xml
from apex_applications aa
inner join APEX_APPLICATION_PAGES aap on aap.application_id = aa.application_id
left outer join apex_application_build_options bo
on bo.build_option_name = aap.build_option
and bo.application_id = aap.application_id
where aa.application_id = p_app_id
-- do not return modal pages
and aap.page_mode = 'Normal'
-- only show public pages
and (aap.page_requires_authentication = 'No' or aa.authentication_scheme_type = 'No Authentication')
-- do not return pages without URL access, e.g. page 0
and aap.page_access_protection != 'No URL Access'
-- do not return login page
and aap.page_id not in (101)
-- check build status
and (bo.build_option_status is null or bo.build_option_status = 'Include');
htp.p('<?xml version="1.0" encoding="UTF-8"?>');
-- Note! This will only output 32K.
-- If you anticipate more than 32K, consider chunking or other options.
htp.p(l_xml.getClobVal());
exception
when others then htp.p('Err: ' || sqlerrm);
raise;
end;
end page_xml;
--
--
-- see package spec for usage
procedure catalog_xml as
l_app_id integer := 373373;
l_page_id integer := 123456;
l_param_name varchar2(128) := 'P123456_ID';
l_xml xmltype;
begin
begin
select
xmlElement("urlset", xmlattributes('http://www.sitemaps.org/schemas/sitemap/0.9' as "xmlns")
-- URLs for all the standard (not parameterized pages)
, (select xmlAgg(
xmlElement("url",
xmlElement("loc", APEX_UTIL.HOST_URL (p_option => null) || '/apex/f?p=' || aap.application_id ||':'|| aap.page_id ||':0')
)
)
from apex_applications aa
inner join APEX_APPLICATION_PAGES aap on aap.application_id = aa.application_id
left outer join apex_application_build_options bo
on bo.build_option_name = aap.build_option
and bo.application_id = aap.application_id
where aa.application_id = l_app_id
-- do not return modal pages
and aap.page_mode = 'Normal'
-- only show public pages
and (aap.page_requires_authentication = 'No' or aa.authentication_scheme_type = 'No Authentication')
-- do not return pages without URL access, e.g. page 0
and aap.page_access_protection != 'No URL Access'
-- do not return login page
and aap.page_id not in (101, l_page_id)
-- check build status
and (bo.build_option_status is null or bo.build_option_status = 'Include')
)
-- URLs for the parameterized page l_page_id
-- repeat this section for each parameterized page
, (select xmlAgg(
xmlElement("url",
xmlElement("loc", APEX_UTIL.HOST_URL (p_option => null) || '/apex/f?p=' || l_app_id ||':'|| l_page_id ||':0::::'||l_param_name||':' || mc.id)
)
)
from my_catalog mc
)
--)
)the_xml
into l_xml
from dual;
htp.p('<?xml version="1.0" encoding="UTF-8"?>');
-- Note! This will only output 32K.
-- If you anticipate more than 32K, consider chunking or other options.
htp.p(l_xml.getClobVal());
exception
when others then htp.p('Err: ' || sqlerrm);
raise;
end;
end catalog_xml;
end;
/Create a REST service to make the Sitemap available via a URL
The easiest way to make the PL/SQL procedures available is to use Oracle REST Data Services (ORDS) to create a REST service. It is my preference to create a separate schema with limited privileges.
Create a user for REST services
Log in as a privileged/DBA user and create a user for REST services.
create user anton_rest identified by mySuperSecretAndLongPassword;
grant create session to anton_rest;
Grant execute on my_sitemap
Log in as the workspace owner and grant execute on my_sitemap.
grant execute on my_sitemap to anton_rest;
Create the REST Service
Using Oracle SQL Developer (version 4 or higher) Log in as the REST user (anton_rest).
Just to make sure we have everything working, we’ll test the output of our sitemap.
Show OWA Output so that you can view the results of the htp commands used in the procedure within SQL Developer.
Enable the OWA Output for your connected session.
Run the following code and view the OWA Output. Be sure to use set p_app_id to an application that has at least one public page.
begin
anton.my_sitemap.page_xml(p_app_id => 373373);
end;
/
Your OWA Output will be one long string. Scroll to see that it contains your anticipated output.
Use ORDS features to create the service
Enable REST for the schema by right-clicking on the connection name and selecting REST Services > Enable REST Services.
On the subsequent screen, check Enable Schema. If you wish to hide the schema name from the URL, you can put another name in the alias.
Click Next and Finish to allow you to build REST services.
Expand REST Data Services, right click on Modules, and create a new module.
Specify Template
When specifying the template, you can use a bind variable. In this case, I’m creating a template that allows me to create a sitemap for any application based upon :APP_ID by entering the URI Pattern
/pages/:APP_ID
Click Next and Finish.
At this point we have a template but have not told it how to handle a request. There are several types of http requests (GET, POST, PUT, DELETE) each of which can be handled separately. In our case, we’ll create a GET handler.
Create a Source Type of PL/SQL.
Paste in the code used to generate the sitemap XML using the bind variable:
begin
anton.my_sitemap.page_xml(p_app_id => :APP_ID);
end;
In order to edit the GET handler in the future, right-click on the handler and Edit.
At this point, you have a functioning REST service and sitemap. You can test it at:
https://yourmachine/ords/other_name/sitemap/pages/373373
other_name = the schema alias entered when you enable REST services on the schema
sitemap = module URI prefix
pages = URI pattern
373373 = your application ID
If you enter the URL in your browser you will see the output. If you view the page source you will see the XML.
Register your sitemap to “turn on” your Search Engine Optimization
You have to tell Google that your sitemap exists and where to find it. You can either use the Google webmaster tools or add the sitemap to your robots.txt file. Both methods are covered in the link below.
https://support.google.com/webmasters/answer/183668?hl=en
Note: At one point Google required your sitemap to have a url that appeared to be a static file, e.g. https://yourmachine/ords/other_name/sitemap/pages373373.xml. This is not currently a requirement, but if it returns, you can create a URI template pages373373.xml with a GET handler containing the following code:
begin
anton.my_sitemap.page_xml(p_app_id => 373373);
end;Similarly, you can create URI templates for sitemaps that include catalogs of content.
Wait for all the new traffic
Prior to implementing your sitemap, you should determine your current traffic level. Then you’ll be able to compare your results after implementing the sitemap and registering it with Google. I’d love to hear your Search Engine Optimization results.
