Databases Reference
In-Depth Information
These are interesting examples of the benefits of rendering applications from the data tier in real
time using metadata stored in database tables, but far and away the greatest advantage is avoiding
network traffic and context switching. Modern dynamic applications frequently require running many
SQL statements to display or process a given web page. For example, a data entry form may need to
populate many lists of values, as well as display master-detail data. With APEX, all of the SQL statements
needed to display or process web pages are performed in a single database call. The context switching or
“chatty” part of an application is managed on the same tier between SQL and PL/SQL, the
implementation language. Because the Oracle Database has a highly mature database, the SQL to
PL/SQL context switching is hyper-efficient.
Oracle APEX Engine
The Oracle APEX engine is installed within the Oracle database. Specifically, APEX is installed into the
APEX_xxxxxx database schema, with the “xxxxxx” representing the product version number (e.g.,
APEX_040000). APEX is comprised of about 500 tables and 300 PL/SQL packages, procedures, and
functions. Other database objects including synonyms, views, triggers, and indexes are also utilized.
The APEX database objects perform two major functions. First, they store the application metadata
in database tables, and second, they contain the logic used to read the metadata and render web pages.
The APEX_xxxxxx schema is locked, and therefore not directly accessed. A limited number of entry
points (PL/SQL procedures and functions) are granted to the public. In this way, applications can invoke
APEX but have no direct access to the application metadata. When the APEX engine receives a request to
render a page, the metadata required to perform the request is fetched from database tables, and a web
page is generated using the PL/SQL Web Toolkit installed in the SYS schema. The PL/SQL Web Toolkit is
a publicly executed set of PL/SQL packages used in conjunction with a web listener to render pages.
Let's take an end-to-end example to illustrate how this process works. First, a request comes from a
browser and the request is passed to the database by the middle tier as a PL/SQL call. APEX PL/SQL, run
by the Oracle APEX Engine, fetches the metadata needed to service the request, generates the page
content using the PL/SQL Web Toolkit, and completes. Upon completion, the middle tier reads the
generated content and returns it to the browser, freeing the database session for another user.
To better understand how APEX generates HTML and transmits this HTML to the browser, it is
useful to understand how the PL/SQL Web Toolkit works. Listing 12-1 shows how a simple HTML page
can be generated using the Web Toolkit. No APEX is used here, just the PL/SQL Web Toolkit. The
SYS.HTP package is part of the PL/SQL Web Toolkit and provides a conduit by which PL/SQL can
generate an HTML web page. Under the covers the
SYS.HTP.P
procedure populates a PL/SQL array.
When page rendering is complete the content can be read using the
SYS.OWA UTIL.SHOWPAGE
function,
which simply dumps the content of the PL/SQL array. The code to read the generated page content
using OWA_UTIL.SHOWPAGE is part of Apache MOD_PLSQL, Oracle APEX Listener, and the Embedded
PL/SQL Gateway (EPG).
Listing 12-1.
Example of HTML Page Rendering Without APEX Using the PL/SQL Web Toolkit
begin
sys.htp.p('<html>');
sys.htp.p('<body>');
sys.htp.p('hello world');
sys.htp.p('</body>');
sys.htp.p('</html>');
end;
