Learning Oracle PL/SQL? Here's What to Expect

Editor's Note: Many of the links in the following article are outdated and may produce a "page not found" error.

If you've ever written applications that use a database management system, you have almost certainly run into the quasi-standard database language known as Structured Query Language, or SQL. And you soon discovered that SQL might be a decent tool for creating and manipulating data in relational tables, but it only takes you so far; in fact, SQL was never designed as a complete substitute for a real language.

Related Reading Learning Oracle PL/SQL By Bill Pribyl Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

Oracle Corporation, realizing the benefit of supplementing SQL with a good, cross-platform programming language, introduced Procedural Language/SQL, or (PL/SQL) more than a dozen years ago. They designed it as a more complete language that would run inside Oracle's database server, tightly integrated not just with SQL syntax but also with the internal gears and pinions of the database itself. Over the years, Oracle has continuously refined and polished PL/SQL, to the point where it remains one of the key reasons that many people (myself included) actually enjoy writing programs for the Oracle database server.

To help prepare a newcomer into the world of PL/SQL, I have collected some thoughts about the way that most new users seem to react to the language. Specifically:

1. What most surprises people about PL/SQL
2. The most wonderful things about PL/SQL
3. What might confuse you the most about PL/SQL
4. PL/SQL's top annoyances
5. The most helpful things while learning PL/SQL

The answers are based not just on my own experiences as a trainer and consultant but also on insights shared by colleagues expert in PL/SQL who get to interact with quite a few beginners.

What Most Surprises People About PL/SQL

If you already have some experience in another language, you are likely to be surprised by how much functionality there really is in PL/SQL. Most people tend to think of it as SQL plus some "other stuff." Really, it's more like a programming language that happens to include SQL.

To emphasize this point, here is a list of some of its other stuff:

• Control structures (if-then-else, case statements, various types of loops)

• Means of creating reusable procedures and functions

• Packages to bundle related procedures, functions, and declarations into a single, reusable programming unit

• Record and array-like data structures

• Support for object-oriented programming

• Built-in utility packages that let you do things like send Internet email, read and write from operating system files, and fetch Web pages via HTTP

• Tools to create entire Web sites using PL/SQL

• Facilities for calling out to C and Java programs

• A small but growing body of open source code

Part of the reason for the misperception about PL/SQL is because of the conventional textbook description of PL/SQL as Oracle's "procedural language extension to SQL." While such a description is at least partially true--it is a procedural language--it can be quite misleading to think of it merely as a SQL's extension.

Another surprise that often strikes new PL/SQL users is how easy they find it to write useful programs in PL/SQL. Now, not everyone has immediate success, but you should be on the lookout for this phenomenon.

The Most Wonderful Things About PL/SQL

Almost without exception, new PL/SQL programmers are amazed and delighted when they discover how easy it is to interact with the database using SQL statements, particularly if they've had to suffer through the way other languages force this union. For example, here is a code fragment showing an IF-test and SQL UPDATE statement (shown below in bold) in Java:

     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();
     }

(The question marks are not part of SQL, but are placeholders for values supplied in Java variables.) But an equivalent PL/SQL fragment would look like this:

     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;

As you can see, you can drop the SQL statement in using a very natural and intuitive syntax. Not only is the PL/SQL version shorter, but most beginners will find it easier to follow and easier to maintain.

Let's look at another wonderful and even beautiful aspect of PL/SQL: the easy way it lets you fetch data from the database. Let's say you have a SQL SELECT statement that identifies potentially hundreds of rows in a database table:

     SELECT title, publication_date
       FROM books
      WHERE author LIKE '%Shakespeare%';

Imagine you are writing some PL/SQL code where you just want to print out this information. Doing so could be as simple as:

     BEGIN
        FOR rec IN (SELECT title, publication_date
                      FROM books
                     WHERE author LIKE '%Shakespeare%')
        LOOP
           DBMS_OUTPUT.PUT_LINE(rec.title 
              || ' published in ' || rec.publication_date);
        END LOOP;
     END;
     /

That's actually the entire program! It uses a cursor FOR loop that automatically declares a cursor for you and fetches each record from the database, allowing you to refer to individual columns using convenient "dot" syntax.

