This article includes a list of general references, but it lacks sufficient corresponding inline citations. Please help to improve this article by introducing more precise citations. (August 2017) (Learn how and when to remove this message)

Oracle Designer

Oracle Designer 2000
Original author(s)	Oracle
Final release	10.1.2.6 / 2010
Type	CASE
Website	Oracle Designer

Oracle Designer was Oracle's CASE tool for designing an information system and generating it. After generating the information system one is able to edit the generated code with Oracle Developer Suite.

As of April 2018 this product has reached its end of life and is now in sustaining support only. Alternative modeling and design tools are Oracle JDeveloper and Oracle SQL Developer Data Modeler.^[1]

The product's original name was Oracle CASE and it was developed in England. Oracle CASE was based on Oracle corporations "Computer Aided Software Engineering" method (CASE Method). CASE Method was in turn developed by Oracle Consulting UK in the 1980s based on modelling techniques such as Richard Barker et al's Entity Relationship Modelling. Eventually the product would be known as Oracle Designer, with a complementary product Oracle Developer (although in practice the combination of Oracle Designer/Developer was most commonly used). Oracle became the dominant database and enterprise application vendor in the 1990s and as a consequence Oracle Designer/Developer was used by many enterprises from the mid 1990s to the mid 2000s. A product called SQL Data Dictionary (SDD) was a precursor to Oracle CASE.

In the 1980s relational database systems, running on unix based servers, became popular for administrative systems used by corporations and governments. Major factors were low maintenance cost and high developer productivity compared to earlier technologies. As increasingly large systems were developed, software development teams struggled to manage requirements and maintain code quality. Oracle CASE was initially used by Oracle Consulting UK's quality management team and later became the de facto standard for Oracle Custom Development (Custom development as opposed to packaged application software). Oracle CASE Method later became known as Oracle Custom Development Method, with a similar approach for customisations of Oracle's Application Suite called Oracle Application Development Method.

Oracle sold their Designer and Developer product's to enterprises and consulting groups, who in turn created thousands of systems that are still in place as of 2021. The design philosophy behind Oracle Designer and competing tools in the 1980s and 1990s was the Three-schema-architecture that separated an external schema, logical schema and internal schema. For Oracle's product line, the internal schema corresponded to the inner workings of their relational database, the logical schema corresponded to SQL and the external schema corresponded to screens and reports.

Oracle Designer was based on a well thought out set of concepts that suited the types of systems being developed from the 1980s to the mid 2000s. It's easiest to describe these concepts separately in terms of skills, structure and technology:

In terms of skills, software designers were expected to think out database structures in entity relationship models and functional decomposition models, then transform those models into database definitions and modules (the screens and reports). Software developers were then expected to elaborate the database definitions and modules to create working code. Finally the day to day running of the system was expected to be carried out by database administrators, who had detailed knowledge of the database internals.

Oracle Designer/Developer divided software development into data and applications, which were viewed at three levels of abstraction; Modelling, Design and implementation. This gives a 2x3 matrix of views which was visible throughout the product's lifecycle:

1. Entity Relationship Model. This is a high level abstraction of the database structure. Used primarily to generate the database design.
2. Database design. This is a representation of the tables, views, constraints of the database, with additional annotations. To illustrate the difference with the above; where an entity relationship model would show a relationship between two entities, the Database design would include additional columns for a foreign key, the foreign key constraint and an index over the foreign key columns. All of these could be generated from the entity relationship model, ensuring consistent naming and traceability. The names of tables and columns in many Oracle production databases in use today are due to the use of Oracle Designer. Later versions of the tool allowed specification of most of the internals of the Oracle database such as tablespaces and files.
3. Database Definition Language (DDL) generation from the Database design.
4. Function model. This is a function decomposition model, where each function contains a description and a CRUD matrix against the Entity Relationship model.
5. Modules. This modelled the screens, reports and other application components. Mostly used for screens, because of the availability of code generation for Oracle (Developer) Forms. It was common for
6. Application code. This consisted mostly of Oracle Forms, Oracle Reports, Stored procedures for the Oracle Database. Initially code was not stored in the Oracle Designer Repository, but in later versions developers were encouraged to add code to the repository, which was merged during code-generation. The client-server architecture of Oracle's Developer product was typical of the 1990s; PC computers running Oracle Forms and Reports that communicated with an Oracle Database over a network protocol called SQL*NET.

This structure was simpler than the software development processes that came before and was a better fit to the available technology. It was also simpler and led to a higher level of code generation than competing methodologies of the time such as IBM's Rational Unified Process.

Oracle Designer was initially based around a database that held design models, called a repository, not to be confused with a modern GIT repository ( A dictionary definition of a repository is a safe central place where things are stored). Later the Oracle Designer Repository included models and code, but always stored in an Oracle Database.

The tools that made up Oracle Designer each had their view on the repository, with which to create and edit models, generate more detailed models, generate code or inspect the quality of a model. For example specification designers were expected to indicate which data elements a function would use, so that the person designing the database structure could verify there were no unused data elements. Another example is the generation of a database definition from an entity relationship model, which eventually would be used to generate table creation scripts. Early users of Oracle Designer tended to focus on modelling and generating the database structures and often neglected the function model and modules.

