Work With Results Through Technology to Grow Your Business Contact Us Today

Categories: Technical Tips

By Connie S. Alexander


Recently I was given the opportunity to create a web application for a local humane society.  The requirements were small, but did include a fully functioning database to support internal daily business.  I chose to create my application in Designer/2000, then use the Web Generator to create my web application.

This paper is intended for the novice developer in both Designer/2000 and Web Application Server.  My goal is to outline the tools I used to create this web application and to show how easy it is to use Designer/2000 to jumpstart your web application.

Determine if your application is a good candidate

The World Wide Web has generated a lot of excitement in the computer industry.  The flexibility, ease of use, and minimal cost has enticed managers everywhere to consider a web application.  But, is your application a good candidate?

Good examples might include:

  • large amounts of informational data (i.e., manuals or catalogs)
  • small amount of simple forms (i.e., insert, update or view)
  • forms where valid values can be chosen from a list of values (i.e., addresses, purchase order)
  • users distributed among several sites or infrequent users
  • small amounts of simple graphics (complex graphics look nice, yet diminish response time)

However, the Internet can be rather slow.  The web isn’t suited for complex forms or heavy data entry.  Another consideration is security.  If you intend to display confidential data, data encryption is a must.

Web applications can save you time and money.  The cost of analysis and design will not change; however, you will reduce the amount of time it takes to develop your application using Designer/2000.  Striving for 100% generated code will save time and money now and when changes are introduced later.  Training users is not an issue as web applications are simple and intuitive.  Maintenance and support of your application will also be reduced because you won’t have the software on the client.

In web development, you should use a phased approach to delivering the application to the client.  Take a couple of weeks (not months) to develop a prototype.  Take the prototype to the users and determine if what you developed meets their needs.  Set user expectations early.  Describe the look and feel of your web application along with estimated access times will make user acceptance go smoother.  Once the prototype has been accepted, set it aside.  Next, develop the smallest, most important piece of your application through to completion.  Once that piece is completed, start on the next phase of the project.

Reverse engineering utilities

The process of taking something that you know works and working backwards to help you find out how it works is called reverse engineering.  There are several utilities to assist you in reverse engineering an existing Oracle database in Designer/2000.  The Reverse Engineering Database Objects utility takes the existing database tables, views, and sequences and creates Designer/2000 objects to be used by the diagrammers for module creation.  A logical schema of entities from tables can be created using the Table to Entity Retrofit utility.  You can also reverse engineer forms and reports from Developer/2000 by using the Reverse Engineer Form and Reverse Engineer Report utilities respectively.  Reverse engineering is an iterative process; thus it may be run as many times as needed.  The process can be used for single or multiple tables.

Entity Relationship Diagrammer

Designer/2000 comes with a suite of diagrammer tools.  The key to success is determining which diagrammer to use.  My database was new, so I couldn’t use reverse engineering.  For this project, I chose the Entity Relationship Diagrammer (ERD) to model my business requirements.

The tool bar contains all the necessary tools to display entities and relationships.  To create an entity, drag the entity icon on the tool bar to the canvas.  Double click on the entity and enter the entity attributes.  The Definition tab is where you define the short name, name, and the plural form of the name.  If any synonyms exist, use the Synonyms tab.  Unique identifier information is gathered under the UIDs tab.  Name, sequence, domain, and format are some of the attributes that are captured on the Attributes tab.

Once the entities are created, relationships need to be added.  Click on the appropriate relationship icon on the tool bar.  Then click on the entities.  Designer/2000 won’t let you save a relationship without filling in the From Name and To Name.  Don’t worry if you don’t get the correct cardinality the first time.  Just double click on the relationship and change the cardinality in the Edit Relationship dialog box.

Tip – The many relationship is commonly known as crow’s feet.  Remember dead crows fly east.  Meaning, if you have a many relationship coming from the north or west, you probably still have some work to do.

