12 Part I: Basic PL/SQL Concepts
Table 1-5 A Sample Relational PURCH_ORDER_DTL Table
PO_Nbr Line_Nbr Item Qty Price
450 1 Hammer 1 $10.00
451 1 Screwdriver 1 $8.00
451 2 Pliers 2 $6.50
451 3 Wrench 1 $7.00
452 1 Wrench 3 $7.00
452 2 Hammer 1 $10.00
453 1 Pliers 1 $6.50
A purchase order can include many items. Table 1-5 shows that Purchase
Order 451 includes three separate items. The link (foreign key) between the
tables is the Purchase Order Number.
Understanding basic database terminology
A database consists of tables and columns, as we describe in the preceding
section. There are some other terms you need to know in order to under-
stand how databases work. A database is built in two stages. First you create
a logical data model to lay out the design of the database and how the data
will be organized. Then you implement the database according to the physical
data model, which sets up the actual tables and columns. Different terminol-
ogy applies to the elements of the logical and physical designs. In addition,
relational database designers use different words from object-oriented (OO)
database designers to describe the database elements. Table 1-6 shows the
words used in each of these cases.
Table 1-6 Database Design Terminology
Logical/Relational Logical/Object-Oriented Physical Implementation
Entity Class Table
Attribute Attribute Column
Instance Object Row
Chapter 1: PL/SQL and Your Database 13
The definitions of the words in Table 1-6 are as follows:
Entity: An entity corresponds to something in the real world that is of
interest and that you want to store information about. Examples of enti-
ties include things such as departments within an organization, employ-
ees, or sales. Each specific department or employee is considered an
instance of that entity. For example, in Table 1-3, Doug is an instance of
the entity Employee. (In the OO world, Doug would be an object in the
Employee class.)
Attribute: This word is used in both relational and OO databases to rep-
resent information about an entity instance or an object that will be
tracked. An example of an attribute might be the birth date or Social
Security number of an employee.
Entities (classes), their attributes, and instances (objects): These are
implemented in the database as tables, columns, and rows respectively.
One additional important concept to understand when dealing with relational
databases is the primary key. A primary key uniquely identifies a specific
instance of an entity. No two instances of an entity can have the same pri-
mary key. The values of all parts of the primary key must never be null. The
most common types of primary keys in relational databases are ID numbers.
For example, in Table 1-3, the EmpID can be the primary key. Sometimes more
than one attribute (or sets of attributes) can be used as a primary key. These
attributes are called candidate keys, one set of which must be designated as
the primary key.
Introducing database normalization
A database is considered normalized when it follows the rules of normaliza-
tion. Database normalization is useful for several reasons:
It helps to build a structure that is logical and easy to maintain.
Normalized databases are the industry standard. Other database profes-
sionals will find it easier to work with your database if it is normalized.
Retrieving data will be easier. This is actually the formal reason to nor-
malize. Graduate students in database theory courses often have to
prove a theorem that roughly states, “If your database is normalized,
you can be sure that any set of information you want to retrieve from
your database can be done by using SQL.”
You frequently need very complex procedural code to extract information
from a non-normalized database. The rules of normalization will help you to
design databases that are easy to build systems with.
14 Part I: Basic PL/SQL Concepts
Although a detailed discussion of normalization is beyond the scope of this
book, there are three basic rules of normalization that every database profes-
sional should have memorized. Not so coincidentally, we tell you about them
in the following three sections.
First Normal Form (1NF)
First Normal Form means that the database doesn’t contain any repeating
attributes. Using the Purchase Order example from Tables 1-4 and 1-5, the
same data could be structured as shown in Table 1-7.
Table 1-7 PURCH_ORDER Table (1NF Violation)
PO_NBR DATE ITEM 1 QTY1 PRICE1 ITEM2 QTY2 PRICE2
450 12-10-06 Hammer 1 $10.00
451 02-26-06 Screwdriver 1 $8.00 Pliers 2 $6.50
452 03-17-06 Wrench 3 $7.00 Hammer 2 $10.00
453 06-05-06 Pliers 1 $6.50
Although this table looks okay, what if a third item were associated with PO
451? Using the structure shown in Table 1-7, you can order only two items.
The only way to order more than two items is to add additional columns, but
then to find out how many times an item was ordered, you’d need to look in
all the item columns. Table 1-7 violates First Normal Form.
You can build a good database that doesn’t adhere to First Normal Form by
using more complex collections such as VARRAYs and nested tables (which
we discuss in Chapter 11).
Second Normal Form (2NF)
Violations of Second Normal Form occur when the table contains attributes
that depend on a portion of the primary key.
To talk about Second Normal Form, you should know what we mean by an
attribute being dependent on another attribute. Say attribute X is dependent
upon attribute Y. Then if you know the value of attribute X, you have enough
information to find the value of attribute Y. Logically, attribute Y can have
only one value. For example, from the information in Table 1-1, if you know
the Employee Number (EmpNo), you also know the employee’s name, which
department number he or she works in, and the number of that department.
In this case, the EmpNo is the primary key. However, knowing the department
number and department name doesn’t tell you a specific employee’s name or
number. You can’t use the department number/name combination as the pri-
mary key. You can’t even use the name (Ename) as the primary key because a
large organization might have more than one “John Smith” working there.
Chapter 1: PL/SQL and Your Database 15
Second Normal Form violations can exist only when you have a multi-column
primary key, such as the purchase order and the purchase order detail struc-
ture, as shown in Tables 1-8 and 1-9.
Table 1-8 PURCH_ORDER Table
PO_NBR DATE Vendor
450 12-10-06 ABC Co.
451 02-26-06 XYZ Inc.
452 03-17-06 XYZ Inc.
453 06-05-06 ABC Co.
Table 1-9 PURCH_ORDER_DETAIL Table (2NF Violation)
PO_NBR LINE DATE ITEM QTY PRICE
450 1 12-10-06 Hammer 1 $10.00
451 1 02-26-06 Screwdriver 1 $8.00
451 2 02-26-06 Pliers 2 $6.50
452 1 03-17-06 Wrench 3 $7.00
452 2 03-17-06 Hammer 2 $10.00
453 1 06-05-06 Pliers 1 $6.50
In this structure, the PURCH_ORDER_DETAIL table uses both PO_NBR and
LINE for the primary key. But DATE is dependent only on the PO_NBR (when
you know the PO_NBR, you know the date that each item was ordered), so
that column violates Second Normal Form.
Third Normal Form (3NF)
Third Normal Form violations occur when a transitive dependency exists. This
means that an attribute ID is dependent on another attribute that isn’t part
of either a primary or candidate key. These are serious violations indicating
errors in the database design that must be detected and corrected. Table 1-1
shows an example of Third Normal Form violation in a badly designed data-
base. The DeptName column is dependent only on the DeptNo column (that
is, if you know the department number, you know the name of the depart-
ment). The EmpNo is the obvious primary key, so the existence of DeptName
column violates Third Normal Form.
16 Part I: Basic PL/SQL Concepts
All attributes in entities (columns in tables) must be dependent upon the pri-
mary key or one of the candidate keys and not on other attributes.
For more information about normalization, look at books about database
theory such as Beginning Database Design, by Gavin Powell (Wiley) and A
First Course in Database Systems, by Jeffrey D. Ullman and Jennifer Widom
(Prentice Hall), or numerous works by Chris J. Date.
What is a DBMS?
After you’ve designed a relational database, you need to implement it. The
easiest way to do this is by using a product that’s specifically designed for
this purpose. Products that perform these operations are called Relational
Database Management Systems (usually abbreviated to RDBMS or just DBMS).
They allow you to easily create relational databases by defining and creating
tables and then populating them with data. In addition, you could be provided
with a special tool to modify and manipulate the data and write reports and
applications to interact with the data.
DBMSs also handle all sorts of other important functions. They allow many
people to access the database at the same time without interfering with one
another or corrupting the data. They also make it easy to create backups in
case of problems such as a power failure or other disasters.
A number of positions in Information Technology involve interaction with a
DBMS:
Database designer: This person analyzes the requirements for the system
and designs an appropriate database structure to house the data.
Database administrator (DBA): This person installs the DBMS, monitors
it, and physically manages its operations.
Database application developer: This person writes the code that
resides within the DBMS and directly interacts with the database.
User interface (UI) application developer: This person writes the code for
the user interface, which enables users to communicate with the database.
Many other people, including project managers, software testers, and docu-
mentation specialists, also work with database systems. This book focuses
on the skills required to be a database application developer.
The Scoop on SQL and PL/SQL
As a database application developer, you interact with the Oracle DBMS
by using the programming languages Structured Query Language (SQL,
Chapter 1: PL/SQL and Your Database 17
pronounced sequel) and Programming Language/Structured Query Language
(PL/SQL, pronounced either P-L-S-Q-L or P-L-sequel). In the following sections,
we introduce how SQL and PL/SQL work together and how they are different.
We also introduce what’s new in the current versions.
The purpose of SQL and PL/SQL
SQL is the industry standard language for manipulating DBMS objects. Using
SQL, you can create, modify, or delete database objects. This part of SQL is
called Data Definition Language (DDL). You can also use SQL to insert, update,
delete, or query data in these objects. This part of SQL is called Data
Manipulation Language (DML).
Oracle’s implementation of SQL isn’t exactly industry standard. Virtually
every DBMS (Oracle included) has invented items that are not part of the
standard specification. For example, Oracle includes sequences and support
for recursive queries that aren’t supported in other DBMS products.
Oracle is more than a database
The Oracle environment doesn’t consist solely The current main product is JDeveloper, a
of the DBMS. The Oracle environment itself is Java-based programming environment.
enormous and complex, and the large number
Oracle Application Server (OAS): Web-
of products that Oracle sells is a reflection of
based applications typically run on a dedi-
that. So how does the DBMS fit into the bigger
cated computer. Oracle’s version of this is
picture? Here’s a quick overview of the main
called OAS.
categories of Oracle products:
Oracle Applications: Oracle has created
Oracle DBMS: This database management
or acquired a number of enterprise-wide
system runs on a variety of computers and
applications that work with the Oracle
operating systems. As we write this book,
DBMS and help Accounting, Manufactur-
it’s often considered to be the largest,
ing, and Human Resources departments to
fastest, most powerful, and fully featured
perform their day-to-day functions more
database product on the market. The Oracle
efficiently.
DBMS is the industry standard for big com-
panies that need to store and manipulate Oracle Corporation also includes consulting
large volumes of data. Oracle also provides (Oracle Consulting) and education (Oracle
versions of the DBMS to support small and University) divisions to round out its offering of
medium-sized companies. products and services.
Application development software: Oracle
has many application development products.
18 Part I: Basic PL/SQL Concepts
Getting to know SQL in an Oracle environment allows you to work in almost
any DBMS environment, such as SQLServer or MySQL, but you’ll encounter
some differences in the DBMS environments. You should probably know SQL
before trying to use PL/SQL. This book assumes that you already know SQL. If
you haven’t mastered SQL, take a good long look at SQL For Dummies, 5th
Edition, by Allen G. Taylor (Wiley), before you dive into this book.
PL/SQL is unique to Oracle. It isn’t industry standard. No other product uses
it. Being able to use PL/SQL will help you work only within the Oracle data-
base environment, but if you’re familiar with any other programming lan-
guage, you’ll find that PL/SQL follows the same basic rules.
PL/SQL is similar to other non-object-oriented procedural programming lan-
guages, such as C or Pascal. Its intellectual roots go back to a programming
language called Ada.
What makes PL/SQL unique is its tight integration with SQL. It is easier and
more natural to embed SQL in PL/SQL than to do so in any other program-
ming language. This makes PL/SQL ideal for writing large, complex programs
that must interact with an Oracle database.
The difference between SQL and PL/SQL
SQL and PL/SQL are completely different languages. SQL is a limited language
that allows you to directly interact with the database. You can manipulate
objects (DDL) and data (DML) with SQL, but SQL doesn’t include all the things
that normal programming languages have, such as loops and IF...THEN
statements.
That is what PL/SQL is for. PL/SQL is a normal programming language that
includes all the features of most other programming languages. But it has one
thing that other programming languages don’t have, namely the easy ability
to integrate with SQL.
What’s new in Oracle SQL and PL/SQL?
Oracle SQL and PL/SQL are evolving languages that constitute the backbone
of applications written for the Oracle environment. Every version of the
Oracle database expands the features of these languages. The production
version of Oracle 10g Release 2 has recently been released. As with previous
versions, this release offers lots of new things, including the following:
PL/SQL will probably run faster in the 10g version than it did in previous
versions. You don’t have to do anything extra to benefit from that
improvement. Oracle has made PL/SQL code run faster without requir-
ing any additional work on the part of the programmer.
Chapter 1: PL/SQL and Your Database 19
In SQL, many new commands allow you to retrieve information more
easily than before. Information about these commands is beyond the
scope of this book, but make sure you have a good Oracle SQL book,
such as Oracle Database 10g: The Complete Reference, by Kevin Loney
(McGraw-Hill), as a source for all the commands.
Because every release brings new capabilities, keeping up with the new fea-
tures in Oracle is important. Many developers don’t keep up with new features
because “all the old features will still work,” but those developers miss out on
the great new features included in each version. If you do a search for “new
features in PL/SQL” or “new features in Oracle SQL” in Google or your favorite
search engine, you’ll always find many articles and resources to show you the
latest additions to these programming languages.
What Is PL/SQL Good For?
PL/SQL is the language to use when writing code that resides in the database.
In the following sections, we introduce different situations in which you’ll find
PL/SQL useful.
Using database triggers
A trigger is an event within the DBMS that can cause some code to execute
automatically. There are four types of database triggers:
Table-level triggers can initiate activity before or after an INSERT, UPDATE,
or DELETE event. These are most commonly used to track history informa-
tion and database changes, to keep redundant data synchronized, or to
enhance security by preventing certain operations from occurring. See
Chapter 3 for more information about table-level triggers.
View-level triggers are very useful. A view is a stored SQL statement
that developers can query as if it were a database table itself. By placing
INSTEAD OF triggers on a view, the INSERT, MODIFY, and DELETE com-
mands can be applied to the view regardless of its complexity, because
the INSTEAD OF trigger defines what can be done to the view. See
Chapter 3 for more information about view-level triggers.
Database-level triggers can be activated at startup and shutdown. For
example, when the database starts up you might want to test the avail-
ability of other databases or Web services. Before a database shutdown,
you might want to notify other databases and Web services that the
database is going offline.
Session-level triggers can be used to store specific information. For
example, when a user logs on or off, you might want to execute code
20 Part I: Basic PL/SQL Concepts
that contains the user’s preferences and loads them into memory for
rapid access. When the session closes, a trigger can save the prefer-
ences for future use.
Database and session-level triggers are usually handled by DBAs, and further
discussion of their use is beyond the scope of this book.
Scripting with speed
When writing code, the ability to type a portion of code and execute it without
first saving it to the database is useful. Oracle provides this capability, which
is supported by all PL/SQL IDEs. We discuss this capability in Chapter 2.
Keeping code server-side
The majority of PL/SQL code is stored as program units in the server. A typi-
cal application has many lines of code.
Some programmers, particularly Web-based developers working in the J2EE
or .NET environments, try to write most of their code in the application server
in Java (for J2EE developers) or VB.NET (for .NET developers). This isn’t good
practice. In a database application, much of the logic is devoted to retrieving
and updating information. If the code to accomplish this task resides in an
application server, it must send a request to the database over a network. Then
the database must process the request and send the information back across
the network for the application to process. Because networks and computers
are now very fast, you might think that this would take only fractions of a
second. Although this is the case for a single request, if a very complex applica-
tion requires millions or even hundreds of millions of interactions with the
database, multiplying the number of interactions by even fractions of a second
can lead to very poor performance.
Even relatively simple operations requiring only a few database requests can
be problematic if the application is being accessed by hundreds, thousands,
or tens of thousands of users simultaneously. It is much more difficult to
build a database-intensive application without using server-side coding than
it is to write all the code to run in an application server.
One of the arguments against writing server-side code is that the application
won’t be portable (can’t be moved from one platform to another). However,
most organizations using Oracle have been using it for a very long time (ten
or more years) and aren’t contemplating a switch to a different platform. Also,
Web development is currently in a state of rapid flux. Organizations frequently
change between .NET, J2EE, and other environments for their Web-based
application development.
Chapter 1: PL/SQL and Your Database 21
Both the .NET and J2EE environments are in flux, as well. In the J2EE environ-
ment, the industry standard for Web development a year or so ago was to
create JavaServer pages (JSPs). Currently, the industry standard is to work in
the JSP/Struts environment. In the next year or so, JavaServer Faces (JSFs)
will likely become the industry standard. Therefore, code written in the
middle-tier runs a high risk of needing to be rewritten in the future.
Server-side code runs faster, is easier to maintain and test, and is less suscep-
tible to change than code placed in the middle tier. Therefore, creating signifi-
cant portions of an application in the database is a better approach.
There are a number of places where you can write code that your applications
can use. We discuss each in turn:
Portions of applications: PL/SQL program units can return a set of values
(functions), or PL/SQL routines can perform database operations (proce-
dures). These functions and procedures may be called by other functions
and procedures or (in the case of functions) used in SQL statements.
PL/SQL routines may be as large and complex as you need them to be.
Some complex routines may contain thousands of lines of code. Entire
systems may contain millions of lines of code. Chapter 3 covers the cre-
ation of functions and procedures and how to place them into packages.
PL/SQL code embedded in views: Oracle allows you to embed code in
database views. The code might actually be located in one of two places
in the view. First, you can place correctly crafted functions returning a
value in the SELECT portion of a SQL statement to retrieve additional infor-
mation, which might or might not be part of the tables being queried. For
example, you can create a view of a Customer table with a function that
would return the amount currently owed, even if this amount involves a
complex calculation and is not stored in the Customer table.
You can also embed PL/SQL in INSTEAD OF triggers on a view. These
triggers allow you to perform INSERT, UPDATE, and DELETE operations
on complex views, with PL/SQL programmatically handling how these
operations should be handled. Chapter 6 tells you about embedding
code in views.
Batch routines: Batch routines run code that processes a large number of
records at the same time. Generating invoices for every customer in a
system or processing payroll checks for an entire organization are exam-
ples of batch routines. These routines are usually large, complex, and data-
base intensive. This type of routine should assuredly be written in PL/SQL.
Programming for Oracle Developer
Oracle Developer used to be the Oracle Corporation’s primary application
development tool. More recently, Oracle’s JDeveloper has been used for Java-
based applications. However, many organizations still use Oracle Developer
22 Part I: Basic PL/SQL Concepts
for internal application development — mostly development for systems that
handle things like payroll.
Oracle Developer consists of two main parts:
Oracle Forms: A user interface screen building tool
Oracle Reports: A reporting tool
Both of these tools use PL/SQL as their programming language. The advan-
tages to this are numerous, because the code used to create the applications
is the same as that used in the database itself. Because the J2EE and .NET
environments have emerged, developers must use one programming lan-
guage for applications and a separate language for server-side development.
Although Oracle made some efforts to make Java work within the Oracle
database as PL/SQL does, the efforts weren’t entirely successful.
If you’re involved in a new project, the probability of using Oracle Forms is
fairly low. Most new development isn’t being done in Forms. However, many
organizations are still using large Forms-based systems that require ongoing
modifications and enhancements.
For reporting, Oracle Reports is still the primary tool for working with Oracle
databases. It continues to be enhanced. Further discussion of Oracle Devel-
oper is beyond the scope of this book. For more information, see Oracle
Developer: Advanced Forms & Reports, by Peter Koletzke and Dr. Paul Dorsey
(McGraw-Hill).
Chapter 2
The PL/SQL Environment
In This Chapter
Installing the Oracle database
Using tools
Understanding users and connections
Looking at sample data
B efore you can start working with PL/SQL, you need a place to write and
run your programs as well as a database (preferably populated with
some sample data) that your programs can interact with. You might be lucky
enough to be working at a company where someone can help you set up and
install everything. Or, you might be completely on your own and need to do
everything yourself.
Installing an Oracle environment isn’t the same as installing a simple
Windows application. You can’t just put the CD in the drive and have it all
magically install for you. You need to set up the right environment. This
involves installing the Oracle database and the necessary tools to allow you
to write, edit, and debug the code. In this chapter, you discover what you
need to do to start writing and testing your PL/SQL code.
Depending upon your particular system configuration and where you’re
working, you might need some help from a database administrator (DBA) to
get everything set up correctly the first time.
Setting Up an Oracle Environment
When setting up your environment, you need several components (although
you can create a pared down version if you just want to get up to speed with
PL/SQL, which this chapter also explains how to do).
24 Part I: Basic PL/SQL Concepts
If you want to set up a typical PL/SQL environment, you need the following
components:
Database server: This is a computer (or set of computers) that runs the
Oracle database. Oracle runs in many popular computer environments.
The most commonly used with Oracle are UNIX, Linux, or some version
of Microsoft Windows.
PL/SQL usually runs on the database server. But Oracle also has a
number of products that can use PL/SQL (Forms Developer 10g, Reports
Developer 10g, and so on).
Database Management system (DBMS): This is the Oracle software
itself. This software is installed on the database server. You must have
this installed prior to creating a database.
Database: A database is primarily a set of tables and data that constitute
the persistent (permanent) information for your system. You will also
store many other things in the database such as code, indexes (to help
queries run faster), and so on. Most of the interesting things you can do
with a database (for example, Java classes, snapshots, and links to other
databases) are beyond the scope of this book.
When you install the database software, Oracle gives you the option of
installing a sample database. If you take advantage of this option, it can
help you get started.
Application server: This is the computer where your application code is
stored and executed. Now that most application development is done
for the Web, you need a place to store and execute these programs.
Those programs are usually executed on an application server. They can
also be run on any common operating system, but are usually run on
Microsoft Windows or UNIX.
Application server software: To make applications work on an applica-
tion server, you need special software to manage and run those programs.
Various products that are available can do this. The most commonly used
products are Orion and Apache. In an Oracle environment, you often see
Oracle’s own application server, called Internet Application Server (IAS),
which is an extension of the Orion server software.
Client computer and software: Batch routines are usually run directly
on the database server, but client programs are run from a PC. Web
applications are run on an Internet browser, and client/server programs
execute directly on the client PC. Web applications mainly run on the
application server and present the screen output to the client machine
only through the browser.
Network: The database server, application server and client computers
have to be able to talk to each other through some kind of network or
Chapter 2: The PL/SQL Environment 25
over the Internet. However, a discussion of how to network computers
together is beyond the scope of this book. For an introduction to net-
working, Networking For Dummies, 7th Edition, by Doug Lowe (Wiley
Publishing, Inc.) can help.
If you’re setting up an environment just to figure out how PL/SQL works, you at
least need access to a database server and a client PC. If you’re trying to learn
from home, you can put everything on the same computer. (This assumes that
you have a fairly powerful PC; see the hardware and software requirements in
the next section for details.) If you run everything from a PC, you can’t build a
really large database but you can build a big enough database to try every
technique that this book demonstrates.
Hardware and Software Requirements
If you’re connecting to an existing Oracle database on a dedicated server, you
don’t need a very powerful machine. However, if you want to run the entire
Oracle environment on the same PC, you need a PC with sufficient resources to
run smoothly. You can use Oracle’s listed minimum requirements as a guideline
for the required hardware and software. However, you’ll have an easier time if
you have the following hardware and/or software resources available:
CPU: Intel Pentium 4 or AMD Athlon 1.5 GHz and up. Of course, faster
processors are always better, but any reasonable CPU made in the last
few years should be sufficient.
Memory: 1GB or more. Memory is the most critical resource.
Hard disk drive: 2.5GB or more free. The database by itself might use
about 1.5GB, but it is also nice to have the entire documentation library
on your local machine, which takes up about 1GB unzipped.
Operating system:
• Microsoft-based: MS Windows 2000 or MS Windows XP. Windows NT
is a bit too old, and Windows 95/98/ME weren’t designed to run
such complex applications.
• UNIX-based: If your workstation is UNIX-based, you’re probably
pretty technical already. We do suggest that you check the OTN (at
www.oracle.com/technology/support/metalink/index.
html) for any issues you will need to consider. (For details about
OTN, see the following section.)
Security software: Oracle uses multiple TCP/IP ports, so be careful
when configuring firewalls. If the database is trying to access something
26 Part I: Basic PL/SQL Concepts
via TCP/IP, don’t block it. This issue arose recently when Microsoft pro-
vided a built-in firewall for Windows XP Service Pack 2, and many users
had configuration problems. Therefore, expect some firewall messages
to pop up after the installation.
Accessing the Oracle
Technology Network
Oracle Corporation supports a forum to publicize technical information
called the Oracle Technology Network (OTN). The OTN contains a wealth of
information for Oracle professionals. Because PL/SQL is the language of
Oracle databases, the OTN includes many valuable resources to help you dis-
cover how to use PL/SQL effectively.
The OTN Web site gives you access to learning materials, discussion forums,
documentation libraries, and best of all, a place from which to download the
software. Because of the large size of some of the files, a reasonably fast con-
nection speed is probably a must.
Although you need an environment in which to practice using PL/SQL, your
boss might not like you playing with a production database. And if you think
that an Oracle license is too expensive to buy for home use, you’re probably
right. Luckily, you can set up everything you need at home. After registering
on OTN, you can download all the necessary software for free.
To register, go to the OTN Web site (www.oracle.com/technology/index.
html) and click the New OTN Visitors link. Then click the Join OTN link, and
on the next page, click the line to create an account. Registration is free, very
simple, and should take you only a minute or two.
After you register, you’re all set to download whatever you need. The first
thing you’ll see on the download page is the following disclaimer:
“All software downloads are free, and each comes with a development
license that allows you to use full versions of the products only while
developing and prototyping your applications. You can buy Oracle prod-
ucts with full-use licenses at any time from the online Oracle Store or
from your Oracle sales representative.”
So, you’re allowed to download and use any of the Oracle software free of
charge as long as you’re just getting to know Oracle.
Chapter 2: The PL/SQL Environment 27
Installing the Database
Many versions of the Oracle database are in use all over the world, and com-
bined with the various operating systems you might be using, it’s beyond the
scope of this book to explain the entire installation process in detail. However,
we do offer some general information in this section to get you started.
As of this writing, the most current version is called Oracle Database 10g. For
getting to know PL/SQL, you should install the latest version of 10g available
for your environment. This will allow you to practice with all the available
new features. With every release, Oracle improves the PL/SQL language by
adding new features and improving performance.
Which version should you use? The Enterprise Edition is preferable because
it includes some features that you might be interested in later during your
work with PL/SQL.
After you’ve downloaded the appropriate version of the database, you need
to install it. Here’s some helpful information that you should be aware of
during the database installation process:
Be sure to record the service name of the database when you install it.
A preconfigured database with example schemas is very helpful. (Schemas
are sets of objects belonging to the particular database user. If an object
belongs to the user SCOTT, it is part of schema SCOTT.) Anyone with at
least some Oracle experience recognizes these schemas, because all the
Oracle tutorials and manuals are based on them.
Don’t forget to unlock and set passwords for the most common schemas
(SCOTT, HR, OE). The SCOTT schema is used often in examples in this
book.
The OTN contains a lot of useful information, including the whole
Oracle documentation library (www.oracle.com/technology/
documentation/index.html). Check out the Quick Installation Guide
(preferable for beginners) or the Installation Guide for installation details
specific to your operating system. Both guides include steps that are
fairly easy to follow.
If you’ve had some experience in installing other large products, you should
be able to successfully install the database. Of course, as with any complex
software, you might have some problems, but many Internet resources are
available to help you if necessary.
28 Part I: Basic PL/SQL Concepts
Working with Code
After you have the database installed, the next question is, “Where will you
run all these PL/SQL programs?” The answer isn’t as simple as you might
think. The ultimate goal is productivity, and you can create PL/SQL programs
from numerous tools.
SQL*Plus
If you’ve already been working with Oracle, you’ve probably heard of
SQL*Plus. This tool is installed with all versions of Oracle (both server and
client). The main purpose of SQL*Plus is to provide quick and easy access to
the database in both interactive and batch modes. Compared to the leading
GUI development tools, SQL*Plus is a relatively primitive tool that most pro-
fessional developers rarely use.
You can run SQL scripts, PL/SQL scripts, and other scripts by using the inter-
nal command language of SQL*Plus by itself. Many developers do all their
work by using SQL*Plus because they don’t trust the fancy GUI tools.
Several versions of SQL*Plus are available after completing the installation of
the Oracle database:
The command line interface shown in Figure 2-1 is the most widely used
tool because it can be called from any operating system batch language.
The basic SQL*Plus GUI tool consists of a wrapper around the same
command line interface, as shown in Figure 2-2. It has some GUI features
such as the ability to set options in the special form, but it can hardly be
called very user friendly.
Figure 2-1:
The
command
line
interface.
Chapter 2: The PL/SQL Environment 29
Figure 2-2:
The basic
PL/SQL
GUI tool.
iSQL*Plus is the Web interface shown in Figure 2-3. It is the latest one
and has some nice features, but it also might require some workarounds
to deal with timeout settings and security patches.
Because SQL*Plus is included with all Oracle configurations, it remains
extremely popular despite its relatively minimal capabilities. For more details
about SQL*Plus, you can find a lot of documentation on OTN as well as many
custom scripts. It isn’t necessary to know a lot about SQL*Plus, but under-
standing some basics about how to run/save/edit scripts could be useful in
case you’re limited to SQL*Plus.
Figure 2-3:
The Oracle
i SQL*Plus
Web
interface.
30 Part I: Basic PL/SQL Concepts
Oracle SQL Developer
In the first part of 2006, Oracle added a new tool to the mix — Oracle SQL
Developer (formerly known as Project Raptor). It’s a free Java-based graphi-
cal environment targeted at database developers. With SQL Developer, you
can browse database objects, run SQL statements and SQL scripts, and edit
and debug PL/SQL statements. The Oracle SQL Developer interface is shown
in Figure 2-4.
Figure 2-4:
The Oracle
SQL
Developer
interface.
Currently, this product it still in the “Early Adopter” phase, although it is a
good alternative to the command line interface. The authors do not recom-
mend using it in production systems without significant testing, but SQL
Developer looks very promising. If you don’t have access to a more mature
third-party tool, we recommend trying it (see http://www.oracle.com/
technology/products/database/sql_developer/index.html to
download it).
Third-party tools
In addition to SQL*Plus and newly born Oracle SQL Developer, a number of
other tools enable you to work with PL/SQL. The reason for using any one
of these is to make your life easier. Of course, any GUI might have bugs/
restrictions/issues, but overall the pros usually outweigh the cons. Here are
a few popular options:
Chapter 2: The PL/SQL Environment 31
Toad from Quest Software: Toad is the most popular PL/SQL coding
tool on the market. It includes a nice editing environment for SQL and
PL/SQL. However, the most distinctive feature of Toad is a very powerful
set of administrative tools. Toad provides a full working environment for
DBAs and power users.
SQL Navigator from Quest Software: SQL Navigator, another product
from Quest Software, has a more limited audience. It is built by Oracle
developers for Oracle developers. Everything there is optimized for writ-
ing PL/SQL or SQL as quickly and effectively as possible. It isn’t as useful
for DBAs, but its add-ons and overall functionality make it a very attrac-
tive option for server-side developers.
RapidSQL from Embarcadero: RapidSQL, another major development-
centered tool, is focused on working with the whole IT environment.
Because Embarcadero targets more enterprise-wide solutions, RapidSQL
has many features that might just get in the way and be confusing for the
PL/SQL beginner. However, RapidSQL does include everything you need
to maximize your productivity.
For more information, you can read a set of reviews of these tools at www.
orafaq.com/tools/index.htm. The site might not always discuss the
most up-to-date versions, but it does include enough details to help you find
the right tool for your needs.
Establishing a Database Connection
After you install the database, you need to perform a few more steps before
you can connect to a database. In the sections that follow, we outline each
step. Connecting to the database after installation is also the simplest way to
verify that you have installed everything correctly.
Before you connect to the database, it’s important to understand how Oracle
handles user accounts. In Oracle, the concept of a user (and a set of objects
that belong to user is called a schema, as you already know) is one of the
major elements of the system. Here are the important points you need to
understand about accounts and how they relate to connecting to a database:
The only way of connecting to the database is by providing a username
and password. So you need to know the user/password account to which
you will connect. If you installed the database yourself, you will have a
user account called SYSTEM with whatever password you specified
during installation. Otherwise ask for help from your organization’s DBA.
Oracle includes sample schemas (SCOTT, HR, and OE), and you should
have unlocked them and set their passwords during the installation. If
you didn’t do it, a bit farther on you find commands for doing it manually.
Copyright by webtailieu.net