Re: Anybody have an Oracle PL/SQL reference at hand? - Mailing list pgsql-hackers

From Jim C. Nasby
Subject Re: Anybody have an Oracle PL/SQL reference at hand?
Date
Msg-id 20040803164204.GA87347@decibel.org
Whole thread Raw
In response to Re: Anybody have an Oracle PL/SQL reference at hand?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
The upsides, as I see them:

They use one system for handling all exceptions, user generated or not.

They didn't come up with their own arbitrary names for all the error
codes they have. Naming an exception follows all the namespace rules;
for example, the exception code example I gave was in the 'symer'
package, so you would reference all those execptions as:

EXCEPTION   WHEN symer.some_error_name_I_came_up_with THEN

As for waiting for the code to fail in the field, I'm not sure that
really applies... I only trap exceptions that I know might happen and
have some specific way to deal with, otherwise I let them percolate up
the call stack.

Looking at the PGSQL side, I think using one system for both system and
user defined errors is a good thing. Right now I don't know that PGSQL
has any concept of different user defined error codes, but that's
something that might be usefull. It allows an application to trap your
errors based on an error code, instead of depending on parsing error
text.

Oracle has defined probably 10,000 or more error codes, which is why it
would be impractical for them to come up with a definative name for each
one. PostgreSQL isn't at that stage, so it's not as big an issue. But if
one error handling system is created for both internal and
'user-defined' errors then it would be possible to set aside chunks of
error codes for gborg projects, for example. This would allow them to
document the errors that might occure specific to their code. Oracle
kind of does this in two ways.. they segment the error numbers, and they
also prefix each error with a three leter acronym indicating what
general system the error is from.

As anxious as I am to have error trapping capability in plpgsql, it
might be good to consider possible future uses before deciding on a
system to handle it.

On Mon, Aug 02, 2004 at 09:24:29PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <decibel@decibel.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.
> 
> In any case we use SQLSTATEs not SQLCODEs, so we have no hope of being
> compatible with Oracle at that level.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
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?"


pgsql-hackers by date:

Previous
From: Gaetano Mendola
Date:
Subject: CVS comment
Next
From: Joe Conway
Date:
Subject: Re: pgxs: build infrastructure for extensions v4