Finally, C and C++ programmers who pick up PL/SQL are almost always happy to discover that there is no need for explicit memory management--Oracle handles it for you.

What Might Confuse You the Most About PL/SQL

The short answer to this one is, "Anything that isn't present in the language you already know." (If you don't know any programming languages yet, the answer is different, of course) Since I don't have the space in this short article for all the answers, I'm just going to give you a quick tour of things that could require a little extra attention while you're learning the language. Note: Although I've included some hyperlinks to these topics in Oracle's official product documentation, you might prefer my book, Learning Oracle PL/SQL, for explanations tailored to beginners.

If you're already, say, a C or Java programmer, but you've never worked on a program that interacts with a database, one of your stumbling blocks in PL/SQL is likely to be cursors. Simply stated, a cursor is a special variable that lets you fetch data from the database. The tricky part that comes up is the manner in which you must operate on the cursor, combined with the fact that there are two types of cursors, explicit and implicit, whose behavior differs.

The next sticky wicket for many beginners is PL/SQL's collection data types. Even if you have used arrays in another language, it's not always obvious which of PL/SQL's data structures will best serve your need to have something like an array. On the other hand, if you've come from a language that lacks composite datatypes, or if you've never used them, all of PL/SQL's composite data structures are likely to present a special challenge.

The third confusing thing is the ELSIF keyword, but this one is the easiest to explain. Yes, ELSIF is spelled funny. It's an optional part of an IF statement can be an alternative to nesting one IF statement inside another. By way of example, instead of this ugly nested thing:

     IF foo = 'APPLE'
     THEN
        eat_foo;
     ELSE
        IF foo = 'WORM'
        THEN
           pitch_foo;
        END IF;
     END IF;

...you could write this slightly more pithy code instead:

     IF foo = 'APPLE'
     THEN
        eat_foo;
     ELSIF foo = 'WORM'
     THEN
        pitch_foo;
     END IF;

Most people find this code easier to comprehend. Notice there is only one END IF in the second case; that's because there is only one IF statement. (If you consider the way I've indented the code, you can see that the ELSIF is part of the outer IF statement.) If you happen to be using Oracle9i's version of PL/SQL (pdf file), you could actually recode this as a CASE statement, but in this case, it wouldn't save you any lines of code.

Another big winner in this so-called confusion category can be the whole idea of using a terminator. At the end of every declaration and at the end of every statement, PL/SQL requires a terminator, that is, a semicolon. Since some languages (like FORTRAN) don't use terminators, this seemingly small point can be a big deal. If you remember that statements can appear inside other statements yet they must all still be terminated with a semi-colon, you shouldn't have too much trouble.

Oracle OpenWorld 2001 Dispatches--If you missed this year's Oracle OpenWorld, these reports from O'Reilly Oracle editor and author Jonathan Gennick and Amazon.com technical lead Jeremiah Wilton will fill in some of the blanks.

Finally, PL/SQL's concept of a package is also different from that of other programming languages, which explains why some students of the language (unwisely) avoid packages altogether! Using packages is perhaps the single best technique most programmers should master if they want to take their code to the next level of sophistication and reusability.

PL/SQL's Top Annoyances

When learning PL/SQL, you are very likely to encounter error messages that won't make a lot of sense to you. Consider this example (the boldface text is what I typed in):

	SQL> DECLARE  
	  2     INTEGER x;
	  3  BEGIN
	  4     x:= 0;
	  5  END;
	  6  /
   	   INTEGER x;
           	   *
	ERROR at line 2:
	ORA-06550: line 2, column 12:
	PLS-00201: identifier 'X' must be declared
	ORA-06550: line 2, column 12:
	PL/SQL: Item ignored
	ORA-06550: line 4, column 4:
	PLS-00201: identifier 'X' must be declared
	ORA-06550: line 4, column 4:
	PL/SQL: Statement ignored

Clearly, I've made some sort of mistake with identifier X, but what is it? Even if I can find my way to the right place in Oracle's Error Messages manual, I still may not get enough of a clue. My mistake? I've reversed the position of the variable and the datatype (tried to do it the way C does it). The correct code is:

	SQL>  DECLARE
	    2    x INTEGER;
    	    3 BEGIN
    	    4    x:= 0;
	    5 END;
	    6 /

	PL/SQL procedure successfully completed.

