Re: Anybody have an Oracle PL/SQL reference at hand?

On Mon, 2 Aug 2004, Tom Lane wrote:

> "Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> > Oracle defines very few named exceptions. Instead, the intention is that
> > you define a name for a numeric exception and use it yourself.
>
> Yeah, I noticed that. It seems a spectacularly bad idea :-(. What
> redeeming social value has it got? AFAICS there are no upsides, only
> downsides: you might get the numeric code wrong, and never know it
> until your code fails in the field; and even if you always get it
> right, having every bit of code invent its own random name for the
> same exception doesn't seem like it does anything for readability or
> maintainability.

I agree with you that forcing users to declare names for SQLCODEs is not
such a great idea. What I do like, however, is the ability to declare your
own exceptions. For example:

DECLARE
invalid_sale EXCEPTION;
BEGIN
...
IF saleid < 0 THEN
RAISE EXCEPTION invalid_sale;
END IF;

...
IF price < '0.00' THEN
RAISE EXCEPTION invalid_sale;
END IF;
...

EXCEPTION
WHEN invalid_sale THEN
...
END;

This is essentially using the exception system for as a goto mechanism,
which usually I wouldn't like except for the problems created when you
have large PL/PgSQL blocks which may encounter the same conditions in
different parts of the block.

This will also be useful because people will want to emulate Oracle PL/SQL
behaviour of generating an exception if is generated when a SELECT INTO
returns no rows. So, they could do:

SELECT INTO myvar ...
IF NOT FOUND THEN
RAISE EXCEPTION NO_DATA_FOUND;
END IF

I also took a look at the Oracle PL/SQL exceptions in 10g. There are only
21 of them people have much finer granularity with PL/PgSQL. The problem
is that I'd imagine that I'd a lot of PL/SQL code captures the exception
VALUE_ERROR (which seems to cover all of SQLSTATE Class 22 it seems). This
would be a special case to the excecption label map.

There is also the STORAGE_ERROR exception which covers
ERRCODE_OUT_OF_MEMORY, ERRCODE_DISK_FULL, ERRCODE_INSUFFICIENT_RESOURCES,
ERRCODE_IO_ERROR and ERRCODE_DATA_CORRUPTED (!!).

There is also INVALID_CURSOR, which basically covers all the cursor
errors.

I have no evidence that these exceptions are in wide use so, maybe its not
a problem at all.

Anyway, I've attached a patch which adds a few more labels for existing
SQLSTATE error codes where there is a one-to-one mapping from PostgreSQL
to Oracle.

Having now added these new exception labels, and given that there are some
errors not supported as exceptions from within PL/PgSQL (success,
warnings, etc), perhaps should generate our own list of error codes within
the PL/PgSQL documentation by looking at plerrcodes.h ?

Just some thoughts...

Gavin