It Was A Busy September for
Oracle, PL/SQL and yours truly
September 2005 was a very
busy month when it comes to PL/SQL,
and my life with PL/SQL, and things haven't been calming down in
October. Since September 3 2005, all because a sliver of humanity just
can't get enough of PL/SQL, I have visited the following cities:
Stockholm, Copenhagen, Oslo, Amsterdam, London, Dublin, Manchester,
Edinburgh, Norwood Massachusetts, San Francisco, New York City, Dallas.
Well, it's my busy time of year! London was especially fun; it seems to
be full of enthused PL/SQL programmers. Developers always turn out in
large numbers for PL/SQL-related activities in London, and this year's
seminar was no exception, with 140 or so programmers
joining me at the extremely professional and comfortable Cavendish
Conference Center near Oxford Circus (I mean no disrespect for the
programmers in the other cities; you are all absolutely fabulous. By
the way, I have found that usually half the students in my classes in
Dublin are women, which is generally not the profile in most other
cities in which I teach PL/SQL. I believe that says something very
positive about Dublin and Ireland).
But enough about me and
my travels. What else has been going
on in the world of PL/SQL?
Let's see, way back in
the first half of September, Oracle
held its umpteenth annual Oracle Open World conference at Moscone
Center in San Francisco. Wow, that was a fairly overwhelming
experience! This conference now combines the user communities of the
core Oracle products (from database to applications), J.D. Edwards,
Peoplesoft, and more -- and it was massive.
I believe Oracle was talking about 25,000 people in attendance. In
between sessions, and at the start and end of the day, the blocks
around Moscone were absolutely packed with crowds of people migrating
from hotel to Starbucks to Exhibit Hall.
With ID
badges swinging from our necks and identical conference bags slung over
our backs, we
must have made for something of a strange sight to the dozens of
panhandlers along the route and the San Franciscans going about their
normal lives.
There were relatively few
PL/SQL sessions at OOW; sometimes I
feel like Oracle takes us a bit for granted. Those that were scheduled,
however,
were full to bursting. From the standpoint of PL/SQL, by far
the biggest news coming
out of OOW was the release of the fourth edition of my Oracle
PL/SQL
Programming. Oh all right, maybe OPP4 wasn't really,
the top news story at OOW, though it was very exciting for me
to see the book in the bookstore, almost exactly ten years after the
first edition was released at International Oracle User Week in
Philadelphia in 1995.
No, without any doubt,
the TRULY most interesting PL/SQL-related development at OOW 2005 was
Oracle's announcement of Project Raptor.
Project Raptor
"Project Raptor" is the
codename for a new development effort
by Oracle to produce a PL/SQL editor or IDE (integrated development
environment). As you are probably aware, this is already a very crowded
market, dominated at the "top" (in terms of functionality and price) by
Toad and SQL*Navigator (both from Quest Software) and then followed by
a substantial field of competitors, from the very popular PL/SQL
Developer to an
ever-lengthening list of similarly inexpensive competitors, including
DreamCoder,
SQL Detective, SQL Insight, and so on.
Disclosure: I am an
employee of Quest Software (a Senior Technology Advisor, in fact!),
though my comments in this newsletter reflect only my personal opinion
and not
the position of Quest
Software.
It has been something of
an odd situatoin for years now that Oracle
itself
didn't offer an editor for its own programming language. Of course,
there used
to be such a
product. It was called Procedure Builder (ah, those marketing
geniuses!) and was available both stand-alone and as the editor within
the
Oracle Developer toolset (Oracle Forms, Oracle Reports, etc.). Oracle
stopped offering Procedure Builder as a separate product years ago.
Recently, it shifted its attention to adding PL/SQL functionality to
JDeveloper.
Now they are giving
JDeveloper away for free, and have decided to build
a new, stand-alone PL/SQL IDE
, based on the JDeveloper framework. This is now being called Project
Raptor, though in the demonstrations they ran, the title bar of the
tool displayed the name "Database Developer". But, fine, for this
newsletter, I will stick with "Raptor".
From all indications,
including several conversations with the
lead developer, Kris Rice, Raptor will be a robust editing environment,
offering integrated source code debugging, schema browser, code
formatting, lots of nice interfaces to underlying data dictionary
views: in short, precisely the minimum feature set that we have
come to expect from any PL/SQL editor. We probably won't see anything
that is available to the public until 2006, perhaps late spring. They
plan to issue frequent releases (every few months), in order to offer
a steadily richer toolset to the users and maintain momentum around the
tool.
Raptor is especially
important to those
within Oracle Corporation. In the time I spent with the Oracle PL/SQL
development team members at OOW, including Chris Racicot, development
manager,
it was clear that they were very excited about Oracle offering its own
PL/SQL editor. They talked easily about "the language" (PL/SQL) and
"the
product" (Raptor), as if this were a natural and long-standing
combination. I can certainly understand their perspective. It must have
been both a bit bewildering and tiny bit more embarrassing for the
PL/SQL development team to not
have a tool over which Oracle exercised control, one that could
showcase
all the latest and greatest features of PL/SQL.
It is less clear how much
"made by Oracle" will mean to those
outside of Oracle. The company, after all, does not have a very good
track record of building PL/SQL programming tools. I expect, though,
that Raptor will demonstrate the truth (however coincidental) of that
old saying "third time's the charm." The Oracle database server now
offers a wide range of powerful, mature functionality (for example, a
stable debugger). Plus, the Raptor team is building the tool on a
proven, sophisticated framework. I expect that they will make rapid
progress and that whatever Raptor turns into (they sure aren't going to
stick with that
name) will be
a very usable and useful tool.
Having said that, Oracle
has a tremendous
amount of catching up to
do, when it comes to providing a comprehensive set of features and
polished interface that even pretends to approach what a tool like Toad
offers. It is very unclear to me how much Raptor will impact the sales
of existing tools. Toad continues to be a remarkable juggernaut,
adding thousands of new users each year. SQL Navigator, the other
PL/SQL IDE from Quest, also racks up impressive sales, and with a much
lower profile marketing effort. These tools cost hundreds of dollars
per developer (and higher); companies that choose the Quest tools are
already saying "no thanks" to low-cost alternatives. What
would make them evaluate Oracle's product in a qualitatively different
way?
It seems to me that the
low-end PL/SQL IDEs, headlined by
PL/SQL Developer, will be much more threatened by Raptor in the years
to come. If I had built a PL/SQL IDE that sold for $49, I would have to
be asking myself why anyone would bother with my tool, when they could
simply download Raptor from the Oracle Technology Network and have an
editor that handles most every basic need they will encounter as they
write, compile and debug their PL/SQL programs.
Interesting days ahead
for the PL/SQL developer community and
tools vendors....
This just in: Oracle
buys Innobase!
Oracle announced on
October 7 that it had acquired Innobase, a
developer of database technology distributed with the MySQL database.
"Oracle has long been a
supporter of open source software such
as Linux
and Apache," said Charles Rozwat, Oracle's Executive Vice President in
charge of Database and Middleware Technology. "Innobase is an
innovative small company that develops open source database technology.
Oracle intends to continue developing the InnoDB technology and expand
our commitment to open source software. Oracle has already developed
and contributed an open source clustered file system to Linux. We
expect to make additional contributions in the future."
Click here
for the full report on Oracle's website.
For the official MySQL,
response: http://www.mysql.com/news-and-events/news/article_968.html
Will it hurt or help
MySQL? Very hard to say, right now. It's
hard to believe that the Innobase folks have database technology
experience not already found in the halls of Oracle, but it certainly
is possible. Rumor has it that MySQL has already been working on their
own code to replicate InnoDB functionality; if so, the impact should be
minimal. Beyond that, though, InnoDB remains a GPL product (GNU General
Public
License), so even if Oracle decides to stop development of the product,
others can pick up where InnoDB left things and enhance it themselves.
By the way, speaking of
MySQL, I am branching out myself, by
co-authoring (as, without question, the junior
author)
with Guy Harrison, of Oracle
SQL High Performance Tuning
fame, on a book about the new MySQL 5 stored programming language. It
is very similar to, but
not nearly as robust as, PL/SQL. It is sure to be used, however, by
many,
many MySQL developers in the coming years.
It's too soon to give you
a link to information about the
book...we
are still sorting out issues like "what should the title be?". But do
check future OPP/News issues or
the O'Reilly media website for an announcement about the book.
Hey, don't forget!
>> OOW
is
great,
but it covers all Oracle
technologies and
actually pays relatively little attention to PL/SQL. I invite you to
join
me on November 2-3, 2005 for a two-day PL/SQL-only
conference: Oracle
PL/SQL
Programming 2005. It's coming up
fast, so I encourage you to
register right away!
>> Help
influence the future
of the PL/SQL language by visiting and contributing to the 21st
Century PL/SQL Idea Collector.
>> Check
out Qnxo, www.qnxo.com,
which you can use to quickly generate a PL/SQL code library customized
to your own database objects. We just released version 1.3, which
includes Codebar,
a very
cool "coding toolbar" that you can use from your favorite editor to
instantly access your Qnxo-generated code library. Qnxo and Codebar
will totally transform the way you
write PL/SQL applications! And we are working hard now on adding
groundbreaking unit testing features: you will be able to specify the
test cases through the Qnxo Unit Tester (Qut) interface, and then
generate and run utPLSQL-compatible unit test packages.
Tip of the Month: Name
those exceptions!
When a SELECT INTO does
not return at least one row, Oracle
raises the NO_DATA_FOUND exception. Most PL/SQL developers probably
think this exception is some sort of built-in, reserved word exception
in the PL/SQL language. It is not. rather, it is simply one of a
relatively small number of exceptions that are defined in the STANDARD
package. This package is one of two default
packages in PL/SQL (the other being DBMS_STANDARD), which means that
when you reference elements in either of these packages, you don't have
to qualify them with the name of their package (though you can if you
want to). Consider the following block of code:
DECLARE
l_counter PLS_INTEGER;
l_counter2 STANDARD.PLS_INTEGER;
VARCHAR2 NUMBER;
BEGIN
SELECT 1
INTO l_counter
FROM SYS.DUAL
WHERE 1 = 2;
DBMS_STANDARD.COMMIT;
EXCEPTION
WHEN STANDARD.NO_DATA_FOUND
THEN
DBMS_STANDARD.raise_application_error (-20000, 'Yikes!');
END;>
This code will compile,
which might come as a surprise to you.
Conclusions to draw from this example:
-
Many of the elements
you consider to be reserved words in
PL/SQL are simply datatypes, structures or programs defined in the
STANDARD package. You can use those names in your own code (notice my
declaration of a
variable named "varchar2"), but I certainly suggest that you not
do that. The resulting code is
just too, too confusing.
-
You can qualify
references to elements in the default packages with the package name,
but again I would recommend you not do this, to avoid extra clutter in
your code.
Now, I strongly encourage
you to take some time to study the
STANDARD package (found in the Rdbms/Admin subdirectory under your
Oracle Home directory, now defined in two separate files:
stdspec.sql and stdbody.sql). You can learn an awful lot about the
PL/SQL language. For one thing, you will see that all of the system
exceptions like NO_DATA_FOUND are created using the EXCEPTION_INIT
pragma. Here is the statement for NO_DATA_FOUND:
NO_DATA_FOUND EXCEPTION;
PRAGMA EXCEPTION_INIT (NO_DATA_FOUND, 100);
And here you thought
Oracle errors were negative! Well,
NO_DATA_FOUND is an oddity in the Oracle world: it has two
error numbers associated with
it, 100 (carried over from the earliest days of compliance with the
ANSI standard) and -1403. The EXCEPTION_INIT pragma references 100, but
-1403 comes along for the ride. [Actually, for whatever reason, Oracle
will not allow you to use -1403 in an EXCEPTION_INIT pragma!]
Thus, you could replace
this exception section:
EXCEPTION
WHEN NO_DATA_FOUND
THEN
...
with this one:
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -1403
THEN
...
but you certainly
wouldn't want
to do this. The code is much less readable.
Well, that same rule (namely, it is better to
reference exceptions by name, rather than number)
applies to Oracle exceptions for which
there are no associated names. In this case, you will need to declare
your own named exception and
use the EXCEPTION_INIT pragma yourself. Let's take a look at an example.
Hopefully you are all
aware of the FORALL statement, which
allows you to perform multiple DML statements much
more efficiently than through
row-by-row processing. If FORALL is news to you, then please go
out and buy my book, Oracle
PL/SQL Programming, and dive
into chapter 14. Or visit Oracle's
on-line doc center at:
PL/SQL
User Guide and Reference: FORALL
Now, in Oracle9i Database
Release 2, Oracle added the SAVE
EXCEPTIONS clause to FORALL. If you use this clause, then Oracle will
execute each DML statement associated with the specified rows of the
driving collection. If it encounters one or more errors, it saves those
errors in a pseudo collection named SQL%BULK_EXCEPTIONS. When it has
completed all the DML processing and at least one error has occurred,
Oracle will then raise the generic "FORALL failure" error, which has
been assigned the number -24381.
So you could
write
code like this when working with FORALL and SAVE EXCEPTIONS:
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -24381
THEN
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
... process the errors ...
END LOOP;
END IF;
END;
This would, however, be
a bad idea. Do you think you will
remember what -24381 refers to? I am pretty certain I will, but that is
because I am an obsessive individual when it comes to PL/SQL. You
surely have better things to do with your
mind. Putting aside the issue of remembering,
code like "SQLCODE = -24381" is also downright bewildering and
intimidating. Developers coming across this code in later years will
feel terribly inadequate because the code clearly expects
them to know what that means, but they
have no idea. So this code creates bad vibes, thereby reducing the
quality of life of programmers, leading to more bugs, and possibly the
collapse of civilization as we know it.
See? Programmers can be super-heroes and save our
world, too! Just
don't hard code error numbers in the exception seciton. Well, yes, it
would certainly be
nice to reference a named exception in
that part of this program, but Oracle did not choose to associate a
name
with -24381. That's all right, though. Don't give up. Just because
Oracle didn't see fit to do this doesn't mean that you have to write
nasty code. Instead, you can give your own name
to this exception.
Consider the following procedure (truncated implementation shown to
allow us to focus on the key elements for the tip at hand):
PROCEDURE upd_for_dept (
dept_in IN employee.department_id%TYPE
, newsal IN employee.salary%TYPE
)
IS
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
...
BEGIN
...
FORALL indx IN employees.FIRST .. employees.LAST SAVE EXCEPTIONS
INSERT INTO employee_history
(employee_id, salary, hire_date
)
VALUES (employees (indx), salaries (indx), hire_dates (indx)
);
EXCEPTION
WHEN bulk_errors
THEN
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
log_error (SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
END LOOP;
END upd_for_dept;
Notice that at the very
beginning of my procedure I declare an
exception and associate it with -24381. Then in my exception section, I
reference the exception by name. The result is much more readable code,
which will make programmers feel better about themselves and also
preserve our quality of life.
Bottom line: give names to any Oracle errors which
you need to trap
and handle in your code. Generally avoid placing obscure, scary,
hard-coded numbers in any part of your application.
|