Initially the Oracle Designer user interface was developed using Oracle Forms and Oracle Reports. This was a character mode user interface that was typically used in terminal sessions or MS-Dos, with a GUI diagram editor that ran on Unix X-Windows terminals only. When graphical user interfaces became easily available on the Windows 3.1 and Windows 95 operating systems in the mid 1990s, a stopgap version was released in Forms 4.0 but quickly shelved and redeveloped in C++ as a Windows only program with sophisticated diagramming tools.

By the time Oracle Designer became obsolete it encompassed code generation of Oracle Forms, Oracle Reports, Database triggers, Stored Database Procedures. It would be commonplace for large portions of a systems code to be generated in this fashion, with developers working around the code generators to add custom code at predetermined lifecycle events.

Three trends made the Oracle Developer tools obsolete and Oracle Designer with it.

Oracle Designer/Developer was aimed at development of administrative systems that were mainly used internally by enterprises. Many applications appearing in the 2000s required customers to perform some form of self-service data entry. The architecture of Oracle Developer was not well suited to the needs and technologies of the internet because it would have required users on the internet to install some kind of application and then directly connect to a database. Although later versions of Oracle Developer included an application server, it required a java based plug-in to be installed in the users-browser which placed high demands on end-users browsers. This posed a challenge for organisations with a fleet of older computers and was impractical for customer-facing applications. Eventually enterprises moved to other development tools which supported HTTP/HTML form based transactions, removing the need for the associated Oracle Designer.

After introducing systems for internal business processes in the 70's to the 90's, enterprises started to place more emphasis on integration between systems. Internet technologies such as HTTP, SOAP and Web-services became industry standards for data-exchange, but Oracle Developer's architecture made it hard to activate part of an application from an external source.

From 2000 onwards, graphical user interfaces and usability became a major factor in adopting newer development stacks. Oracle Developer was intended for, and very good at administrative applications that are used for data entry by enterprise employees. New users had to be trained how to use certain key-combinations in order to use the applications. For example each screen had a query and insert mode which allowed users to find and manipulate thinly veiled database records. Screens tended to resemble a collection of spreadsheet-like tables with a menu structure. Expectations of system user-friendliness increased in the 2000s and eventually outweighed the development productivity advantages of generating these types of applications.

Business Process Modelling

• Process Modeller

Systems Analysis Modelling

• ER Diagrammer
• Dataflow
• Function Hierarchy

Design Wizards

• Database Wizard
• Application Wizard

Systems Design

• Data Schema
• Module Logic
• Module Data
• Preferences
• Module Structure

Client/Server Generators

• Server - Oracle database objects (tables, indexes, constraints etc)
• Oracle Forms
• Oracle Reports
• Visual Basic
• Web server
• Microsoft Help
• C++ Object Layer

Utilities

• Repository Object Navigator (RON)
• Matrix
• Repository Reports
• Admin Utility
• SQL*Plus
• Online help

Oracle CASE 1

Oracle CASE 2

Oracle CASE 3

Oracle CASE 4

Oracle CASE 5 - developed using SQL*Forms 3 character mode screens

Oracle CASE 5.1 was a major redevelopment where the screens were redeveloped using the Oracle Forms 4.0 which provided a GUI interface

The version numbers get confusing at this point because the numbers go backwards. The software was renamed and the next version released was Oracle Designer/2000 6.0 (not to be confused with Designer 6 that was released years later).

The next minor release changed the numbering system to be in line with Oracle Developer, so it was named Designer 1.1

Designer 1 which supported generators for Forms 4.5

Designer 2 which supported generators for Forms 4.5 and 5

After this point the version numbers were changed to be in line with Oracle Developer

Designer 6 which supported generators for Forms 4.5, 5 and 6.

Designer 6i - the pre-release version number was 6.5. The production release was changed to 6i to keep in sync with the Oracle Developer version name

Designer 9i

Designer 10gR2 (10.1.2.6) – this was the last release of Designer^[2]

• Billings, Chris (1997). Rapid Application Development with Oracle Designer/2000. Harlow: Addison Wesley Pub. Co. ISBN 0-201-63444-9.
• Dorsey, Paul (1997). Oracle Designer/2000 Handbook. Berkeley: Osborne. ISBN 0-07-882229-7.
• Anderson, Carrie (1997). The Oracle Designer/2000 Handbook. Boston: Addison-Wesley. ISBN 0-201-63445-7.
• Dorsey, Dr (1998). Oracle Designer Handbook. New York: McGraw-Hill Companies. ISBN 0-07-882417-6.
• Lulushi, Albert (1998). Inside Oracle Designer/2000. Upper Saddle River: Prentice Hall PTR. ISBN 0-13-849753-2.
• Atkins, Kenneth (1999). Oracle Designer Generation. New York: McGraw-Hill Companies. ISBN 0-07-882475-3.
• Kramm, Mark (2000). Oracle Designer. Upper Saddle River: Prentice Hall PTR. ISBN 0-13-015343-5.

• Tutorial for Designer/2000
• Oracle Designer Tutorial: Creating an Oracle Database, Elizabeth Gallas, Fermi National Accelerator Laboratory, November 2, 2000.
  PDF Version: Oracle Designer Tutorial: Creating an Oracle Database
• Fast Track Design and Development with Oracle Designer 6i