Ministry of Agriculture & Lands
PL/SQL GATEWAY APPLICATION STANDARDS
Version 1.0.1 October 24, 2007
Table of Contents
1. INTRODUCTION
1.1 Audience
1.2 Purpose
1.3 References
to Other Documents
2. DEFINITIONS
2.1 Standard
2.2 Guideline
3. APPLICATION
SYSTEM PROPERTIES
3.1 Application
Naming
3.2 Application
Delivery
4. DEVELOPMENT STANDARDS & GUIDELINES
4.1 Coding
Style
4.2 Application
Schema
4.3 PL/SQL
Web Toolkit
4.3.1 The
HTP and HTF Packages
4.3.2 The
OWA Packages
4.4 Look
and Feel
4.5 Security
4.6 Error
Handling
4.7 Documentation
5. CONCLUSION
VERSION
CONTROL
This section of the document is to be used to control the various versions or releases of the document.
| 1.0.0 | Production | Whole Document | 20-Mar-2002 | Jordan Armstrong | HeavyLifters Inc. |
| 1.0.1 | Production | Sections 3 and 4.4 | 24-Oct-2007 | Jordan Armstrong | IMB |
DOCUMENT
CONVENTIONS
The
following document conventions are used in this document:
| CAPS | are used when capital characters are to be entered |
| Bold | will indicate a proper name or system command to be performed |
| Bold Italics | indicates a role or function to be performed |
| <version> | represents a versioning format ##.##.## where the first ## represents a major version release, the second ## represents a minor version release, and the third ## represents a patch version release. |
This section outlines the Audience and Purpose of this standards document.
This document is directed at those who will be maintaining PL/SQL Gateway application systems on the Ministry of Environment Information Management Branch's corporate infrastructure. This includes external contractors, consultants, and business partners, as well as Ministry employees.
This document outlines the standards that must be followed when maintaining corporate applications for the Ministry of Agriculture and Lands and the Ministry of Environment. From this point forward any mention to either the Ministry of Agriculture and Lands or the Ministry of Environment will be referred to as "the Ministry". The Information Management Branch (IMB) serves both Ministries with the mandate to formulate and maintain application development standards.
It is expected that this document will be altered as required over time to accommodate the maintenance of PL/SQL Gateway applications.
1.3 References to Other Documentation
The
Ministry has a number of standards pertaining to application development and the system development
lifecycle (SDLC).
The following definitions apply throughout this document.
A standard is a specific statement of the rules and constraints governing the naming, contents, and operations of software. A standard must be followed.
A
guideline is a method or custom, which through common usage has
become an accepted method of work. A guideline is not enforced,
and is not a standard.
3. APPLICATION SYSTEM PROPERTIES
The Ministry is implementing PL/SQL Gateway applications via a 3-tier computing model using an Oracle 10g Application Server (OAS). In this model the application server is the middle-tier between the clients and the database. The database houses the application's stored procedures and the clients make requests via their web browser to the OAS server which then sends the request to the database. The process is described below:
- The OAS server receives a request from a client browser.
- The OAS server routes the request to the PL/SQL Gateway.
- The PL/SQL Gateway uses the configuration information stored in your Database Access Descriptor (DAD), connects to the database and forwards the request. The PL/SQL Gateway prepares the call parameters (if applicable), and invokes the application PL/SQL stored procedure in the database. The PL/SQL procedure generates an HTML page using data and the PL/SQL Web Toolkit accessed from the database. The response is returned to the PL/SQL Gateway.
- The OAS server sends the response to the client browser.
A
diagram of the three-tier application server deployment at the
Ministry is shown below in Figure 1.
Applications must be named as a 2-4 character short name or acronym that is unique within the business area or corporation. This Application Short Name must be prefixed to all 'physical' database objects such as tables, views, packages, sequences and roles (see Database Design Transformer). Functions and procedures that are not encapsulated in packages should also be prefixed with this name.
The intent of requiring the prefixing the Application Short Name on all objects is to reduce the possibility of namespace collisions. Approval to use a new application short name (acronym) must be obtained from the Corporate Database Administrator to ensure that there are no duplicate names.
All deliveries of PL/SQL Gateway Applications must follow the PL/SQL Gateway Delivery Standards and the Application Delivery Standards. The application must be delivered in a compressed tar file that includes:
- The scripts to create the application - users, tables, program units, etc.
- Any required html files.
- A readme file.
- Any other required documentation.
4. DEVELOPMENT STANDARDS & GUIDELINES
This section describes the Ministry's PL/SQL Gateway application development standards and guidelines. Application development is specified in terms of standards and guidelines as defined in section 2.
All PL/SQL Gateway applications must adhere to existing Ministry web standards and Designer standards.
Vendors should note that the Ministry IMB will review source code as part of the Ministry's Quality Assurance process. Source code must contain comments and be well structured; all non-functional ("dead") code must be removed from source modules prior to delivery. The comments at the header of the stored program unit must include:
- Name of the stored procedure
- A detailed purpose
- A modification history for any changes made
- Version #
- Date
- Author, Company Name
- Description
An example is shown below:
/****************************************************************************
NAME: usl_get_staff_region(Procedure)
PURPOSE:
To retrieve default region for a particular user for a specified
app.
REVISIONS:
Ver
Date Author
Description
----
--------- ------------------ ------------------------------------
1.0
31-MAY-01 J. Armstrong, 1.
Created this procedure.
HeavyLifters Inc.
1.1
14-JUN-01 J. Armstrong, 2.
Modified procedure to accommodate
HeavyLifters Inc. new table structure.
****************************************************************************/
New applications will own a schema that will contain all the necessary database objects required to run the application. The schema name must be the approved acronym for the application and all the objects must be prefixed with this acronym.
If the application is a supplement to an existing schema then the PL/SQL Gateway application schema name must be the acronym of the existing application with a suffix of "wb" or "web" to indicate it is the web component of the application. The new schema must be given the appropriate privileges to access the existing application schema objects. It is suggested that these privileges be granted via a role owned by the existing application schema.
The PL/SQL Web Toolkit is a set of packages you utilize to create PL/SQL Gateway applications. The packages define data types, functions, and procedures that are used by the mod_plsql plugin, and can also be used in the development of the application. The functions and procedures are used to generate dynamic HTML pages that contain data retrieved from the database.
4.3.1 The HTP and HTF Packages
The HTP (hypertext procedures) package contains the procedures that the OAS server uses to generate HTML tags and the text that surrounds the tags. The HTF (hypertext functions) package contains the function version of the procedures in the HTP package. These functions do not generate any output directly to the web; the functions are used to pass their return values to the statement(s) that invoked them.
There are several packages that the OAS server utilizes to generate PL/SQL Gateway applications. They are OWA, OWA_CACHE, OWA_COOKIE, OWA_CUSTOM, OWA_IMAGE, OWA_OPT_LOCK, OWA_PATTERN, OWA_SEC, OWA_TEXT, and OWA_UTIL. These packages can also be used by a developer to get information about the client request, construct HTML tags, return header information to the client, etc.
Standard headers and footer have been created to maintain a common look and feel throughout Ministry web applications. These stored procedures are stored in the PLS_STANDARDS package and are available for operational applications in the MELP schema in ENVDLVR1, ENVTEST1, ENVPROD1 and ENVTRN1; for warehouse applications they are available in the APP_UTILITY schema in IDWDLVR1, IDWTEST1 and IDWPROD1 . PUBLIC has been granted the EXECUTE privilege on the package and a public synonym with the same name has also been created. The headers and footer are listed below:
- pls_standards.pls_mal_header
- pls_standards.pls_moe_header
- pls_standards.pls_moe_footer
The Database Access Descriptor (DAD) for the application controls access to the PL/SQL Gateway application. The DAD contains the application schema username and password. If the application requires the database to authorize the user then the username and password can be omitted from the DAD. This forces the user to login to the database and access will be granted according to the roles the user possesses.
Steps should be taken to prevent application processing errors by observing the following guidelines:
- Provide default values for data elements when their availability and validity cannot be guaranteed.
- Ensure that input fields are trimmed of trailing space.
- Force user input to match case data is stored in.
- Use defaults to avoid NULL values unless NULL values are intended by design.
Several goals must be met by the documentation provided with an application, including an explanation of the configuration of the application software, instructions on how to use it, and any further recommendations collected during development.
A single general user guide must be provided for the application. Normally this will be the responsibility of the application custodian to produce in consultation with the developer; however, it may be included in the specific deliverables for the project.
Applications should also have on-line, context-sensitive help system. The content of this help system should be derived verbatim from the user guide on a section-by-section basis, where each section in the user guide corresponds to a logical unit of work in the application. This means that the user guide should be developed with the help feature in mind.
The
system documentation for the application should include technical
instructions for building and compiling the application in addition
to the procedures required to maintain the application on an ongoing
basis.
5. CONCLUSION
This document provides a set of standards and guidelines for the development of consistent and maintainable PL/SQL Gateway applications.
The goal of any development standard is the delivery of quality software solutions. In the case of the development of application systems, following these few simple rules can be the difference between the success and failure of a project.
As this technology matures, so will these development standards. For the most up-to-date set of development standards please check the Ministry's website.
