PL/SQL features by release
Oracle tips by Burleson
A Mini-History of Oracle and PL/SQL
by Lewis Cunningham
This chapter answers two questions: where did PL/SQL
come from and why is it the best database development language ever developed?
In the late 70s, around the time Ingres was getting
started at UC Berkeley, three guys working on a contract for the CIA got
together and started a company called Relational Software, Inc.
Their first product was a relational database called
Oracle. The founders decided to use the C language for development. This would
later become important when they decided to start porting to different
They also decided to support SQL as the internal data
access language. This would also become a very important factor to its
success. In 1979, Relational Software was the only company making an SQL
compliant database. If anyone ever asks you who wrote the first SQL database,
you now know the answer: Oracle.
To access the database, to write an application for
example, you had to use an external language and compiler. In the early days of
Oracle, that was C but, in time, several other languages were added: COBOL, ADA,
Fortran, PL/1, and others.
In the early 1980s, the company was renamed Oracle
Corporation. That would just be the beginning of Oracle?s desire to rename its
products. In my time using the Oracle database, I think every tool I have used
has been renamed at least once. In the case of CDE/Developer 2000/Developer
Suite, it has been renamed enough to be confusing.
Oracle did not have an embedded language for many
years. Having come from a government background, when they chose a language for
the database, they modeled it on ADA.
I programmed in ADA for a few years in the 1980s while I
was working as a consultant for the US Department of Defense. It is a very
powerful, but very wordy, object oriented language. ADA, and by extension
PL/SQL, are descendants of Pascal.
Oracle named this new language PL/SQL; the Procedural
Language extension to SQL. I pronounce it pee ell sequel but many others
pronounce it pee ell ess que ell. Feel free to pronounce it however you like
Being descended from ADA and Pascal, PL/SQL is a
block-oriented language. Delphi has the same roots. Anyone who is familiar with
a block-oriented language should be able to pick up PL/SQL fairly quickly.
Before becoming a PL/SQL programmer, I was an xBase
(Clipper) programmer. XBase is also a block-based language. The move to PL/SQL
was relatively easy for me. I had other reasons to hate the move, which will be
explained in a little more detail later, but learning the language was a
The point I am eventually getting to is that PL/SQL is
not JUST a database language. It is a full-featured language that can be used
for almost any purpose. A later chapter will cover the guts of PL/SQL, but I
also want to make the point that PL/SQL is as easy to learn as it is powerful.
As I reference PL/SQL below, I will use the Oracle
database version rather than the PL/SQL version. For a time, PL/SQL versions
did not match that of the database. For a complete history, that might be
significant but for the mini-history I provide below, I think sticking to the
database version makes the most sense.
Oracle version 6 was the first version to support
PL/SQL. This support was somewhat limited. It was an after-the-fact add-on and
it did not provide PL/SQL as stored procedures.
It did provide PL/SQL as a scripting language.
Exception handling was primitive. The language at this point was dependent on
tricks to get it to do what you wanted. Error handling was practically
non-existent. Converting this version of PL/SQL to later versions was error
That being said, it was a start. Like everything else,
a language has to start somewhere. And while the mighty Oak was still an acorn,
PL/SQL was enjoying its birth.
Oracle version 7 was released around 1992. This was the
first version I ever used. I used a beta version of Oracle 7, a beta version of
Oracle Forms 2.0, and a beta version of Oracle Reports 2.0.
I have to admit that I hated every minute of the move to
Oracle. Up to this point, I had complete control over everything I developed.
I was using Clipper in MS-DOS and C in UNIX. I created tables as I needed
them. I did not think this whole thing with putting all your tables in one big
file (at least that is what it looked like to me) was ever going to catch on.
And I had to say how much space I was going to use before I even used it! How
I also had a DBA who would create my tables with the
columns alphabetized. That drove me up the wall.
These were the days of MS-DOS; the end days. While all
of this was going on, my team and I were also moving from character based
Clipper and C to GUI based Forms and Reports. The tool we used, called Common
Development Environment (CDE) 1.0, was both MS-Windows 3.0 and X-Windows based.
This involved a mouse, a beta GUI form builder and a
semi-GUI OS running on top of DOS. That was a winning combination. We could
crash the database with a divide by zero and crash the GUI by double clicking a
button, which we facetiously called the quick exit. And we could look
cross-eyed at the monitor to get the Blue Screen of Death (BSOD).
From the beginning I loved the language, though. I
thought it was primitive. No way to build a screen, having to resort to the
nasty Oracle Forms tool, and no way to access OS files, but it was intuitive to
I later learned to appreciate the speed of development
with Oracle Forms. Forms 4.0 was awful but later versions were much better.
Oracle Forms 3.0 and up all supported PL/SQL also. For the most part, PL/SQL
has been the embedded language of choice for Oracle?s entire toolset.
Back then, PL/SQL already had the familiar:
And it was a beginning.
As the releases moved up through 7.1, 7.2, 7.3, and
especially the super stable 7.3.4, Oracle added more and more functionality to
UTL_FILE was added to provide OS file access. PL/SQL
Tables (primitive array) was added. Dynamic SQL was added.
The important thing to remember even at this early stage
in its life was that SQL was completely integrated to PL/SQL. This is important
for ease of use and for performance.
Compare selecting a scalar value from the database in
PL/SQL versus most any other language. In PL/SQL it looks like this:
SELECT ... INTO ...
In most any other language it is a connection variable,
a string is built, a string is executed, conversions are done, etc. In Java, a
simple example looks like this:
Connection conn =
Statement stmt =
ResultSet rs =
stmt.executeQuery( "SELECT ...
SQL is native to PL/SQL. But dynamic SQL was also
supported after the introduction of DBMS_SQL.
This takes me back to my words above about the Oak and
the acorn, which is also my little jibe to Java. PL/SQL runs in a virtual
machine. PL/SQL is tightly integrated with SQL. And PL/SQL is much more
write-once, run-anywhere than Java ever was or ever will be.
Install Oracle and your PL/SQL will run. Install Java,
and your Java program might run. Hopefully. With some help. If the virtual
machine matches the development virtual machine. And you are not taking
advantage of any OS specific operations. Etc.
What Java has over PL/SQL is the size and functionality
of the provided class libraries. And some would say better Object Oriented (OO)
support. PL/SQL started life with a small set of supplied packages, which are
the PL/SQL equivalent of the class libraries, and no OO support beyond
encapsulation and data hiding.
Somewhere about 1997, Oracle released version 8. This
is probably the most maligned release Oracle ever made. It was buggy. It took
5 minor releases to get it to a point where it was actually usable.
I used this version for prototyping but nothing I wrote
ever made it into production. I look at Version 8 as a beta for 8i.
Version 8 introduced some heady concepts though: object
orientation via types with methods, external procedures, real partitioning and
star query support, Oracle AQ, and more.
The most important PL/SQL improvements from my point of
view were the object support, AQ, LOB support, enhanced array processing, by
which I mean varrays and nested tables, and the ability to make HTTP calls from
It would take a while for some of these features to
catch on and some of them never really caught fire for most of the Oracle
Fortunately, the Oracle world would not have long to
wait. In 1998, Oracle moved leaps and bounds beyond the competition when it
released version 8i.
This was one of the nicest surprises Oracle ever gave me
(my ACE was the next best). I just knew that this new Internet version was
going to be a bust; another buggy and unimportant release. Boy was I wrong!
Version 8i was a huge improvement over anything that had
come before it. I include databases from any other vendor in that statement.
With 8i, Oracle became the premiere commercial database. Everybody wanted an
Oracle version of their software.
The first version of Oracle 8i was very stable. It had
a few bugs but overall it was a clean implementation. By 8i release 3 (8iR3) or
8.1.7, this would become a hugely popular development platform. Even today in
2006, there are customers BEGINNING development and putting it on 8i. I would
not recommend that but it says a lot about the environment.
A lot of the acceptance of Oracle 8i had to do with the
improvements in PL/SQL and the enhancements to the SQL language. Some of the
* Drop Column
* Enhanced Partitioning and Sub-partitions
* Analytical Functions in SQL
* Autonomous Transactions
* Virtual Private Databases
* WebDB (pre-pre-pre-HTMLDB, i.e., ApEx)
* Java in the Database (even servlet support)
* Online Indexing
* Many SQL and PL/SQL Performance Improvements
* Native Dynamic SQL
* Bulk Operations
* Database Triggers
* The list goes on
Oracle 8i made PL/SQL a viable development environment.
This is the version that I always referred to as the developer?s release. There
was now very little that you needed to do that you could not do. This occurred
about the same time when the standard began to move the application logic into
Just when you think PL/SQL could not get any better, it
Oracle 9i was released in 2001. If 8i was the
developer?s release, 9i was the DBA?s release. But it still had plenty for the
* External Tables
* Pipelined Functions
* Native Compilation (PL/SQL to C)
* Oracle Streams
* XML DB
* Many data warehouse and BI Improvements
* Analytic SQL enhancements and additions
For Oracle 9i, Oracle seemed to be trying to make life
easier for the data warehouse developer. Many improvements were made in
handling large volumes of data.
The external tables saved a lot of time during
Extract/Transform/Load (ETL) jobs. Data files could be accessed from the OS
file system as if they were actual database tables. PostgreSQL and EnterpriseDB
has something much like this, which will be covered later in the book.
Pipelined functions allowed data to be streamed back to
a calling process as if the function was an SQL table. This allowed very
complicated logic to be embedded in a function but still perform acceptably.
PostgreSQL also has something very much like this, which will be covered in
A feature that was very timely for me was XML DB. This
was available as an add-on to 9iR2. The XMLType datatype was added in 9iR1.
The ANSI SQL/XML Standard was also added in this time frame. These three things
basically gave Oracle full native XML support with XPath and XSLT.
Oracle 10g was released in 2004. This was another nice
surprise. This release was mostly bug free and performed extremely well.
While there were many new features for administrators,
the list of new features for developers slowed down some.
* The recyclebin (retrieve dropped objects)
* A new job scheduler with full dependency support
* Asynchronous commits (do not need to wait for it to
* Transparent data encryption (automatic encryption at
the column level)
* Enhanced SQL analytic support (seems to be a trend)
* SQL Tuning Advisor
* Conditional Compilation
* Bioinformatics and OLAP Improvements
* SQL Model Clause (SQL Spreadsheets basically)
* Compression Package
* .Net Stored Procedures
Oracle opened its documentation to the world. It has
been free for a long time but required registration at the Oracle technology web
site. It is now completely open and available to search engines.
Oracle also took a stab at free software. Oracle
jDeveloper, Oracle SQL Developer and Oracle Database 10g Express Edition were
all released. While not open source, it was nice to have some free tools being
PL/SQL 11g and Beyond
We can all be sure that Oracle has not stopped at 10g.
There is currently a lot of speculation about what the future holds. 11g is
expected sometime in 2015.
I would expect improvements to the compiler
optimizations, improvements to native compilations, more analytic support and
performance improvements, even deeper integration of OLAP and BI, and much
more. Beyond that we can expect 12g, and 13r, and 14x and on and on.
PL/SQL History Wrap-up
The purpose of this chapter was to show how large PL/SQL
is and to show that Oracle compatibility has to take into account the structure
of PL/SQL, its integration with SQL and the way the database supports
PL/SQL has been available for over 15 years now. It is
robust and comprehensive. Thousands of applications and millions of lines of
code have been written. It would be as bad a design decision to abandon PL/SQL
as it would be a bad business decision.
Oracle support forums abound on the Internet; both for
PL/SQL and for SQL. There are millions of experienced developers and DBAs.
PL/SQL skills are not a niche skill. Search any job board and you will find
many resumes and many job placement ads looking for PL/SQL as the primary skill.
With the marriage of the worlds most advanced open
source database and compatibility with the worlds most advanced database
programming language, you can do that.
This is an excerpt
from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.