PL/SQL: What, when, and where

What the Procedural Language/Structured Query Language (PL/SQL) really is, what it is good for, and how it fits into the world.

This is Chapter 1 from the O'Reilly book "Learning Oracle PL/SQL" by Bill Pribyl & Steven Feuerstein. You can purchase the book here.

In this chapter:
What Is PL/SQL?
Why Use PL/SQL?
What You Need to Get Started with PL/SQL

Let's start at the beginning and take a look at what Procedural Language/Structured Query Language (PL/SQL) really is, what it is good for, and how it fits into the world.

What Is PL/SQL?

Pick up most any reference book about PL/SQL and you'll read that it is Oracle's "procedural extension to Structured Query Language (SQL)." If that definition doesn't help much, consider what it assumes you know:

  • What a computer "language" is
  • What "procedural" means in this context
  • Some concept of Structured Query Language, including the notion that SQL is not procedural
  • The idea of a language "extension"

Let's look at each concept in turn.

A computer language is a particular way of giving instructions to (that is, programming) a computer. Computer languages tend to have a small vocabulary compared to regular human language. In addition, the way you can use the language vocabulary--that is, the grammar--is much less flexible than human language. These limitations occur because computers take everything literally; they have no way of reading between the lines and assuming what you intended.

Procedural refers to a series of ordered steps that the computer should follow to produce a result. This type of language also includes data structures that hold information that can be used multiple times. The individual statements could be expressed as a flow chart (although flow charts are out of fashion these days). Programs written in such a language use its sequential, conditional, and iterative constructs to express algorithms. So this part of the PL/SQL's definition is just saying that it is in the same family of languages as BASIC, COBOL, FORTRAN, Pascal, and C. For a description of how procedural languages contrast with three other common language categories, see the following sidebar.

Language Categories

Saying that PL/SQL is a procedural language makes more sense when you understand some other types of programming languages. There are at least four ways to categorize popular languages. [A].

Procedural programming languages
Allow the programmer to define an ordered series of steps to follow in order to produce a result. Examples: PL/SQL, C, Visual Basic, Perl, Ada.

Object-oriented programming languages
Based on the concept of an object, which is a data structure encapsulated with a set of routines, called methods that operate on the data. Examples: Java, C++, JavaScript, and sometimes Perl and Ada 95.

Declarative programming languages
Allow the programmer to describe relationships between variables in terms of functions or rules; the language executor (interpreter or compiler) applies some fixed algorithm to these relations to produce a result. Examples: Logo, LISP, Prolog.

Markup languages
Define how to add information into a document that indicates its logical components or that provides layout instructions. Examples: HTML, XML.


A. These category definitions are derived from an indispensable resource edited by Denis Howe called The Free On-line Dictionary of Computing, copyright 1993 by Denis Howe.

Structured Query Language is a language based on set theory, so it is all about manipulating sets of data. SQL consists of a relatively small number of main commands such as SELECT, INSERT, CREATE, and GRANT; in fact, each statement accomplishes what might take hundreds of lines of procedural code to accomplish. That's one reason SQL-based databases are so widely used. The big joke about the name "SQL" is that it is not really structured, is not just for queries, and (some argue) is not even a real language. Nevertheless, it's the closest thing there is to a lingua franca for relational databases such as Oracle's database server, IBM's DB2, and Microsoft's SQL Server.

A language extension is a set of features that somehow enhance an existing language. This phrase might imply, incorrectly, that PL/SQL is a special version of SQL. That isn't the case, however. PL/SQL is a programming language in its own right; it has its own syntax, its own rules, and its own compiler. You can write PL/SQL programs with or without any SQL statements. Some authors assert that PL/SQL is a superset of SQL, but that's a bit of an overstatement, because only the most common SQL statements can be used easily in a PL/SQL program.

PL/SQL, then, is a language that is closely related to SQL, but one that allows you to write programs as an ordered series of statements. Or, if you want a definition of PL/SQL that befits a programmer:

PL/SQL is a procedural (Algol-like) language with support for named program units and packages; much of its syntax is borrowed from Ada, and from Oracle's SQL it derives its datatype space and many built-in functions.

But if that doesn't make any sense, don't worry about it! You'll get the same message in plain English in the forthcoming pages.

