Using Oracle EPG to provide XML services for Flex applications

The Embedded PL/SQL Gateway (EPG) has been a feature of Oracle Database since version 10g. EPG enables a client to invoke a PL/SQL stored procedure through an embedded HTTP server. The content is generated dynamically using the PL/SQL Web Toolkit, a set of built-in packages provided in the database that handles the HTTP protocol.

This article provides an introduction to Oracle EPG for Flex users, outlines a simple PL/SQL framework, and shows how to create a Flex application that uses the framework.

Oracle EPG and XML

EPG is most commonly used to create dynamic HTML content, but since it provides control over the content and HTTP headers, you can easily use it to generate XML too. Oracle offers many ways to create and manipulate XML inside the database (for example, DOM, XQuery, and XPath) but this article will focus on one way of doing this using a simple framework based on pure PL/SQL. This method is suited for applications that need to access the database from Flex and that require neither the power provided by the other methods available nor the interoperability and metadata capabilities of Web Services (also supported natively by Oracle XML DB). Using this method you do not need additional application or HTTP servers, just Oracle Database 10g or later.

XML and Flex

One way to access and bind data in Flex is with the simple HTTPService class. This class requests the content of a URL and stores it in memory. This content can then be deserialized into different formats by setting the resultFormat property value of your HTTPService component to Object, Array, XML, E4X, Flashvars, or Text. The most common and flexible format is XML, and the best options for XML are Object, XML, or E4X. The choice is important because it affects how you reference elements as well as how the values will be cast in terms of data type. The E4X format is often the best option, because it treats the content as literal XML (String) and because the results can be easily accessed via ECMAScript for XML expressions (E4X).

Storing the application files

Another great feature of Oracle XML DB is the file repository inside the database that can be accessed via HTTP, WebDAV, or FTP. This file repository was designed to host XML content directly in the database but you can use it to store any file type or create any folder structure you want, just like a file system. It has many file controls such as ACL (Access Control List), version control, check-in/check-out, programmatic access to files via PL/SQL or Java, file events and more. Accessing the application files from the database, as opposed to the operating system, simplifies deployment because you don't need a HTTP server to host and publish them. This model also offers the advantage of centralizing security, access, and backup of these files together with your application data and business logic.

How Oracle EPG works

Oracle EPG is similar to the Apache extension module MOD_PLSQL available in earlier versions of Oracle Database (you can still use this method with Oracle HTTP Server). The stored procedure execution is done via URL reference. You define the database instance and username used to connect to the database using a Data Access Descriptor (DAD). With EPG you don't define the database instance since it is embedded in the database already. You give each DAD a name, which is referenced in the URL as a "path". The stored procedure name is referenced as the "program" and the optional parameters in the "query string" portion. The format is:

Advantages of database centralization

By having your data, business logic, and application files in one central place, the database, you reduce not only maintenance but complexity. Databases are designed to handle data efficiently using structures like caches, indexes, query optimizers, transactions, security, integrity, and recovery. With Oracle Database, stored procedures are commonly coded in PL/SQL, although you can use Java for this purpose too. PL/SQL is a very powerful language for data processing. It is tightly integrated with the database and it has a small footprint in terms of resources and code size for database intensive routines. When executing a service with numerous SQL statements, these features enable stored procedures to almost always outperform a middle tier application server executing the same statements on comparable hardware.

In addition, you can deploy stored procedures while the application is online. Any change can be applied as the application is running and it will be reflected immediately, as long as no code is invalidated. Stored procedures also reduce round-trips and save time because data sets don't need to be selected over the network since the procedures run in the database core.

Implementing a simple PL/SQL framework

Although it is not always necessary, having a framework to help automate repetitive tasks, standardize common features, and reduce coding is highly recommended. As you build XML dynamically using PL/SQL, at a minimum you will need to do the following:

• Define the HTTP MIME header for XML
• Begin/end the root element
• Begin/end a tag
• Define the XML declaration
• Print an element (text value)
• Replace entity references (special characters by placeholders)
• Handle messages, errors, and exceptions

The example framework for this article is named HPF (HTTP PL/SQL Framework) and it is packaged in the database. As the name implies, it is a general purpose HTTP framework that can handle several languages and formats. Because of this, there can be overlap between the package procedure names used for XML and other languages like HTML. Using a prefix helps to group them based on their purpose. Because the main focus of this article is XML and because the framework can be enhanced for many other languages, the package procedures listed below are all prefixed with "XML_".

