Creating powerful application with Oracle Database

Application creation with OracleFor an Oracle application to be built and used rapidly and effectively, users and developers must share a common language and a deep and common understanding of both the business application and the Oracle tools. This is a new approach to development. Historically, the systems analyst studied the business requirements and built an application to meet those needs. The user was involved only in describing the business and, perhaps, in reviewing the functionality of the application after it was completed.

With the new tools and approaches available, and especially with Oracle, applications can be built that more closely match the needs and work habits of the business - but only if a common understanding exists.

This book is aimed specifically at fostering this understanding, and at providing the means for both user and developer to exploit Oracle's full potential. The end user will know details about the business that the developer will not comprehend. The developer will understand internal functions and features of Oracle and the computer environment that will be too technically complex for the end user. But these areas of exclusive expertise will be minor compared with what both end users and developers can share in using Oracle. There is a remarkable opportunity here.

It is no secret that "business" people and "systems" people have been in conflict for decades. Reasons for this include differences in knowledge, culture, professional interests and goals, and the alienation that simple physical separation between groups can often produce. To be fair, this syndrome is not peculiar to data processing. The same thing occurs between people in accounting, personnel, or senior management, as members of each group gather apart from other groups on a separate floor or in a separate building or city. Relations between the individuals from one group and another become formalized, strained, and abnormal. Artificial barriers and procedures that stem from this isolationism become established, and these also contribute to the syndrome.

This is all very well, you say, and may be interesting to sociologists, but what does it have to do with Oracle?

Because Oracle isn't cloaked in arcane language that only systems professionals can comprehend, it fundamentally changes the nature of the relationship between business and systems people. Anybody can understand it. Anybody can use it. Information that previously was trapped in computer systems until someone in systems created a new report and released it now is accessible, instantly, to a business person, simply by typing an English query. This changes the rules of the game.

Where Oracle is used, it has radically improved the understanding between the two camps, has increased their knowledge of one another, and has even begun to normalize relations between them. This has also produced superior applications and end results.

Since its first release, Oracle has been based on the easily understood relational model (explained shortly), so nonprogrammers can readily understand what Oracle does and how it does it. This makes it approachable and unimposing.

Furthermore, Oracle was created to run identically on virtually any kind of computer. Thus, it doesn't matter which manufacturer sold you your equipment; Oracle works on it. These features all contributed directly to the profound success of the product and the company.

In a marketplace populated by computer companies with "proprietary" hardware, "proprietary" operating systems, "proprietary" databases, and "proprietary" applications, Oracle gives business users and systems departments new control over their lives and futures. They are no longer bound to the database product of a single hardware vendor. Oracle runs on nearly every kind of computer. This is a basic revolution in the workplace and in application development, with consequences that will extend far into the future.

Some individuals neither accept nor understand this yet, nor do they realize just how vital it is that the dated and artificial barriers between "users" and "systems" continue to fall. But the advent of cooperative development will profoundly affect applications and their usefulness.

However, many application developers have fallen into an easy trap with Oracle: carrying forward unhelpful methods from previous-generation system designs. There is a lot to unlearn.

Many of the techniques (and limitations) that were indispensable to a previous generation of systems are not only unnecessary in designing with Oracle; they are positively counterproductive.

In the process of explaining Oracle, the burden of these old habits and approaches must be lifted. Refreshing new possibilities are available.

Throughout my blog, the intent will be to explain Oracle in a way that is clear and simple, in terms that both users and developers can understand and share. Outdated or inappropriate design and management techniques will be exposed and replaced.


The Cooperative Approach

Oracle is an object-relational database. A relational database is an extremely simple way of thinking about and managing the data used in a business. It is nothing more than a collection of tables of data. We all encounter tables every day: weather reports, stock charts, sports scores. These are all tables, with column headings and rows of information simply presented. Even so, the relational approach can be sophisticated and powerful enough for even the most complex of businesses. An object-relational database supports all of the features of a relational database while also supporting object-oriented concepts and features.

Unfortunately, the very people who can benefit most from a relational database - the business users - usually understand it the least. Application developers, who must build systems that these users need to do their jobs, often find relational concepts difficult to explain in simple terms. A common language is needed to make this cooperative approach work.

The first article of my blog explain, in readily understandable terms, just what a relational database is and how to use it effectively in business. It may seem that this discussion is for the benefit of "users" only. An experienced relational application designer may be inclined to skip these early chapters and simply use the blog as a primary source Oracle reference. Resist that temptation! Although much of this material may seem like elementary review, it is an opportunity for an application designer to acquire a clear, consistent, and workable terminology with which to talk to users about their needs and how these needs might be quickly met. If you are an application designer, this discussion may also help you unlearn some unnecessary and probably unconscious design habits. Many of these habits will be uncovered in the course of introducing the relational approach. It is important to realize that even Oracle's power can be diminished considerably by design methods appropriate only to nonrelational development.