Also New to SQL?

If you're completely new to the relational database world, you will also want to learn more about SQL, which is beyond the scope of this book. Fortunately, or perhaps unfortunately, there are hundreds of SQL training materials on the market, including many web sites and books. Although neither of O'Reilly's two books on SQL qualify as tutorials, you may still find them helpful to have on your bookshelf: Oracle SQL: The Essential Reference, and SQL in a Nutshell: A Desktop Quick Reference, the latter of which addresses multiple vendors' versions of SQL (Oracle, Microsoft, PostgreSQL, and MySQL). A popular tutorial-style book is the Oracle SQL Interactive Workbook by Alex Morrison and Alice Rischert. As far as web sites go, you might try "SQL for Web Nerds" at www.arsdigita.com/books/sql.

Why SQL Is Not Enough

As a beginner in the world of relational databases, you might wonder why SQL, which is supposed to be so wonderful, isn't always enough. It is true that SQL's high-level operations are a big boon to programmers dealing with relational databases, but the real world of programming includes many tasks other than straight database manipulation. SQL is not a general-purpose language for expressing computer algorithms. Although you can build a SQL "program" that consists of a sequence of SQL statements, such a program could not have any "conditional" statements. That is, SQL has no convenient way to say, "IF something-is-true THEN do-this OTHERWISE do-something-else."Example 1-1).

Example 1-1: A Wimpy PL/SQL fragment
BEGIN
   IF TO_CHAR(SYSDATE, 'DAY') = 'TUESDAY'
   THEN
      pay_for_hamburgers;
   ELSE
      borrow_hamburger_money;
   END IF;
END;

In addition to the IF-THEN-ELSE statement, this PL/SQL code fragment shows BEGIN...END delimiters, none of which you'll find in SQL. Borrowed from SQL, though, are the SYSDATE function, which returns the date and time on the system clock at the moment that you call it, and TO_CHAR, which converts the date bytes to something understandable such as the day of the week. The statements

pay_for_hamburgers; 

and

borrow_hamburger_money;

are the names of stored procedures [3] to let it talk to Oracle, boom! Herman can bypass all your carefully programmed security checks!

Without PL/SQL, it is quite easy to expose your data to intentional or unintentional tampering. Using PL/SQL as a programming tool (particularly in combination with a feature introduced in Oracle8i called "Fine Grained Access Control") can help lock up this "back door" into the database. Chapter 7 examines PL/SQL's security features.

A Meaty PL/SQL Example

Enough talk, let's code! Drawing from the world of the neighborhood library, here is a PL/SQL stored procedure that might run when a patron returns a book to the library. The example in Figure 1-1 expresses a lot of ideas about PL/SQL. At this point, you will probably just want to scan it for pieces that seem interesting, and then proceed to the discussion that follows.

Figure 1-1. Example PL/SQL stored procedure for handling library book returns
Figure 1