• xml_header: Define the HTTP MIME header for XML and begin the root element
• xml_footer: End the root element
• xml_begin_tag: Begin a tag
• xml_end_tag: End a tag
• xml_print_element: Print a text element
• xml_message: Print an application message element
• xml_error: Print an application error element
• xml_exception: Print an application abnormal exception element

This basic framework provides facilities for messages, errors, and exceptions. Using these framework procedures Flex applications can provide appropriate feedback to the user with pre-defined XML elements. When there is an application exception, for example, your application can show a message in a pop-up window or display an alert message.

Creating stored procedures using the framework

The basic structure of any stored procedure using the HPF framework will be as follows:

The main sections in this structure are the header, footer, and exception. You can also add special code in the header, for example, to do any of the following without changing the dependent stored procedures:

• cancel execution if the user does not have the necessary privilege
• identify in runtime which stored procedure is running
• log or profile the execution
• check session expiration

In summary, the framework should have full control of all the most common features and repetitive tasks so you can focus exclusively on the business logic.

To build the XML that will be returned to Adobe Flex, you use the following three framework procedures: xml_begin_tag, xml_print_element and xml_end_tag. For example, suppose you want to build the following XML:

You would use xml_begin_tag to start the node element "supplier" and xml_end_tag to end it. The enclosed elements with values are created using the procedure xml_print_element. Using the framework in PL/SQL this is coded as follows:

Demonstrating the PL/SQL framework

To demonstrate how the framework is used, this section will show you how to create a simple supplier table to be displayed by Flex in a data grid. The application will also have basic search functionality to demonstrate passing parameters back to the stored procedure. The procedure name (or service) is named "list_supplier" and the Flex test application is named "query_supplier". To run this demonstration you will need access to two database users accounts, one with administrative (DBA) privileges (to create the DAD and adjust Oracle XML DB settings) and a second regular user account to run tests. You can run the demonstration tests as the privileged DBA user, but this can raise security concerns, especially if the database also hosts production data.

Follow these steps to install the demonstration:

1. From the flex_epg.zip file, extract the SQL files xdb_dad_config.sql, hpf.sql, and list_supplier.sql.
2. Connect to the Oracle instance as a user with DBA privileges and execute the following statement to create the test DAD and adjust some Oracle XML DB settings.

Note: these configuration steps are based on a standard Oracle Database installation. They are not intended to be performed in a production environment unless approved by the database and system administrators. They can break existing applications that rely on Oracle XML DB and cause conflict with other network listeners, especially other application or HTTP servers such as Apache and Tomcat.

If you use the Express Edition (XE) of the Oracle Database and you want to allow remote access of the embedded HTTP server, execute the following statement:

3. Connect to the Oracle instance as the test user.
4. Create the framework package by executing the SQL statement:

5. Create the supplier table and test service by executing the SQL statement:

6. Check if the framework and the test service were correctly created by typing the following URL in any browser. Replace "server" by the server name where the Oracle instance is running (see Figure 1).

7. From the file flex_epg.zip, extract the application files query_supplier.html, query_supplier.swf, query_supplier.mxml, AC_OETags.js, and playerProductInstall.swf.
8. Copy the extracted application files to the Oracle XML DB using FTP or WebDAV. The server name is the database server name where you created the framework and the test service. Put the files in the directory named "public", which is created by default in all Oracle XML DB installations.
9. To run the test application, type the following URL in any browser. Replace "server" by the server name where the Oracle instance is running (See Figure 2).

Here is the MXML code used to execute the service (see Figure 3):

Where to go from here

As you develop your own Flex applications using EPG, you will likely want to expand the basic framework outlined in this article to include other frequently needed capabilities. The procedures implemented for this article form a minimum set of capabilities for a simple framework. Here are some ideas you could implement to enhance this framework:

• Overload XML_PRINT_ELEMENT to handle other data types automatically, not only strings
• HTTP compression of the XML document
• Parallel or serial execution of stored procedures
• Session management to persist values across HTTP calls
• Session expiration
• User authentication and password encryption
• Access privileges to control who can execute what
• Logging to record when, how long, and who executed some stored procedure
• HTTP cache control
• Data paging for queries
• Microsoft Excel export
• Microsoft Excel upload
• Restrict execution of the HPF package via URL
• Send an email when an exception happens

You can find more information on developing Oracle Database applications in the following resources:

Oracle Database Advanced Application Developer's Guide
Oracle XML DB Developer's Guide