There are other gems. One of my all-time favorite messages is ORA-03113: end-of-file on communication channel. The first time I ever saw that I thought something had gone horribly, horribly wrong. But it only means that I'd lost touch with the Oracle server. The funny thing was, though, that it had occurred in a circumstance other than one listed in the official documentation. The solution to a 3113 is to always disconnect and reconnect to the server.

Some of the most annoying things PL/SQL newcomers encounter are not truly PL/SQL problems but instead emanate from an Oracle tool called SQL*Plus. SQL*Plus is Oracle's command-line interpreter that allows you to type in SQL and PL/SQL commands and run programs that consist of these commands. Frequently, new users encounter the following mystery message when running SQL*Plus on a Microsoft operating system such as Windows 95 or Windows NT:

Input truncated to n characters

This occurs when executing a script (a file containing SQL and/or PL/SQL commands) from within SQL*Plus. There is no good reason that SQL*Plus gives this error message; it occurs when the last line of the file isn't blank. The fix is easy: just edit the script and append a blank line to the very end. Or switch to Unix.

Another annoyance in SQL*Plus has to do with getting your PL/SQL program to print to the screen. Oracle provides a built-in package called DBMS_OUTPUT.PUT_LINE that is supposed to let you do this, but in SQL*Plus it only works if you've first run the command:

SET SERVEROUTPUT ON

This setting will stay in effect until you set it Off or disconnect from the Oracle server. It's quite mysterious to me why Oracle decided the default for this setting would be Off.

The last thing I'll mention in the annoyances category is PL/SQL's lack of integration with operating system functionality. For example, it's not easy to have a PL/SQL program send output directly to a printer. Over the past few years, however, Oracle has provided increasingly reasonable workarounds to this shortcoming, such as external procedures and Java integration.

The Most Helpful Things While Learning PL/SQL

Sometimes there is no substitute for having live access to a real flesh-and-blood expert (or at least someone who started before you did) who is available to help. Occasionally, confusion can make it nearly impossible even to formulate the right question, and the fastest road to comprehension is the old-fashioned, face-to-face encounter between a student and a teacher. Some good third-party trainers include PL/Solutions, Sintaurus, Thinkspark, and DataCraft. (OK, I admit it: that last one is my company.)

Reviewing other people's code samples is another great way to learn. A few places you might find good PL/SQL code on the Internet include the Oracle Technology Network, PLNet, Revealnet, and JLComp.

Of course, having a good book doesn't hurt either. This was clearly another setup since I just wrote, with my friend and coauthor Steven Feuerstein, such a book. (Well, I hope it's good, anyway.) But there are lots of PL/SQL books on the market.

If you can't attend a training course, can't find an expert, and you've left your copy of my book on your nightstand, there are a few PL/SQL discussion groups (Revealnet, orafaq, Oracle Corporation) available on the Internet where you can post a question for others to contemplate. If you want to try an electronic book, you can get an online subscription to a number of PL/SQL books by using O'Reilly's Safari Tech Books Online. Be sure to check the Open Directory Project's PL/SQL page for more Internet resources.

And what about Oracle's own documentation? It has certainly gotten better and better over the years, though it's also gotten more and more voluminous as Oracle adds features. A relatively new search facility gives anyone on the Web the ability to search the entire documentation set electronically. This tool is nearly indispensable, as long as you already have a good idea of what you're looking for.

Acknowledgments

Thanks to Dick Bolz, Ron Martini, Miriam Moran, and Chris Racicot for sharing the insights they've gleaned from their many hours spent helping PL/SQL programmers.

O'Reilly & Associates recently released (November 2001) Learning Oracle PL/SQL.

• Sample Chapter 1, PL/SQL: What, When, and Where, is available free online.

• You can also look at the Table of Contents, the Index, and the Full Description of the book.

• For more information, or to order the book, click here.

Bill Pribyl author, teacher, and software consultant, is coauthor of the best-selling Oracle PL/SQL Programming and its companion pocket reference, both published by O'Reilly Media, Inc..

Return to oracle.oreilly.com.