PL/SQL features by release Oracle Database Tips by Donald 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 platforms.

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 though. 

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 breeze. 

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.

 

PL/SQL v6

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 prone.

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.

 

PL/SQL v7

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 primitive!

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 me.

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:

DECLARE

...

BEGIN

....

EXCEPTION

WHEN ....

END;

 

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 PL/SQL.

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:

DriverManager.registerDriver(...

Connection conn = DriverManager.getConnection(...

Statement stmt = conn.createStatement(...

ResultSet rs = stmt.executeQuery( "SELECT ...

while (rs.next())

...

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.

 

PL/SQL v8

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 the database.

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 development world. 

 

PL/SQL v8i

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 additions:

* Drop Column

* Enhanced Partitioning and Sub-partitions

* Analytical Functions in SQL

* XML

* 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 the database. 

Just when you think PL/SQL could not get any better, it did.

 

PL/SQL v9i

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 developer:

* 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 Chapter 5.

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. 

 

PL/SQL v10g

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 finish)

* 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 released.

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 development.

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.