The idea behind this program is that it would support a library clerk who checks in books by scanning them with a barcode reader. (There would be some other program to supply the barcode identifier and, optionally, the date when the book was returned to this return_book procedure.) The overall arrangement and flow of the example is as follows:

  • In the program specification, declare the program name and the parameters it will accept. Here, we accept a barcode ID and the date the book was returned. If the calling program does not supply a return date, the program defaults to use the current date (based on the database server machine's internal clock).
  • In the declaration section, define variables that will be used inside the program, including a "cursor" that will allow us to query the borrowing_transaction table.
  • In the first executable statements, open and fetch from the cursor to attempt to retrieve a record that corresponds to the supplied barcode_id.
  • If no such record exists, log an error message (by raising, and then handling, an "exception") by invoking a separate stored program that we have previously written, log_transaction_error.
  • If a matching transaction record does exist, update it to reflect the fact that the book has been returned.
  • Compare the check-in date to the due date, and assess a fine if the book is returned late.

In this prose summary, the program should make at least some sense. I won't discuss the details of the code here, but there are a few things I would like to emphasize that might not be apparent in the figure:

  • The CREATE PROCEDURE statement causes Oracle to load the program into the database server. If everything succeeds, the procedure remains in the database, available to execute later. Chapter 3 discusses more about creating stored procedures.
  • PL/SQL uses "blocks" to organize code, and the blocks are delineated by keywords including BEGIN and END. Details are in Chapter 2.
  • PL/SQL programs are often populated with many SQL statements such as SELECT and UPDATE. Conveniently, these SQL statements drop right into the code without much fuss.
  • When retrieving data through a SELECT statement, you will fetch one row at a time using a thing called a cursor. A detailed discussion of this appears in Chapter 4.
  • You can use PL/SQL program variables directly in the embedded SQL statements. In the first UPDATE statement in the example, Oracle assigns the value of the variable trunc_return_date to the value in the table's return_date column.
  • PL/SQL is a "readable" language. Well, it should be, anyway, if the programmer uses reasonable names and follows simple coding conventions. The value of readability will become apparent the first time you have to make a change to some code that someone else wrote!

Now that we've seen a short but rich example of PL/SQL, let's take a look at how PL/SQL fits into the big picture of the Oracle database.

What, exactly, does it mean that PL/SQL executes "inside the database server"? To understand the answer, it's helpful to know a bit about how the database works.

As illustrated in Figure 1-2, client programs can make calls to a PL/SQL program running inside the Oracle database server. Virtually any database-aware programming environment can invoke PL/SQL stored procedures: Visual Basic, C, Java, even another Oracle database. The stored routines can, in turn, call others in a very efficient manner, performing manipulations of the database, computations, or lookups as needed by the program that originally made the request. Results and status codes then pass back out to the calling program. The figure also shows that in an Oracle database server, all contact with the data on disk goes through a core set of background processes, and PL/SQL runs quite intimately alongside these processes. The net result is a high-performance database that can have a lot of "smarts" supplied by the programmer.

Figure 1-2. Simplified representation of PL/SQL in the Oracle Server
Figure 2

PL/SQL can also run on client machines that are not running a database server but that can talk to the database server machine over a network. This kind of arrangement would use Oracle's application development tools like Oracle Forms. [5] of your application, and it will pass your input to PL/SQL. In some ways this is a good thing, because it forces you to separate the concern of data management from the concern of user interface design. See Chapter 4 for examples of using a web-based front end to PL/SQL.

Proprietary language
PL/SQL is proprietary to Oracle Corporation and is not useful with any other vendor's database product. While there are some ways to integrate the Oracle database server with other vendors' servers, PL/SQL won't execute anywhere but Oracle. This is unfortunate for independent software vendors who prefer to build their database-aware products to run against different databases. Also, very large companies suffering from "let's get one of everything" syndrome are unlikely to settle on PL/SQL as their standard language for procedural database programs.

Limited object-oriented features
(Beginners, skip this paragraph.) Up until Version 9, PL/SQL was lacking in object-oriented programming language features, although Version 8 did add support for abstract datatypes. Object- based programming was even reasonable to achieve using PL/SQL packages in Version 7. Oracle9 i introduced more object-oriented features, such as multi-level collections, inheritance, and runtime polymorphism, although there are still some unfortunate limitations such as no private methods.

Now just hold on here, you're saying to yourself--if PL/SQL is often only part of a complete application, and only works with the Oracle database, why not just use one of the multi-purpose languages like C or Java for everything? Why bother with PL/SQL at all?

Why Use PL/SQL?

To fully understand why and where PL/SQL is a good fit, it's important to understand the limitations of alternate languages. Let's first hark back to the early days and find out why PL/SQL exists at all.

"I'd Rather Use a `Real' Language Like C!"

Before PL/SQL, the only way to bundle up Oracle's SQL statements inside complex procedural programs was to embed your SQL in another programming language, which was typically C. This was essential because SQL alone has no way to enforce rules such as "when you sell a widget, total the monthly sales figures, and decrease the widget inventory by one," or "only a manager can discount blue widgets by more than 10%." So the C programs had to enforce those business rules.

While using a "host language" like C can work, more or less (as long as everybody is strictly required to use the application program--and that's a big if), it has some other limitations:

  • Different vendors' C compilers and libraries are not 100% compatible, making it expensive to port application programs from one maker's computer to another. Even if the code doesn't change, you still have to test it. Because Oracle designed PL/SQL to run identically on every platform, though, stored procedures are reusable across different server hardware and operating systems, with minimal testing required (after testing on one platform, some people don't even bother to test PL/SQL before using it on another platform). This turns out to be important not just to customers' applications but also to Oracle itself, since it lets the company easily package and deliver new features on all 80+ platforms where the Oracle server runs. (One of Oracle's hallmark marketing angles has long been the promise of "running everywhere.")
  • Despite widespread adoption, C is generally considered more suited for a class of programming tasks that does not include writing typical business applications. Programmers in the corporate MIS shop usually prefer languages immune from the peril of C's "pointers." In addition, text manipulation in C is sort of tedious compared to PL/SQL.

As Oracle began to mature, though, the database industry began to see the wisdom of moving processing logic into the database server itself. Even though C can be the right answer in many cases, a C program will always execute outside the database server; it cannot be used to program a true stored procedure.

Why Should I Use Stored Procedures at All?

Although there are many arguments in favor of stored procedures, they have evolved a bit over the years. Back when the stored procedure feature was new, you had only two choices for where to locate the Oracle application logic: the client, which was usually a PC, or the database server, which was usually running on a higher-powered minicomputer. It was easy to make a case in favor of using stored procedures by pointing out their help in centralizing complex code, securing the database, reusing software, and increasing performance.

Why Is PL/SQL As Fast As It Is?

Executing in close proximity to the data in the database, PL/SQL allows for highly efficient database reads and writes. Why?

First, PL/SQL's variables store data in the same internal binary format as the database. For example, when retrieving a numeric value from the database, you don't have to convert it from one set of bits in the database to a different set of bits in the program; you just make an exact copy of the bits. By contrast, if you read numeric data from the database into a Java program, you are likely to need to convert it into something like a Java "BigDecimal." Now, this may seem like a point only a geek could love, but, when multiplied by thousands or millions of occurrences, it can turn out to be a big impact--not only in ease of programming, but also in ease of performance.

Second, server-side PL/SQL gets executed inside the same running program (in the same memory space) as the Oracle server itself. This translates into another performance win because there is extremely little communications overhead required for the program to talk with the database. Normally, this overhead would be either in the form of network bandwidth or in the CPU power and memory required to use the computer's internal messaging system known as inter-process communication.

It is true that PL/SQL has for years been an "interpreted" language rather than a true "compiled" language, resulting in some kinds of operations being slower. Even though millions of users found PL/SQL's interpreted performance to be acceptable, Oracle introduced a native execution feature in Oracle9i that can dramatically accelerate execution speeds. It actually translates your PL/SQL into C and compiles it into machine-specific binary form. See Chapter 9 for more details about compiling and native execution.

Nowadays, though, a common arrangement is to use one or more middle-tier machines between the client (which is now often a simple web browser) and the server. The middle tier typically runs the application logic on some convenient platform using some convenient language. Many of the benefits of using stored procedures can accrue to this multitiered arrangement, and the arguments in favor of stored procedures have evolved since the early days. I've narrowed them down to only four basic arguments, but they are critical:

Fewer things to break
By relying on stored procedures, there are fewer "moving" parts in the overall system. Controlling a development effort with client, middle-tier, and server-tier components requires not only inspired and skillful managers but also, in many cases, a great deal of luck. In addition, as time goes on, the evolution of a system with fewer components is likely to be simpler and less expensive.

Centralized consistency
Stored procedures provide greater assurance of data integrity. It's just easier when you don't have to secure both the database and a middle tier. The term "secure" here encompasses both privileges (Joe has the privilege to update the table of accounts) and business rules (no transactions permitted on accounts more than 30 days past due).

Performance
Stored procedures can potentially yield greater performance, as discussed in the sidebar " Why Is PL/SQL As Fast As It Is?"

Developer productivity
Stored procedures can facilitate greater productivity when you write applications with products that assume the presence of tables in the database. In Oracle, you can, for example, write stored procedures that allow other programs to insert, update, and delete through database views.

Okay, let's assume you like the sound of those four benefits, that you are using Oracle, and that you definitely or possibly want to use stored procedures. It does not automatically follow that you should use PL/SQL; you might prefer to use the Java programming language, which Oracle supports as an alternative. If you have time to learn only one language, which should it be?

"Hey, Isn't Java Where It's At?"

A lot of Oracle programmers wonder whether they would be better off using Java for all their stored procedures. It is true that Java offers some features that are impossible to program directly in PL/SQL. But there are several striking advantages to using PL/SQL. First off, PL/SQL can offer superior performance to Java, as discussed in the earlier sidebar, "Why Is PL/SQL As Fast As It Is?" Another major argument in favor of PL/SQL is that as a companion to SQL, PL/SQL offers uniquely close integration. This section explores four examples of this integration:

  • PL/SQL is more concise than Java.
  • You can call PL/SQL functions directly from SQL.
  • PL/SQL can run without human intervention.
  • Many cool features are only accessible via PL/SQL.

Let's look at each one in turn.

PL/SQL is more concise than Java

Using SQL statements within PL/SQL is free of programming "cruft" (programmer's slang for superfluous code). Without getting into the finer points about cursor-FOR loops, automatic declarations, statement parsing, etc. (described in later chapters), suffice it to say that PL/SQL can accomplish more using fewer lines of code than any other SQL-hosting programming language you care to use with Oracle. Well, at least when compared to Java. Take a look at the code fragment in Table 1-1.

Table 1-1: Simple code fragment in PL/SQL and Java
PL/SQL
IF return_date_in > l_due_date
THEN
   days_late := trunc_return_date - TRUNC(l_due_date);
   UPDATE borrowing_transaction
      SET fine_amount_usd = NVL(fine_amount_usd,0) 
          + days_late * daily_fine_usd
   WHERE barcode_id = barcode_id_in;
END IF;
Java (using JDBC)
if (returnDate.after(rs.dueDate)) {
   s = "UPDATE borrowing_transation ";
   s += "SET fine_amount_usd = NVL(fine_amount_usd,0) ";
   s += "+ (TRUNC(?) - TRUNC(?)) * ? "
   s += "WHERE barcode_id = ?";
   PreparedStatement ps = aCon.prepareStatement(s);
   ps.setDate(1, returnDate);
   ps.setDate(2, dueDate)
   ps.setInt(3, dailyFineUSD);
   ps.setString(4, barCodeID);
   ps.executeUpdate(  );
}

In the Java/JDBC

Lessons From My Father on the "Best" Language

Rewind to the last millennium, back when I was about eight. I used to spend long hours working in my father's workshop, "helping" him with his latest project. A natural tinkerer, my father was always building some gadget or gizmo, and I'd hammer and saw right along with him. Although many of his creations are still in use today, I get more frequent mileage from one of his oft-demonstrated lessons that really stuck with me:

You gotta use the right tool for the job.

In my father's world, this message surfaced repeatedly, forcefully, and unforgettably. When confronted with a Phillips head screw, don't use a flat-bladed screwdriver just because it's the one in your pocket. Use clamps to hold stuff you've glued while it's drying. Only on rare occasions is it okay to install a screw with a hammer. And so forth.

This goes a long way toward explaining my maxim that you shouldn't fight any meaningless battles over what the "best" language is, because each has its uses. In other words, use the right tool for the job.

When Is PL/SQL the Right Tool for the Job?

How will you recognize the job for which PL/SQL is the right tool? Wrong answers may not be as obvious as when you pound a screw with a hammer. Right answers sometimes require quite a bit of experience with a number of different languages, plus sufficient scrapes, bruises, and hair-pulls from years of trying things the wrong way (my thumb is healing up nicely, thank you, but my hair is still a little thin).

Table 1-2 summarizes some of the major differences between the two languages, in very high-level and admittedly subjective terms.

Table 1-2: Comparison of PL/SQL and Java as the language of stored procedures

Criteria

PL/SQL

Java

Suitable for stored procedures in...

Oracle (all currently supported versions)

Oracle8i, DB2, Sybase, Informix, several others (but not Microsoft's SQL Server)

Suitability for use in other tiers

Client-side or middle tier when using Oracle tools

Client-side or middle tier with many vendors' products

Portability of your programs to different DBMSs

None

Excellent if programmer avoids proprietary database features

Conciseness of code for common database tasks

Excellent

Not so great

Portability of your programs to different operating systems

Excellent

Excellent

Overall performance and scalability in database usage

Excellent, particularly using native execution in Oracle9i

Scales well, but some performance problems with lots of database interaction

Well-supported programming styles

Functional decomposition; object-based

Object-oriented programming (OOP)

Ease of learning

Moderately easy

Steep curve; requires knowledge of OOP to use well

Ratio of features to complexity

Excellent

Fair

Most business programmers (that is, programmers such as MIS staff who work in non-software industries) tend to prefer PL/SQL because it is a lot easier to learn and use than Java, which can be cryptic and verbose. In contrast, many software-industry programmers often prefer Java because of its object-oriented features and wide support. Of course, you have to factor in your existing programming skills and knowledge; are you more likely to build the desired system(s) in the allotted time in PL/SQL or in another language?

The Best of All Worlds

It turns out that you can integrate PL/SQL in conjunction with many other popular languages. If you're already writing in a language like C, Perl, Java, Ada, FORTRAN, or COBOL, you can use it to call your PL/SQL programs. You can pass data and other information (like exceptions, covered in Chapter 3) back and forth. The way you call PL/SQL programs from these other languages is usually a simple extension of the way the other language calls SQL. In fact, in some cases it's easier to call PL/SQL from one of these languages than it is to call SQL, because you can migrate iterative program logic into PL/SQL, thereby replacing (in some cases, anyway) a lot of tedious fetch loops with a single call to a named PL/SQL program.

In addition, with a bit of cleverness, PL/SQL can also invoke programs written in these other languages. See Chapter 8 for an introduction to the use of external procedures.

What You Need to Get Started with PL/SQL

Now that you're sold on PL/SQL, I'd like to change gears a bit and give you some practical information on what you'll need to start programming.

First off, you'll need access to an Oracle database. Since Oracle is a product designed to be shared, it isn't necessary to have your own private copy of Oracle on your own private machine. You just need an account in an Oracle installation where the administrator will let you experiment with PL/SQL. You can use your desktop machine merely as a tool through which you connect to a database on a different machine. If you don't have that, though, you might have to set up your own Oracle database.

In the simplest arrangement, you would have the Oracle server running on a machine on your desk, where you would also do all your development. There are four things you will need:

  1. Access to a "big enough" machine running an operating system supported by Oracle
  2. A licensed copy of Oracle's server software, available free (with some restrictions) from Oracle's web site
  3. A text editor
  4. A copy of this book

Since you've already got the book, and getting #2 will also get you #3, you're halfway there already.

Hardware and Operating System

If you want to install the Enterprise Edition of Oracle9i on a typical Unix machine, Oracle says you need at least the following:

  • 256 megabytes of RAM
  • 2.5 gigabytes of disk for software and starter database
  • 400 megabytes (or more) of swap space during installation

Or, if you want to run the older release, Oracle8i, on a Windows NT or 2000 machine, you'll need a machine something like this:

  • Pentium 166MHz or better processor
  • 96-megabyte RAM (256 megabytes is recommended)
  • 2 gigabytes of disk space

As you can see, the actual hardware requirements depend on the Oracle version and options you want to use (and, to a lesser extent, on the operating system). As for the operating system, Oracle generally provides licenses for developers (see the next section) on the following:

  • Windows NT, Windows 2000, and Windows XP, Professional (some Oracle versions are even available for Windows 98)
  • Intel Linux
  • Sun Sparc Solaris (a Unix flavor that runs on Sun and Sun-compatible hardware)
  • Some Oracle versions are available for other Unix flavors such as Compaq Tru64 Unix and IBM's AIX

It is probably not sufficient to have the version of the operating system that happened to come "out of the box" with the hardware. In addition to matching the exact version number that Oracle supports, you must ensure that the operating system on the machine has the proper patches (or service packs) installed.

WARNING:  Obtaining the proper version of an operating system for your version of Oracle, and then applying the necessary patches, is usually a task big enough to be annoying. Be sure to follow the instructions in whatever documentation Oracle supplies that is specific to your platform. You should always check the documents that have the name Installation Guide or Release Notes or README in the title. These documents should also contain the exact hardware requirements.

What's the "best" hardware and operating system for Oracle? For a beginner, my answer is always "whichever one you are most comfortable with." Too many new toys makes your life unnecessarily complicated.

A Licensed Copy of Oracle

The next step is to acquire and install a licensed copy of Oracle.

Acquiring Oracle

Oracle offers a single-user, development-only license for free, as long as you agree to a lot of legal fine print. To obtain a copy of the Oracle server software for use by an individual developer, you can visit the Oracle Technology Network (OTN) web site, sometimes known as Technet, at http://otn.oracle.com. If you have a very fast Internet connection or a lot of time, you can actually download a copy of the software itself. Be warned, though--you may have to download more than a gigabyte of stuff!

If a 48+ hour (at 56K) Internet download isn't your idea of fun, you may be able to order what they call a "CD Pack," currently around $40 in the U.S., or possibly a "Technology Track" subscription, which I bought at one point for about US$200/year (but it was not available to order the last time I checked). Maybe they have some new deal by now.

When downloading or ordering, you will at some point have to designate which version of which Oracle server you want. After identifying your hardware, you need to choose a version of the database server. My suggestion for beginners is to get the latest available version of the Enterprise Edition unless your organization has a specific requirement for you to learn or support something else. The Personal Edition is probably okay too; I believe it actually includes almost all the features of the Enterprise Edition.

Installing Oracle

I wish I could include detailed instructions, but the installer varies according to Oracle version and behaves slightly differently on different platforms. Instead, I will point you to the relevant documentation that will help you do the installation properly. In addition to Oracle's Installation Guide (IG) appropriate to your platform, look in particular for:

  • A file in the root directory of the installation media called index.htm or index.html. If you find it, open it in your web browser.
  • Anything in a relnotes (Release Notes) subdirectory.
  • Anything with README in its filename (and also the lowercase readme, if your operating system is case-sensitive), especially files with rdbms in their names.
  • Anything in a doc subdirectory, especially if there is a subdirectory rdbms/doc.

Some of these documents may be available on the OTN web site, but others might only be available after you download and "expand" the software and start poking around in the resulting directories and files. And some may only be available after you've actually installed the software!

If you've never installed Oracle before, I recommend using as many of the default settings as possible. You can almost always rerun the installer later and add or modify the options. I will also mention that if the installer gives you the choice, be sure to install the built-in web server features, known as "Oracle HTTP Server powered by Apache," or some combination of those words. It may also give a URL to the local web server's administrative page; be sure to write down or copy relevant information.

A Text Editor

A text editor is a program that allows you to create and modify documents such as programs that consist of text only--that is, no fonts, borders, colors, graphics, or other fancy stuff. I've included this requirement as something of a joke, because, as Table 1-3 illustrates, each operating system includes a text editor of some kind.

Table 1-3: Text editors for various environments

Environment

Common text editors

DOS

edit

Windows

Notepad, Wordpad (in text-only mode)

Unix, Linux

vi, GNU emacs

Macintosh

Teachtext, Simpletext

Of course, in addition to the hundreds of different text editors available, there are also commercial programmer's editors and entire interactive development environments available, some of which are built specifically for PL/SQL. Chapter 6 describes these in more detail, although as something of a traditionalist, I don't recommend that beginners use these tools right away.


1. More advanced readers may correctly point out that Oracle's version of SQL includes a nonstandard though useful function known as DECODE, which provides a crude if-then construct.

2. A stored procedure is a program that resides and executes inside the database server. Most of this book is about stored procedures.

3. A common mechanism for this purpose is known as Open DataBase Connectivity (ODBC), used in widely available tools such as Microsoft Excel. Oracle's ODBC "drivers," as they are called, are freely downloadable from Oracle Technology Network (OTN) web site, sometimes known as Technet, at http://otn.oracle.com.

4. Over the years, these tools have been known by a variety of names, such as Internet Developer Suite, Oracle Developer, and Oracle Developer/2000.

5. Somewhat anthropomorphically, the terms back end and front end generally refer to the server and client parts (or the computational and user interface portions) of an application. As an example, a web browser might display the front end, and a server machine running the application might be the back end.

6. JDBC is the standard Java library for connecting Java programs to SQL-based databases. JDBC doesn't officially stand for anything, but most people think of it as Java DataBase Connectivity.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.
This was first published in May 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close