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

On Sat, Jul 31, 2004 at 01:43:25PM -0400, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > Tom Lane wrote:
> >> Can anyone check how well the syntax of plpgsql EXCEPTION, as described
> >> at
> >> http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
> >> agrees with what Oracle does?
>
> > It appears you can have multiple exceptions in a single handler, but
> > that the separator is 'OR' rather than ','.
> > See
> > http://www.cise.ufl.edu/help/database/oracle-docs/appdev.920/a96624/13_elems17.htm
>
> Got it. We can do that.
>
> > The page also gives a link to a list of the predefined exceptions.
>
> Hmm. Not only is that a pretty short list, but many of them don't
> correspond very closely to the errors that Postgres would raise.
> Maybe we should just forget about trying to be compatible with Oracle's
> condition names. That still leaves us with needing to decide what our
> condition names should be, though.
>
> One thing that just occurred to me is that we should accept the category
> names (in errcodes.h, the exceptions with '000' as the last three digits
> of the code) as matching any exception in their category.
>
> > PL/SQL allows users to define and raise their own exceptions too.
>
> Yeah. Our RAISE statement really needs a lot of work, but I think it
> will have to be left for a future release cycle ...
>

Oracle defines very few named exceptions. Instead, the intention is that
you define a name for a numeric exception and use it yourself.

CREATE OR REPLACE PACKAGE symer AS

en_missing_data CONSTANT NUMBER := -20999;
exc_missing_data EXCEPTION;
PRAGMA EXCEPTION_INIT (exc_missing_data, -20999);

END;

--
Jim C. Nasby, Database Consultant jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"