19 May 2008
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.
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.
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
Text. The most common and flexible format is XML, and the best options for XML are
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).
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.
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:
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.
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:
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.
The basic structure of any stored procedure using the HPF framework will be as follows:
create or replace procedure procedure_name ( p_parameter1... p_parameter2... ... ) as variable_declarations... cursor_declarations... type_declarations... exception_declarations... ... begin hpf.xml_header; initialization_code... if some_error_condition then hpf.xml_error('some_error_message'); end if; if not hpf.g_error_found then build_the_xml... hpf.xml_message('optional_successful_message'); end if; finalization_code... hpf.xml_footer; exception when others then hpf.xml_exception; end; /
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:
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_end_tag. For example, suppose you want to build the following XML:
<supplier> <supplier_code>S1</supplier_code> <supplier_name>Supplier #1</supplier_name> </supplier>
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:
hpf.xml_begin_tag('supplier'); hpf.xml_print_element('supplier_code','S1'); hpf.xml_print_element('supplier_name','Supplier #1'); hpf.xml_end_tag('supplier');
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:
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:
Here is the MXML code used to execute the service (see Figure 3):
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:
You can find more information on developing Oracle Database applications in the following resources: