Wednesday, July 11, 2007

How Oracle describes PL/SQL for 11g

It is summertime, right? I hate it when I am so busy that I barely notice summer in Chicago. Aw, that's an overstatement. I've been out. I've ridden my bicycle a couple of times. I've seen people visiting the beach. I even played some basketball with teenage nephews who absolutely ran circles around and over me. [I will, however, state for the record that in one game, I scored 7 of the 15 points. Dang, I was hot.]

And I also did some work on Oracle 11g during the beta program (which officially ended on July 11, with the launch of Oracle latest and greatest version).

So I eagerly downloaded the Oracle Database 11g Application Development PDF to check out its high level commentary regarding PL/SQL and other dev languages available for use against and within the Oracle database.

Under the heading of PL/SQL, this document offered several interesting statements:

"PL/SQL is an imperative 3GL..." An imperative 3GL? I'd never seen or heard of that before. Now, as some of you may know, this ignorance of mine should not be a big surprise. I have little formal training in computer programming (three 101 classes in 1979, to be precise), so lots of the fancy verbiage goes whistling past my head. This one sure did. Imperative? What the heck is that? Thank the heavens for Wikipedia, which tells us that an imperative language is "a programming paradigm that describes computation as statements that change a program state. In much the same way as the imperative mood in natural languages expresses commands to take action, imperative programs are a sequence of commands for the computer to perform." Oh, OK, right. PL/SQL is a procedural language.

"It...supports exactly the same datatypes as SQL." Now this really surprised me. I think I know what Oracle intended to say....that PL/SQL supports all the datatypes of the SQL language, and that is the case. But let's be clear: PL/SQL's set of datatypes is a superset of the SQL datatype domain. For example, PL/SQL supports Booleans, which Oracle's SQL language does not.

"While it is available in other environments, this document focuses on PL/SQL that is stored and compiled in the Oracle database." This sentence my eye because of the word "environments." I know that PL/SQL can be run on the "frontend" inside the Oracle Developer Suite: Forms and Reports (which, interestingly, Oracle refers to as Traditional Oracle Tools). But where else do you use PL/SQL? Perhaps they are talking about Application Express, but that is still PL/SQL code that is stored in the database, I believe. So what are all those other environments?

And, finally, the most delightful aspect of this high level description:

"A best practice used by many Oracle customers is to have client code access Oracle Database only by calling PL/SQL subprograms." Yes! This is one my mantras, these days. Namely, "SQL is bad" aka "All SQL statements are hard-coding and hard-coding is bad" aka "Hide all your SQL behind a procedural (imperative!) API." It's very nice to see this argument for using PL/SQL highlighted in Oracle's description. By the way, for more details on what I am talking about here, check out Practical Best Programming.

I will, in a near future blog on ToadWorld, talk more about the actual new features of PL/SQL for Oracle Database 11g. Now, however, it is time to head to the airport for a weekend in NY to celebrate my mom's 75th birthday.



Gary Myers said...

"this document focuses on PL/SQL that is stored and compiled in the Oracle database"..."Application Express, but that is still PL/SQL code that is stored in the database"

The Apex PL/SQL is stored in the database, but only as rows in a table not with compiled DIANA code. Stuff like native compilation wouldn't apply to it.
There's also anonymous PL/SQL blocks which can be sent from any host language (Java, Perl...) or even SQL*Plus etc.

Michael said...

Steven, some of the olds code I have to support and work on are PL/SQL scripts ran from a shell script in UNIX so that might be another area they were trying reflect in that statement.