Modifications to data in the Entity Relationship Diagrammer are relatively easy.  There is an AutoLayout feature under the Utilities menu if your diagram needs some help untangling itself.  The ERD allows for visual scrutiny of your database.  Creating multiple ERD diagrams is a great way to break up complex diagrams to hand out to users for signoff.  After all relationships are correct, you can generate your database.

Database Wizard

The Database Wizard transforms entity models into database designs.  If you are still in the Entity Relationship Diagrammer, you can select the Database Wizard from the Utilities menu.  If you are at the main Designer/2000 screen, double click on the Database Wizard.

This utility transfers information from logical to physical.  If this thought is confusing, just think of copying entity and attributes to table and columns respectively.  Choose which entities you want to map in the Table Mappings tab.  The Run Options tab sets various flags to allow for creating, modifying, and when to commit the changes.  The Database Wizard can be run more than once to modify elements not created correctly the first time around.

Module Data Diagrammer

Now comes the fun stuff!  You have done the analysis and design and created the database.  Now you need to create the modules that will become the HTML needed for the Web Application Server.

One of the features that Designer/2000 offers is an About screen.  This screen is created in all diagrammers.  It contains the diagram name, title, type, create and last modified date, author, application system and username.  This information can be used as an About screen for your application.  By default, it is turned off.

The Module Data Diagrammer (MDD) is where you create screens and reports that will be used in your web application.  You can create master-detail links, look up links, and treewalk links just to name a few.

To create a detailed table usage, click on the first icon of the toolbar and drag it to the canvas.  Notice that a double-lined border is surrounding your detailed table usage.  The border signifies a screen.  If more than one table is needed to complete this page, you must drag the next detailed table usage within the double-lined border.  Use the relationship icon to draw relationships.

Double clicking the detailed usage table displays the Detailed Usages for the Web Application Server Module dialog box.  Within this dialog box, you control the table, column, and page details.  The Table Details tab contains Allow checkboxes to determine whether records can be added, updated, delete, or queried.  Alias is mandatory and must be unique as it will be used as the FROM clause in the SQL statements.  There are three kinds of Presentation Styles: list, frames, and form.  List displays a record list and view form on separate pages.  Frames display the record list and view form on the same page.  A Form contains buttons to move from one record detail to another.  Use the Form Presentation Style only if you have selected a query only form.

The Page Text tab is used to define text that will introduce your document or give instructions to the reader.  Special formatting, such as bold or italic, can be achieved via the use of HyperText Functions (HTF) packages that come with the Developers Toolkit.  During generation, page text is scanned for function calls.  When found, appropriate PL/SQL is generated to combine HTML returned from the function call, with the rest of the static text.  An asterisk (*) displayed next to the name denotes page text has been defined.

Example page text

Welcome to the htf.italic(‘Best Dog Humane Society’)web page.  To look at the animals we have up for adoption, press htf.bold(‘Animals’).

The Web page will look like

Welcome to the Best Dog Humane Society web page.  To look at the animals we have up for adoption, press Animals.

Other examples of HTF functions include htf.image, hft.mailto, and htf.anchor.

The next tab is Usage.  Select the CRUD matrix options desired for the columns in use.  Remember the options available depend on whether the table you are using is a base or lookup table.  Be sure and check Ctx if you are using a record list .  If this option is not checked, your records will not display.

The Column Details and Column Display tabs allow for more formatting of your page.  The Display Style dropdown list gives you the options of image, pop list, and radio group.  Radio group (meaning) displays associated meanings on screen.  Use the formatting column if you have a valid URL you want displayed as a hyperlink.

 Preferences Navigator

 Double Click on the Prefs icon from the main Designer/2000 screen to format your application.  Select WebServer Generator as the Product and Flavor when the Product Flavors dialog box is displayed.

 Tip – Select Description from the View Menu.  Oracle brings up the preferences using their name, i.e., MODAL C.  MODAL C spelled out stands for Active Hyperlink Color.

 A lot can be said or not said about changing preferences.  I’ll give you a couple of ideas.  It’s best just to play around and see what you like.

 To change the text color isn’t as easy as highlighting Text Color and putting in the word, Blue.  Colors are defined using hexadecimal values in the form “XXYYZZ”, where XX is the value from 00-FF(hex) for red, YY for green, and ZZ for blue.  The easiest way to find a color is to press ? (help) on the tool bar, then click on the Text Color field of COLORS.  This will bring up the Color Preferences help guide.  At the bottom of this help guide is a Quick Reference Color Chart.

 Specify the name of the image you want to use as a background on the Background Image File under GENERAL.

