This might be one of the most common questions when introducing APEX to someone used to a totally different web stack. Or, as I’ve witnessed it many times, to a beginner or intermediate APEX developer who was to focused on building apps, but not really trying to understand how APEX really works. Here’s an explanation:
PL/SQL Web Applications
What are those? Well, a web application written in PL/SQL is a set of subprograms that interact with web browsers through HTTP. APEX itself and the applications it produces is exactly that: PL/SQL code that interacts with the web server of choice, whether EPG, Apex Listener or Oracle HTTP server.
In order to develop and execute a PL/SQL web application, you need to use two components:
- The PL/SQL Gateway
- The PL/SQL Web Toolkit
The full Oracle documentation about Developing PL/SQL web application is available in the Oracle® Database Advanced Application Developer’s Guide (11g release 2) here: http://oracle.su/docs/11g/appdev.112/e10471/adfns_web.htm
Developing PL/SQL web applications is not something that is unique to APEX, as there are other web 2.0 frameworks based on the same concept, such as FormSpider, created by a team from the Istanbul Technical University, not to mention custom web applications developed from scratched based on the PL/SQL Toolkit API.
When going beyond the features that APEX offers out of the box, it is important to have some basic knowledge about the technology stack and more important, have that information presented in way that relates to you, the APEX developer.
The PL/SQL Gateway
The PL/SQL Gateway enables a Web browser to invoke a PL/SQL subprogram through a HTTP listener. As by Oracle’s official documentation,
“the gateway is a platform on which PL/SQL users develop and deploy PL/SQL Web applications.”
Mod_plsql is the widespread implementation of the PL/SQL Gateway and it consists of an Apache extension that allows rendering dynamic web pages from executing PL/SQL code.
MOD_PLSQL was previously called the Oracle PL/SQL Cartridge and Oracle Web Agent (OWA).
Mod_plsql uses a two-tier architecture where clients interact with the databases. The Oracle database interacts with the Oracle HTTP Server, through the mod_plsql extension. Thus, the HTTP server will dynamically generate HTML code, interpreted by the web browser. Similar architecture and web page generation is found in the mod_php Apache extension that generates web pages from PHP code.
Another implementation of the PL/SQL Gateway is the Embedded PL/SQL Gateway (EPG), a simple implementation that runs inside the XML DB component. EPG is simpler to install and configure, but has only the core features of mod_plsql. Oracle XE comes by default with EPG and the only way APEX can be installed is through the Embedded PL/SQL Gateway. As of Oracle XE 11g, APEX is bundled with the installation package and there is no need to install or configure anything on the Application Express side. For the other versions of the Oracle Database, the Standard and Enterprise editions, you can choose what web server to install.
The PL/SQL Web Toolkit
The PL/SQL Web Toolkit is a set of packages that makes use of the PL/SQL Gateway to generate web pages from stored subprograms. The PL/SQL Web Toolkit API is used to generate HTTP headers, set browser cookies, get information about an HTTP request and generate HTML pages.
The PLS/SQL Web Toolkit and the PL/SQL Gateway are the two main components that hide behind Oracle Application Express. WebDB, Flows and HTML DB, all the products that existed before APEX, made use of these components. This is how the APEX development tool was created and this is how the APEX generated pages are rendered.
The most commonly used packages in the PL/SQL Web Toolkit are HTP (Hypertext procedures) and HTF (Hypertext functions) that generate HTML tags.
For example, to generate a simple web page with a title, some text and a URL link, create this procedure:
CREATE OR REPLACE PROCEDURE hello_world AS
HTP.TITLE('A PL/SQL generated web page');
HTP.HEADER(1, 'Created using the HTP package');
HTP.HEADER(2, '... part of the Pl/SQL Web Toolkit');
After the mod_plsql implementation is configured to allow web access to this procedure, accessing it from the web browser will result in this:
When creating a page with a report based on a table, it will populate the APEX internal tables with metadata: what report belongs to what page, what data it uses, who has access to it and so on. When a request is made the web server to display the page, APEX will call stored procedures that assemble the metadata to create HTML code, using the PL/SQL Web toolkit.