If you are an end user, understanding the basic ideas behind object-relational databases will help you express your needs cogently to application developers and comprehend how those needs can be met. An average person working in a business role can go from beginner to expert in short order. With Oracle, you'll have the power to get and use information, have hands-on control over reports and data, and possess a clear-eyed understanding of what the application does and how it does it. Oracle gives you, the user, the ability to control an application or query facility expertly and know whether you are getting all the available flexibility and power.

You also will be able to unburden programmers of their least favorite task: writing new reports. In large organizations, as much as 95 percent of all programming backlog is composed of new report requests. Because you can write your own reports, in minutes instead of months, you will be delighted to have the responsibility.


Everyone Has “Data”

A library keeps lists of members, books, and fines. The owner of a baseball card collection keeps track of players' names, dates, averages, and card values. In any business, certain pieces of information about customers, products, prices, financial status, and so on must be saved. These pieces of information are called data.

Information philosophers like to say that data is just data until it is organized in a meaningful way, at which point it becomes "information." If this is true, then Oracle is also a means of easily turning data into information. Oracle will sort through and manipulate data to reveal pieces of knowledge hidden there—such as totals, buying trends, or other relationships—which are as yet undiscovered. You will learn how to make these discoveries. The main point here is that you have data, and you do three basic things with it: acquire it, store it, and retrieve it.

Once you've achieved the basics, you can make computations with data, move it from one place to another, or modify it. This is called processing, and, fundamentally, it involves the same three steps that affect how information is organized.

You could do all of this with a cigar box, pencil, and paper, but as the volume of data increases, your tools tend to change. You may use a file cabinet, calculators, pencils, and paper. While at some point it makes sense to make the leap to computers, your tasks remain the same.

A relational database management system (often called an RDBMS for short) such as Oracle gives you a way of doing these tasks in an understandable and reasonably uncomplicated way. Oracle basically does three things:

  • Lets you put data into it
  • Keeps the data
  • Lets you get the data out and work with it

Figure 1-1 shows how simple this process is.

What Oracle dose with Data

Oracle supports this in-keep-out approach and provides clever tools that allow you considerable sophistication in how the data is captured, edited, modified, and put in; how you keep it securely; and how you get it out to manipulate and report on it.

An object-relational database management system (ORDBMS) extends the capabilities of the RDBMS to support object-oriented concepts. You can use Oracle as an RDBMS or take advantage of its object-oriented features.


The Familiar Language of Oracle

The information stored in Oracle is kept in tables—much like the weather table from a daily newspaper shown in Figure 1-2.

Wether table in newspaper

This table has four columns: City, Temperature, Humidity, and Condition. It also has a row for each city from Athens to Sydney. Last, it has a table name: WEATHER.

FIGURE I -3. A WEATHER table from Oracle information is stored row after row (city after city). Each unique set of data, such as the temperature, humidity, and condition for the city of Manchester, gets its own row.

Weather table in Oracle Database



Oracle avoids specialized, academic terminology in order to make the product more approachable. In research papers on relational theory, a column may be called an "attribute," a row may be called a "tuple" (rhymes with "couple"), and a table may be called an "entity." For an end user, however, these terms are confusing. More than anything, they are an unnecessary renaming of things for which there are already commonly understood names in our shared everyday language. Oracle takes advantage of this shared language, and developers can too. It is imperative to recognize the wall of mistrust and misunderstanding that the use of unnecessary technical jargon produces. Like Oracle, my blog will stick with "tables," "columns," and "rows."


Structured Query Language (SQL) for coding

Oracle was the first company to release a product that used the English-based Structured Query Language, or SQL. This language allows end users to extract information themselves, without using a systems group for every little report.

Oracle's query language has structure, just as English or any other language has structure. It has rules of grammar and syntax, but they are basically the normal rules of careful English speech and can be readily understood.

SQL, pronounced either "sequel" or "S.Q.L.," is an astonishingly capable tool, as you will see. Using it does not require any programming experience.

Here's an example of how you might use SQL. If someone asked you to select from the preceding WEATHER table the city where the humidity is 89, you would quickly respond "Athens." If you were asked to select cities where the temperature is 66, you would respond "Chicago and Manchester."

Oracle is able to answer these same questions, nearly as easily as you are, and in response to simple queries very much like the ones you were just asked. The keywords used in a query to Oracle are select, from, where, and order by. They are clues to Oracle to help it understand your request and respond with the correct answer.


A Simple Oracle Query

If Oracle had the example WEATHER table in its database, your first query (with a semicolon to tell Oracle to execute the command) would be simply this:

select City from WEATHER where Humidity = 89 ;

Oracle would respond:




Your second query would be this:

select City from WEATHER where Temperature = 66 ;

For this query, Oracle would respond:





As you can see, each of these queries uses the keywords select, from, and where. What about order by? Suppose you wanted to see all the cities listed in order by temperature. You'd simply type this:

select City, Temperature from WEATHER order by Temperature ;

and Oracle would instantly respond with this:

City              Temperature

---------         ---------------

SYDNEY              29

LIMA                45


CHICAGO           66

SPARTA              74

PARIS               81

ATHENS              97

Oracle has quickly reordered your table by temperature. (This table lists lowest temperatures first; in a later chapter, you'll learn how to specify whether you want low numbers or high numbers first.)

There are many other questions you can ask with Oracle's query facility, but these examples show how easy it is to obtain the information you need from an Oracle database in the form that will be most useful to you. You can build complicated requests from simple pieces of information, but the method used to do this will always be understandable. For instance, you can combine the where and order by keywords, both simple by themselves, to tell Oracle to select those cities where the temperature is greater than 80, and show them in order by increasing temperature.

You would type this:

select City, Temperature from WEATHER
where Temperature > 80
order by Temperature;

and Oracle would instantly respond with this:

City                   Temperature
---------- -------------
PARIS                     81
ATHENS                    97

Or, to be even more specific, request cities where the temperature is greater than 80 and the humidity is less than 70:

select City, Temperature, Humidity from WEATHER
where Temperature > 80
and Humidity < 70
order by Temperature ;

and Oracle would respond with this:

City              Temperature   Humidity
------ ---------- --------
PARIS                 81          62


Why It Is Called “Relational” Database

Notice that the WEATHER table lists cities from several countries, and some countries have more than one city listed. Suppose you need to know in which country a particular city is located. You could create a separate LOCATION table of cities and their countries, as shown in Figure 1 -4.

For any city in the WEATHER table, you can simply look at the LOCATION table, find the name in the City column, look over to the Country column in the same row, and see the country's name.

These are two completely separate and independent tables. Each contains its own information in columns and rows. They have one significant thing in common: the City column. For each city name in the WEATHER table, there is an identical city name in the LOCATION table.

For instance, what is the current temperature, humidity, and condition in an Australian city? Look at the two tables, figure it out, and then resume reading this.

How did you solve it? You found just one AUSTRALIA entry, under the Country column, in the LOCATION table. Next to it, in the City column of the same row, was the name of the city, SYDNEY. You took this name, SYDNEY, and then looked for it in the City column of the WEATHER table. When you found it, you moved across the row and found the Temperature, Humidity, and Condition: 29, 12, and SNOW.

Weather and location table in Oracle Database

Even though the tables are independent, you can easily see that they are related. The city name in one table is related to the city name in the other (see Figure 1-5). This relationship is the basis for the name relational database.

This is the basic idea of a relational database (sometimes called a relational model). Data is stored in tables. Tables have columns, rows, and names. Tables can be related to each other if each has a column with a common type of information.

That's it. It's as simple as it seems.

relationship between the WEATHER and LOCATION tables

Some Common, Everyday Examples programming for Oracle

Once you understand the basic idea of relational databases, you'll begin to see tables, rows, and columns everywhere. Not that you didn't see them before, but you probably didn't think about them in quite the same way. Many of the tables that you are accustomed to seeing could be stored in Oracle. They could be used to quickly answer questions that would take you quite some time to answer using nearly any other method.

A typical stock market report in the paper might look like the one in Figure 1-6. This is a small portion of a dense, alphabetical listing that fills several narrow columns on several pages in a newspaper. Which stock traded the most shares? Which had the biggest percentage change in its price, either positively or negatively? The answers to these questions can be obtained through simple English queries in Oracle, which can find the answers much faster than you could by searching the columns on the newspaper page.

Figure 1 -7 is an index to a newspaper. What's in section F? If you read the paper from front to back, in what order would you read the articles? The answers to these questions are obtainable via simple English queries in Oracle. You will learn how to do all of these queries, and even build the tables to store the information, in the course of using this reference.

stock market table

Throughout my blog, the examples use data and objects encountered frequently in business and everyday life. Similar data to use for your exercises should be as easy to find as your nearest bookshelf. You will learn how to enter and retrieve data in the pages ahead, using examples based on these everyday data sources.

Вас заинтересует / Intresting for you:

Stopping the Oracle Database 1...
Stopping the Oracle Database 1... 414 views Андрей Волков Sat, 29 Feb 2020, 10:19:28
How to connect to Oracle Datab...
How to connect to Oracle Datab... 669 views Андрей Волков Sat, 29 Feb 2020, 10:19:58
Oracle Database and Instance d...
Oracle Database and Instance d... 896 views Masha Thu, 21 Jun 2018, 18:23:39
Oracle Database 12C Backup: un...
Oracle Database 12C Backup: un... 660 views Андрей Волков Sat, 29 Feb 2020, 10:15:04