Tip – Set up a virtual directory for images in the Oracle Web Listener – Directory Mapping.  This allows for one central location to change the location of your images.

To have your logo show up as a background image, put <path><image name> (i.e., /images/logo.jpg) in the Background Image File (see Figure 2).  This preference may be set at the application or module level.  If set at the application level, the image will be common across all pages.

Headers and footers are another preference that can be set in the Preferences Navigator.  They can be set at the application or module level.  Standard Header/Footer On All Pages defaults to No, which means the header/footer will only appear on the Startup Page.  Standard Header/Footer defines the content and format of the header/footer.  Use the htf.img(‘/images/header.jpg’) command to display an image.

 Generating Your Web Application

 You have setup your module, defined your preferences, and now you are ready to generate your web application.  Verify the Web Application Server has been configured.  If the Web Application Server has not been configured, refer to Setting up Oracle Web Application Server latter in this paper.  To start the web generator, double click on WebServer from the Client/Server Generators Tab on the Designer/2000 Main screen.   The Generate WebServer dialog box is displayed.

 Select the Generate Server API tab first (see Figure 3).  Click on Show All Tables in the Application checkbox.  Select all tables by pressing the double down arrows icon.  Click Start Server API Generation.  You must generate the API before generating the module initially.  After initial generation, only regenerate the server API when changes are made to the underlying database tables.

 Next, select the Options tab.  Fill in the PL/SQL username, password, and connect string in the Install Generated PL/SQL box.  Also, fill in the Browser and Web Agent URL.  The URL contains the Oracle Web Application Server Database Access Descriptor (DAD) for the generated WebServer application.  The URL should look like:

 http://<machine:port#>/<WebServer DAD>/owa

 Setting up the Run Generated Application box allows 1) your browser to be invoked and 2) the name of the procedure generated for the Startup Page of the module appended to the Web Agent URL.  In short, this is the URL needed to view your web page.

On the Generate Tab, Highlight the Module you have created.  Once you get several modules created, you will want to check the Include the module network checkbox.  This will generate for the whole module network.  Putting a check in the Update Repository checkbox allows the Designer/2000 Repository to be automatically be updated if warning messages during generation suggest a change is needed to the module definition.

 Now you are ready to press Start.  This brings up the WebServer Generator.  The WebServer Generator will generate the module you requested.  Next, a SQL*PLUS dialog box is displayed showing the generation of the packages.  A Processing Complete dialog box is displayed when generation is finished.  Press Run from the WebServer Generator and with any luck, your web page is displayed.

 A few words on what the server generates.  The server API packages are created from three generated script files and written to the current working directory (usually c:/orant/bin).  These script files are:

  • master command file                     CDSAP13.SQL
  • package specification                    <table_alias or table_name>.PKS
  • package body                                <table_alias or table_name>.PKB

The master command file calls the package specification and package body scripts.  Here is an example of the master command file:

PROMPT Spooling to logfile ‘cvwsgin.log’

