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

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> 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:

Agreed, that would be a good thing to have, but I think it's something
we'll have to leave for the next release cycle.

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

Isn't this covered by our provision for catching whole classes? WHEN
DATA_EXCEPTION THEN catches all of class 22, for example.

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

I had originally gone into this with the idea of duplicating their
labels, but I'm now of the opinion that it's a bad idea and we shouldn't
have just partial agreement. That would just lull porters of Oracle
code into thinking they didn't need to review their exception labels.
So, yeah, it would be easy to offer ZERO_DIVIDE as an accepted spelling,
but will that really improve anyone's life?

This leads into another thing I wanted to have some discussion about.
The way the plpgsql exception code is currently written, it doesn't
notice or complain about bad labels. For instance you could write
WHEN FOOBAR THEN and you'd never get a complaint; the corresponding
handler code would simply never be entered. This is probably bad.
How tense do we need to be about detecting bad condition labels?

Any opinions out there about what to do about these points?

> 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 ?

I think referencing Appendix A is the way to do it.

regards, tom lane