Developing PL/SQL Web Applications in Oracle10g: An Overview

PL/SQL has numerous features that can be used to make the database accessible on the Web and help to make back office data reachable on the internet. In general, a Web application that is written in PL/SQL consists of a set of stored procedures that works together and interacts with Web browsers through HTTP (Hypertext transfer protocol) protocol.

A set of dynamically generated and interlinked HTML pages constitutes the user interface of a web application. Delivery of Web content with PL/SQL stored procedures gives added value of the power and flexibility of database processing. This is also for the reason that it supports the usage of DML, Dynamic SQL and cursors.

The generic process that builds a PL/SQL Web application includes the following steps. First a Web page is visited by the user; a hypertext link is followed or data may be submitted in a form. This will in turn induce the browser to send a HTTP request for a URL (Uniform Resource Locator) to an HTTP server. The HTTP server then calls a stored procedure on an Oracle database depending on the encoded data in the URL. The data in the URL constitutes as parameters which is passed to the stored procedure. Subprograms in the PL/SQL Web Toolkit are called by the stored procedure. Some subprograms such as HTTP.Print generate Web pages in a dynamic manner. The subprograms then pass the page generated dynamically to the Web server which delivers it to the client.

A Web browser based application can be implemented completely in PL/SQL along with the Oracle Database components present in the PL/SQL Web Toolkit. While responding to a browser request, data is updated or retrieved data from the database by a PL/SQL procedure, based on the user input and it generates an HTTP response generally as a download file or HTML for being displayed. The Web Toolkit API (Application Programming Interface) enables the underlying stored procedures to carry out operations such as attain information pertaining to an HTTP request, Generate HTTP headers, Set browser cookies and produce HTML pages. The commonly used PL/SQL Web Toolkit packages are OW_CACHE, OWA_CUSTOM, OWA_COOKIE HTP, HTF etc.

To be useful is a wide range of circumstances a Web application must be interactive and allow user choices. The key methods of passing parameters to PL/SQL Web applications are either using HTML form tags or hard coding in the URL. While using HTML form tags, the user proceeds to fill a form constituting a web page and all the corresponding data are passed to a stored procedure on the click of a submit button by the user. In the Hard-coded in the URL method a set of predefined parameters are passed to a stored procedure with separate links in the Web page corresponding to the choices required by the user.

Oracle Database provides packages that also help to launch internet computing to PL/SQL programmers. With this it is possible to send e-mail from PL/SQL with the UTL_SMTP package. Using the UTL_INADDR the host name pertaining to the local machine or the IP address of an identified host name can be determined from a PL/SQL program. The UTL_TCP package is used to open TCP/IP connections for a set of machines on the network and to enable reading or writing to the corresponding sockets. The UTL_HTTP package helps retrieve the contents of a HTTP URL and lets the details of the HTTP session such as header lines, cookies, proxy servers be controlled by the programmer among other things. Similarly operations such as speeding up multiple accesses to the same website and construction and interpretations of URL for various uses can be performed.

Thus the operations of web applications such as visiting a web page, pressing a button, choices made by user in an HTML form can be used to invoke a stored procedure to drill down or carry out some other operation on displayed data. To produce dynamic content, using stored procedure allow users to have the same flexibility and interactive nature of CGI (Common Gateway Interface) programs devoid of the memory overhead of forking a CGI process.

FREE Subscription

Subscribe to our mailing list and receive new articles
through email. Keep yourself updated with latest
developments in the industry.

Note : We never rent, trade, or sell my email lists to
anyone. We assure that your privacy is respected
and protected.

_______________________________________