SPOOL cvwsgin.log







 The package specification and package body scripts contain the commands required to create the package in the database.

 Setting up Oracle Web Application Server Version 3.0 (Advanced Edition)

 I found setting up the Oracle Web Application Server was probably the most taxing phase of this project.  I have included this section in my paper to shed a little light on setting up the server.

 Setting up the Web Application Server consists of creating a Web Listener, creating a web user, creating a Database Access Descriptor, creating a PL/SQL Agent, and configuring the Web Request Broker.  These steps will be carried out on the Oracle Web Application Server web page usually installed at http://<machine:8888>.  The number, 8888, is the default admin port number.

 The Web Listener’s task is to listen for user requests.  A user request is a Uniform Resource Locator (URL).  The URL contains the Database Access Descriptor (DAD) for the Web Application Server user.  To create a Web Listener go to the Oracle Web Application Server.  Click on Web Application Server Manager.  Click on Oracle Web Listener.  Click on Create Listener.  You need to supply a name for the listener, port number, host name of machine, and document root.  If you are using UNIX, you need to supply a user ID and group ID.  The port number can be any port not currently in use by another listener.

 Next, create a Web Application Server User.  This user will have access to the database containing the web-generated application.  This user will also be used to install the Oracle Web Application Server Developers Toolkit, Web Application Server Generator Library packages, create the Oracle Web Application Server Database Access Descriptor (DAD) and update the Oracle Web Application Server Web Request Broker.  To create the Web Application Server User, log on to SQL*Plus as a DBA user and type the following:

 CREATE USER <username> IDENTIFIED BY <password>;


 The Oracle Web Application Server Developer Toolkit has four packages: HTF, HTP, OWA, and OWA_UTIL.  To install these packages, run the OWAINS.SQL script as application owner located in the $ORACLE_HOME/ows/admin directory.  Remember to ‘set scan off’ before you run the script.

 The Web Application Server Generator library contains three packages: WSGL, WSGJSL, and WSGLM.  To install these packages, run WSGL.SQL script as application owner, located in the orant\cgenw10\cvwetc directory.

 Now, add your new user to the Web Application Server configuration by creating a Database Access Descriptor (DAD).  To create the DAD, go to the Oracle Web Application Server.  Click on Web Application Server Manager.  Click on Oracle Web Application Server.  Click on DAD Administration.  Click on Create New DAD.  You will need to supply the DAD name, database user, ORACLE_HOME, and SQL*NET V2 Service (which is the host name).  If the DAD is accessing a remote database, supply the DBA Username and Password.  Click on Submit New DAD.

 Next, create a PL/SQL Agent.  To create the new agent, go to the Oracle Web Application Server.  Click on Web Application Server Manager.  Click on Oracle Web Application Server.  Click on Cartridge Administration.  Click on PL/SQL Cartridge.  Click on Create New PL/SQL Agent.  You will need to supply the name of the PL/SQL Agent, name of the DAD you just created, and Authorized Ports.  The Authorized Ports can be any port you want.  Separate multiple port numbers with a comma (,).  Many users use port 80.  Click on Submit New Agent.

 Last, but not least, add the virtual path to the PL/SQL Agent you just created.  To add the virtual path, go to the Oracle Web Application Server.  Click on Web Application Server Manager.  Click on Oracle Web Application Server.  Click on Cartridge Administration.  Click on PL/SQL Cartridge.  Click on Configure Web Request Broker parameter for PL/SQL.  Arrow down to the Virtual Paths section (see Figure 4).  Add a virtual path for plsql and owa for your application.  Click on Modify Cartridge to save your changes.

 Tip – When adding a name, use the name of the application.  This allows for consistency throughout the application and identity when other applications are introduced later.


 In conclusion, it is my hope I have been able to give you a taste of how easy it is to create a web application in Designer/2000.  By just using three tools, the Entity Relationship Diagrammer, Database Design Wizard, and the Module Data Diagrammer I was able to create the database and application.  Using the WebServer generator, I was able to web enable my application.


Spread the Word, like or share this page, your friends will also love it and thanks for it.

About the Author

Scott Nelson is an entrepreneur, Oracle wizard, consultant, businessman, filmmaker, inventor, tinkerer. He’s always been willing to approach the unapproachable, attempt the impossible, reach for the brass ring in a wild dive into the darkness of the unknown because his motto is “All you have to do is do it” Currently living the good life in southern Indiana amongst the hills and pleasant valleys of Brown County.