Oracle Application Express (APEX) is a declarative, database-centric application development tool that runs within the Oracle Database. APEX does not fit the classic three-tiered physical model where applications are deployed to the application tier. The definitions of applications are stored in database tables within the Oracle Database. The application server tier manages browser requests and passes page "Gets" and "Posts" to the Oracle Database. In this way, the application server tier is basically a proxy to the APEX engine installed within the Oracle Database. For non-critical applications, or running on a laptop, the XDB listener with the Embedded PL/SQL Gateway (EPG) can be used as the web listener allowing you to run with a database-only configuration. Figure 12-1 shows this configuration.
Developing database structures and web applications using APEX has a number of interesting and somewhat unique characteristics. First, application development is done through a browser, completely over HTTP or HTTPS. Second, application definitions are stored as metadata in database tables. And third, the rendering of applications is accomplished by reading database table metadata for each request. This combination has a number of advantages. One advantage comes at application design time. With APEX, it is common to have multiple developers working on the same application and even the same page concurrently. An optimistic locking model prevents developers from overwriting each other's work, allowing multiple developers to work on the same project easily. Another advantage related to the metadata model is that APEX applications are run without deployment. After modifying an application, the changes are available the next time a developer tests his or her work.
APEX also heavily leverages the native strengths of the Oracle database platform. For example, with the Oracle Database, you have the ability to efficiently store and retrieve large amounts of metadata for large numbers of users concurrently without read locks. Another nicety is that developers can revert their changes using flashback queries; this enables the fat-fingered application developer the ability to export an application as it existed 5 minutes ago. APEX also takes advantage of Oracle database referential integrity and check constraints to ensure the application metadata retains its integrity.
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.
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).
Example 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;Obviously, the above example is very simplistic, but it illustrates the mechanism APEX uses to generate pages. Real-world web pages are much more sophisticated. They generate specific CSS and JavaScript as demanded by the application context, emit form controls, perform partial page renderings, and include many other operations used by modern web applications. APEX developers don't need to code using the Web Toolkit; with APEX, you take advantage of a declarative framework with built-in components and controls.
The genesis of Oracle Application Express (first called Flows, then Platform, then Marvel, then HTML DB, and finally Application Express) was the need to develop a better way to build web applications that improved both productivity and functionality. Writing PL/SQL pages manually has the following drawbacks:
Writing large amounts of code is time consuming and hard to maintain.
Manual coding lacks separation of user interface and application logic.
Manual coding has a much greater probability of being inconsistent.
Compared with declarative frameworks like APEX, applications that involve the hand coding of web pages can be very labor intensive. It takes time to handcraft code, and it takes even more time to maintain it, especially if you didn't write it.
Using a framework that has a clear separation of user interface and application logic is also a significant advantage. For example, you can use one developer to write the presentation layer and another to craft the application logic. This allows managers to take optimal advantage of specialized skills.
Perhaps the greatest drawback of coding by hand is inconsistencies. With declarative frameworks, controls are rendered using the same code, making application functionality consistent. Hand coded applications, in contrast, have diverging implementations. These code differences can make it hard to discover issues with globalization, accessibility, security, performance and aesthetic consistency. This also makes testing more time consuming, because similar controls on different pages may behave differently especially if they were developed by different developers.
The APEX architecture engine is designed to dramatically reduce code by allowing developers to declare page components, such as a report, and rely on APEX to provide the rich controls needed to render the results. The Application Express framework uses themes and templates to achieve a clear separation of user interface and application logic, and not only makes application pages consistent, but makes one application consistent with another. The combination of developer-defined page controls and templates is all managed by the APEX engine.
APEX is installed by running a SQL Plus installation script which creates database objects in a specific database schema. The SQL installation script calls many SQL files that create database tables, PL/SQL packages, procedures, functions, etc. APEX has a few privileged objects installed in the SYS schema as well. In addition to database objects, Oracle APEX has a large number of images, CSS, and JavaScript files that are typically installed in the application server file sytem.
Upgrading to a new patch level requires re-creating various database packages, changes to metadata, and new static files. Patching does not result in a new schema, so the 4.0.1 and 4.0.2 patches are both made within the APEX_040000 schema.
Upgrades to new versions of APEX do result in the creation of new database schema. For example, APEX 4.1 would install in an APEX_040100 schema. Upon installation, all APEX metadata is copied from the old schema to the new schema. In a last step, the public synonyms are pointed to the new version. This makes reverting to the previous version relatively simple as the database objects that comprise APEX are untouched, so reverting is mostly an exercise in re-pointing the synonyms. After upgrades the old schema is not removed, so after you are committed to the new version the old schema should be dropped to recover the space.
The Application Express application development environment is fully browser based. Applications are developed using wizards and by filling in property pages. The screen shot below (Figure 12-2) shows a page in the Application Builder environment. Clicking the Create Page > button will start a wizard to define a new page within the current application.
Under the covers, application development is just an exercise in metadata collection. The Application Builder is the user interface used by application developers to create applications. When Application Express was first created in August of 1999, seven years before it was released as a product, the metadata that defined applications was manually inserted using handcrafted SQL plus scripts. Once the product matured, a limited builder-style interface was created to make it easier to define applications. This interface, and subsequent iterations of the environment, were actually created with Application Express itself.
Writing Application Expess using Application Express has been a great opportunity to "eat your own dog food." The product has been developed and enhanced in this way for over ten years. As new capabilities are created they are folded into the more then 1,000 pages that make up the product.
Because APEX and applications developed with the tool are simply standard parts of the Oracle Database, when it came time to build a utility to export applications, the implementation was straightforward. The export utility would use calls to procedures that front-ended tables. So an application export is just a SQL*Plus script. When you select Export from the Application Builder home page, as shown in Figure 12-2, you can export your application or application components to SQL scripts that can be run on other instances of APEX to import the applications or components.
If you are building applications that you intend to be deployed on other systems, such as building on a development instance for deployment on a production instance, or if you wish to share your application with others, you can use supporting objects. Supporting objects are basically scripts that can be run on an application installation to create database objects, load seed data, and install images, and other file types. Supporting objects complete the definition of an application in that they include all of the tables, views, sequences, etc., that make up an application. Supporting objects also include a remove script that can cleanly remove all objects that define your application.
To fully understand the APEX architecture, it is also important to understand the metadata structures and how they are related. Simply put, an application is made up of pages. Pages are comprised of regions, processes, dynamic actions, computations, and branches. Regions can contain other regions, items and buttons. Applications also have authentication schemes, authorization schemes, user interface templates, tabs, additional items and other attributes. All of this metadata is stored in database tables. Database referential integrity and check constraints are fully leveraged to insure the integrity of the application. For example, removing an application will remove the metadata for that application as stored in 50 + tables.
Rendering a page is typically a call to the database procedure 'f' while passing an argument 'p'. Actually, 'f' is a public synonym for APEX_xxxxxx.f. The f stands for flows, which was the original name of Application Express, and the p stands for page. The relative URL request f?p=100:1 is a request to render page 1 of application 100. It would look something like the following:
http://myserver.mydomain.com/apex/f?p=100:1
When received by the mid tier, it will be turned into an anonymous PL/SQL block, conceptually:
begin
f(p=>'100:1'),
end;The APEX procedure 'f' will parse the colon-separated list of arguments passed to 'p' and then call the procedure WWV_FLOW.SHOW to orchestrate the rendering of the page. The application ID, 100 in our example, is used to look up and fetch key application attributes from the metadata tables in real time for each page (or partial page) request. To determine how to properly render a page, Application Express will first check the authentication method defined at the application level. Pages identified as public pages are exempted from authentication checks. If the page requires authentication, the authentication scheme is invoked and checked.
Once the authentication is checked, the remaining page metadata can be fetched and rendered. Authorization is managed by authorization schemes. An authorization can be applied at application level or at a component level. An authorization is a check that either succeeds or fails. For example, an application may wish to restrict access to a specific domain of users defined in a table. An authorization scheme called "is valid user" could be created that simply checks to see if the current user is contained in the table. For example:
select 1 from my_valid_users where user_name = :app_user
This named authorization scheme can be applied to an application to protect every page. It could be applied to a specific page, or applied to a specific component on a page. Conditions are also provided for page components. Conditions, if defined, are checked before rendering.
In our example the application and page attributes have been fetched, the application authentication scheme has been passed, and now the generation of the page HTML can start. Each page has an associated page template that defines the structure of the HTML page. The page template has substitution strings—for example, #TITLE#, #REGION_POSITION_01#, and #TAB_CELLS#. The page's region definitions are fetched from metadata and injected into the page template, thus building up a complete web page. Listing 12-2 is a page template snippet that includes the #TITLE# substitution string.
Example 12-2. Example APEX Page Template Snippet
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html lang="&BROWSER_LANGUAGE." xmlns="http://www.w3.org/1999/xhtml"xmlns:htmldb="http://htmldb.oracle.com" xmlns:apex="http://apex.oracle.com"> <head><title>#TITLE#</title> ...
The discussion so far illustrates page rendering; however, each page can also define page processing, or the events to be invoked when a page is posted. Figure 12-3 illustrates that application attributes for rendering and processing are differentiated within the Application Builder.
When a page is posted, the architecture is similar to page rendering. When APEX pages are posted they are submitted directly to the WWV_FLOW.ACCEPT procedure using syntax similar to
<form action="wwv_flow.accept" method="post" name="wwv_flow" id="wwvFlowForm">
Page accept processing is similar to page show processing, except that no user interface is generated. On page accept, application page metadata is fetched from tables and the corresponding page validations, processing, and branching are processed.
Page branch metadata defines the URL that is to be displayed after successful processing of the current page, typically another page in the application. Figure 12-4 shows how page 10 branches to page 8.
Most branches are implemented as redirects; however, you can create branches that simply call the "page show" method directly.
When a user runs an application, regardless of whether it is an authenticated or unauthenticated application, they are assigned a random but unique session ID. This session ID is maintained on "page gets" by passing the numeric session identifier in the third position on the f?p syntax:
f?p=<application>:<page>:<session ID>
"Page posts" pass the session ID as a hidden form element. The session ID maintains the user's context in the application and allows the user to have access to session state set on previous page views.
Session state is automatically maintained for page items—developers need not write any code to save session state. An example of a page item is displayed in Figure 12-5. The name of the page item in the application is P1_SEARCH, and it is displayed as a large input text field. When the page is posted, the text entered is set as session state for the item P1_SEARCH within the user's session.
All page items and application items must be uniquely named within an application. All session state is global, meaning that session state values can be accessed from any page or application component. Session state can also be set using the f?p syntax —see Listing 12-3 below which includes the full f?p syntax.
Example 12-3. Full APEX f?p syntax
f?p=<application>:<page>:<session ID>:<request>:<clear cache>:<item names>:<item values>
The relative URL f?p=100:2:2244749031848871:::2:P2_ID:12345, is a request to render page 2 of application 100, for the session 2244749031848871. It directs any existing session state on page 2 to be removed and sets the value of the item P2_ID to be 12345. Be aware that application developers normally never have to write code or even create these links; they are generated by declarative page controls, such as a page branch.
Page posts and URLs that set session state are two examples of setting session state. Session state can also be set in PL/SQL using bind variable assignments. Listing 12-4 shows the PL/SQL source of an example APEX page process that queries an Oracle table then sets an item using the PL/SQL assignment syntax.
Example 12-4. Example PL/SQL Page Process That Sets Session State
BEGIN
FOR C1 IN (SELECT NAME
FROM PROJECTS
WHERE ID = :P2_ID) LOOP
:P2_NAME := C1.NAME;
END LOOP;
END;When APEX executes PL/SQL that assigns values to bind variables, it ensures that the session state is maintained. The code in Listing 12-4 will allow the bind variable :P2_NAME to be referenced in subsequent SQL statements, or using the &P2_NAME. syntax to reference session state in non-SQL contexts.
Page computations can set application item or page item values using a variety of methods. Computations provide a convenient and well-structured method for setting session state and are very useful for setting static values. Figure 12-6 shows a step in the wizard used to create computations.
Session state can be set for application items and page items. Application items are not displayed on any page and are simply used to maintain state. Page items have the dual role of maintaining state and displaying values. A page item's definition can be assigned using a number of methods. In Figure 12-7 the value for item P2_PROJECT is set from a database column value.
You can also set the session state from a source value "always from source" or "only when the value of the item is null". The actual source can come from a static text assignment, SQL query, SQL Expression, or PL/SQL code snippets. This provides fine-grained control and flexibility in managing your session state using just a few declarative attributes.
Referencing session state is accomplished using different methods, with the access method depending on the context. To access session state from SQL or PL/SQL you use bind variable syntax, with a colon before the name of the variable. The SQL query in Listing 12-5 demonstrates how a report region query can use a bind variable syntax.
Example 12-5. Example SQL Query Referencing Session State Using Bind Variables
select name, owner from projects where project_id = :P2_PROJECT_ID
The same syntax also works in PL/SQL.
Application pages can define page processes of type PL/SQL. Listing 12-6 is an example of a PL/SQL process referencing session state.
Example 12-6. Example PL/SQL Page Process Referencing Session State Using Bind Variables
begin
insert into project_action_items (
project_id, action_name, action_date)
values (
:P2_PROJECT_ID, :P2_ACTION_NAME, :P2_ACTION_DATE);
end;Session state can also be referenced in templates and general text attributes such as field labels using the &<ITEM_NAME>. syntax:
&P2_PROJECT_ID.
Lastly, session state can be referenced from database stored procedures, functions, and triggers using the V('<MY_ITEM>') syntax:
v('P2_PROJECT_ID')At this point we have discussed various methods of setting session state, including the following:
The URL
By posting a form page
Page processes
Page computations
Page item source and default values
We have also discussed the following methods of referencing session state:
Within SQL using bind variables
Within HTML APEX attributes using
&<ITEM_NAME>. syntaxWithin PL/SQL application and page processes
APEX maintains session state for all users in database tables, one table for sessions and another table for detailed session state. The APEX engine automatically deletes session state for expired sessions. Session state can also be set and removed using published APIs. APEX has a number of built-in session state items that are used internally by the APEX engine and are available for use by application developers. Built-in APEX items include, but are not limited to
Three application server alternatives are available for Application Express:
Oracle HTTP Server (Apache)
Java Server (Glassfish or Web Logic Server) with the Oracle APEX Listener
Embedded PL/SQL gateway
The first and second options are recommended for high loads and production applications. Configuration and setup of these are beyond the scope of this chapter; however, the basic asynchronous architecture and connection pooling are important concepts.
A connection pool is a set of connections with a minimum and maximum number of connected sessions defined. Each connection is connected as an unprivileged database user, typically APEX_PUBLIC_USER. Each connection services a request and then is immediately available to service other requests. Each connection is initialized before use and can handle any request from any workspace or application. This asynchronous architecture does not support transactions that span requests; however, the scalability is substantially better when compared with a synchronous connection architecture. As discussed above, session state is maintained on the server side so logical sessions are maintained by passing a session ID with each request.
To facilitate monitoring of application activity, each individual application page view is logged in a row in a log table. This data is available as the APEX_ACTIVITY_LOG view. This view provides the elapsed page generation time, user, application, page, workspace, report engine rows fetched, and other details.
APEX uses two identical log tables that are unioned together and exposed in APEX_ACTIVITY_LOG. The APEX engine writes to one log table for two weeks, then switches and writes to a second identical table. Two weeks later, it truncates the first table and writes to the first table. This provides between two and four weeks of logged activity. Both page posts and page show events are recorded. Partial page rendering is also recorded. The timestamp of the page view is indexed to enable efficient access to recent page views.
The activity log is summarized daily and exposed as the view APEX_ACTIVITY_LOG_SUMMARY. This summary view provides an efficient way to view application usage and performance over time. It contains one row for each active application for each day. Page view counts and other statistics are aggregated. This view is never deleted from or purged.
Application developers and administrators interested in viewing activity log data can do so from the workspace administration pages within APEX. Alternatively, they can write their own SQL queries against the provided views, as shown in Listing 12-7. Reviewing the activity logs can be a productive way to locate potential tuning opportunities.
Example 12-7. Example SQL Query to Locate Popular Poorly Performing Pages
select to_char(timestamp,'YYYY.MM.DD.HH24') hour,
application_id,
page_id,
count(*) page_events,
sum(elap) eplased_time,
count(*) * sum(elap) page_weightfrom apex_activity_log group by to_char(timestamp,'YYYY.MM.DD.HH24') order by 6 desc
APEX reports, conditions, charts, processes, and a variety of other components, described in depth within this book, allow for the use of SQL and or PL/SQL. As described earlier, the APEX engine is the "executive" which is responsible for rending pages. The APEX engine reads the metadata required to service requests using the PL/SQL packages that comprise APEX. Internal metadata queries are constrained by the workspace ID, and frequently also application ID, page ID, region ID, etc.
If you take the example of a SQL report region, the APEX engine will query the report SQL text from the APEX metadata. It will then parse the SQL as the parsing schema of the application. Each application defines a parsing schema which is checked to ensure that the corresponding workspace has the privilege to parse as this schema. Workspaces can have one or more assigned schemas, so the application's parsing schema defines how the SQL within the application is to be parsed. Before executing any SQL, the APEX engine will locate bind variables and bind in corresponding session state. If no session state is set for a given bind variable, a null will be bound. If the bind variable referenced does not exist in the application, a null will also be bound. Listing 12-5 illustrated a SQL query with a bind variable.
PL/SQL can also exist in many contexts within APEX. The most popular uses of PL/SQL within APEX applications include
Snippets or fragments used for validations and component conditionality
Page and application-level processes
Regions of type PL/SQL
Applications are developed and deployed within a workspace. Figure 12-8 shows the Oracle Application Express login page. Application developers log in to a workspace and provide a username and password specific to that workspace.
Any number of workspaces can be created in an instance of APEX. Workspaces can be created manually, by logging in to the instance administration application, or Application Express can be set up to allow self service, where application developers can request a workspace. Each workspace is assigned one or more physical database schemas. Figure 12-9 illustrates workspace-to-schema assignment options. Typically, each workspace is assigned one schema, and no two workspaces have access to the same schema.
The physical tables that manage the APEX metadata are "striped" with a numeric workspace identifier (internally called SECURITY_GROUP_ID). This workspace ID isolates each workspace's application metadata, similar to how the Oracle data dictionary isolates one database schema from another. Also, like the Oracle data dictionary, APEX provides a number of views, including APEX_APPLICATIONS, APEX_APPLICATION_PAGES, APEX_APPLICATION_PAGE_REGIONS, and more. These views can be accessed from other tools like Oracle SQL Developer and Oracle SQL Plus. When used outside of APEX, the query, SELECT APPLICATION_NAME FROM APEX_APPLICATIONS, is automatically constrained by the workspace(s) that are associated with the current schema. Because of this, it will return different rows depending on which database user you are connected as.
Within APEX, you can write your own SQL INSERT, UPDATE, and DELETE statements or, if you build forms on tables, you can have Application Express automatically manage data manipulation for you. If you use the "Create form on a table" wizard, you will define the application metadata needed to allow Application Express to manage the INSERT, UPDATE, and DELETE of the data.
APEX uses an optimistic locking model. An optimistic locking model assumes that when an application attempts to update a row, another user has not changed it since the application queried it. If a row an application is updating is changed between the time the row is queried and the update is posted, then the update is rejected and an error message is returned to the user. If the update was instead allowed to succeed, the one user would unknowingly overwrite changes made by other users, thus creating a lost update.
To make this easy and declarative, APEX provides a built-in, and mandatory, implementation of optimistic locking using checksums. When a row is fetched using a built-in page rendering fetch process, the fetched column values are checksumed. When the page is posted, each checksum calculated on the fetch is compared with the checksum of the displayed columns in the database. If the checksums match, the update is allowed to go through. APEX also provides the same automatic protection of lost updates for tabular forms.
APEX developers have significant declarative control of user interface. The primary control is templates. Templates provide the HTML content for pages and page components. Templates are organized into themes. You can use themes supplied as part of Oracle Application Express, you can create your own themes, or you can import themes developed by others. When this doesn't provide what you need to get the look or functionalty you're after, you have the following choices:
Create custom user interface templates.
Generate your own static HTML.
Generate dynamic HTML using PL/SQL.
Use plug-ins.
Templates are used to define the look, feel, and some of the functionality for page, region, item label, list, breadcrumb, report, calendar and button components with Oracle Application Express. Each theme contains several variations and alterantive templates of each type. You can create new templates or edit exiting templates to achive a specific user interface. Figure 12-10 shows a button template. The button template references the #LABEL# and #LINK# substitution strings. When buttons are created, corresponding properties of the button are substituted. This makes the button template generic.
Page templates are more powerful and more complex than button templates, but the functionality is implemented in the same way. Structured content is used to replace substitution strings in reusable components. An easy way to create a new template is to simply copy an existing template and customize it.
A simple and obvious way to create a customized user interface is to simply create a region of type HTML. HTML regions simply display the HTML content. Three types of HTML regions exist: HTML, HTML with Shortcuts, and HTML that escapes special characters. The standard HTML text simply renders the HTML as typed. If you are comfortable writing static HTML, this can be a convent way to create static page content.
HTML with Shortcut regions allows you to type HTML, with double quoted shortcut names. The example in Figure 12-11 can be referenced in the source of an HTML region using "DELETE_CONFIRM_MSG". Basically, regions of type HTML with shortcuts parse the HTML for double quoted shortcuts, and when found replace the double quoted text with dynamically or staticly defined content. The example of the DELETE_CONFIRM_MSG allows the message to be defined in one place yet referenced many places.
Sometimes you may want full control over HTML—sometimes you just have a need to define a unique display control that is not available by any declarative component. You can do this by creating plug-ins which define your own declarative display controls; for example, region controls or item controls (see Listing 12-8). Additionally, you can also render content using PL/SQL region types. A PL/SQL region allows you to generate anything you want into a page. This gives APEX developers the ability to generate virtually any HTML page content.
Example 12-8. PL/SQL Region Used to Generate HTML
declare
c := 0;
begin
sys.htp.p('<div class="nice-format"><strong>Past Due Projects</strong>: <span>'),
for c1 in (select name from projects where status = 'OPEN' order by due_date asc) loop
c := c + 1;
if c > 1 then
sys.htp.prn(', '),end if;
sys.htp.prn(sys.htf.escape_sc(c1.name));
end loop;
sys.htp.prn('</span></div>'),
end;The example in Listing 12-8 is very simplistic. It displays a comma separated list of project names that are past due. The sys.htp.p call prints text with a trailing newline, the sys.htp.prn call prints text without any trailing newline. The sys.htf.escape_sc function is a function in the PL/SQL Web Toolkit that escapes special characters to retain the integrity of your generated HTML.
Writing custom PL/SQL regions is powerful—plug-ins give you exact control over the user interface. Some custom development efforts can be reused. For those cases where custom code is reusable, you can convert the logic into an Oracle Application Express plug-in. Once a plug-in is created, it can be installed into other applications, thus making the component reusable. Plug-ins function like native declarative controls and can be used for regions, items, processes, and dynamic actions. The example in Figure 12-12 shows a step in the Create Region wizard. Once the region type of Plug-in is selected, the developer is prompted for the plug-in to be used and then, in a later wizard step, for the various plug-in attributes.
APEX uses the Model-View-Controller architectural pattern to isolate the user interface look (presentation) from the data manipulation (logic). Presentation is controlled by a number of declaratively defined attributes, starting with the page template. When defining the rendering of a page, the application developer may choose templates. Templates control the HTML used to render application components. Applications and pages are discussed at length in this book, but the point here is that the logic used to render or display the data is separate and distinct from the logic used to update database data.
Page processing, or data manipulation, is typically controlled by page processing attributes, most commonly page processes. Page processes do not define user interface, except for error messages to be raised in the event they fail.
The majority of the look and feel of an application is governed by the templates that are assigned to display components such as pages, regions, items, and buttons. Many templates, of all types, are combined into what is known as a theme. An APEX theme is selected when creating an application. If you wish to change your look and feel you can do so by switching themes. Switching a theme will map the templates used in the current theme to templates in the new theme. Each template type has many classes but each template has only one assigned class. These template classes are used to appropriately map the new theme templates, to the previous themes templates. For example, a given theme may have five different button templates, each template with its own class. When switching themes, the button template from the old theme is mapped to the same template class in the new theme.
Oracle Application Express is a declarative, database-centric application development tool that is installed and runs within the Oracle Database. APEX is implemented in PL/SQL and stores metadata in Oracle Database tables. This architecture leverages the scalability, reliability, and availability of the Oracle Database to deliver a very compelling platform for application development.
APEX is an application development platform that has been optimized for building applications that interact with Oracle Database data and are developed by application developers with a strong level of comfort with SQL. Knowing PL/SQL can allow a developer to really exploit all the APEXfunctionality. PL/SQL knowledge is not required to build many basic applications, but it is required for selective components of advanced applications and is a strong asset.
I hope you enjoy using APEX as much as we have enjoyed creating this product. I am especially proud of the development team, and the extradinary developer community. Together we are ever improving an outstanding product. Two people who interacted with thousands of customers, who answered countless questions, and in the process were able to contribute mightily to the development of a better Application Express are Carl Backstrom and Scott Spadafore. Both Carl and Scott made enormous contributions to the product. I can remember so many conversations with them about how to implement new features and improve existing ones. Their knowledge of the code as well as their knowledge of how customers use the product was a spectacular combination. They will both be greatly missed.