This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,693 Users
  • 2,269,776 Discussions
  • 7,916,823 Comments

Discussions

Support any object (or record) type as an exception

Lukas Eder
Lukas Eder St. Gallen, SwitzerlandMember Posts: 128 Bronze Badge
edited Apr 11, 2018 8:29AM in Database Ideas - Ideas

In languages like Java, we can declare exception classes almost like any other class, with the only restriction that it has to be a subtype of java.lang.Throwable:

class MyException extends Throwable {

}

The above can be thrown as follows:

throw new MyException();

We can then have our own custom constructors, which take exception arguments, such as:

class MyException extends Throwable {

    final String message;

    MyException(String message) {

        this.message = message;

    }

}

The above can be thrown as follows:

throw new MyException("Error!");

The message can then be passed from the site throwing the exception to the site catching the exception. The use-case is very obvious. Unfortunately, we cannot do this in PL/SQL. The exception name is all there is. The only workaround is to put some global variable in a package and use that to pass around messages, but this is super unclean.

A much more useful exception type in PL/SQL would allow for using certain OBJECT or RECORD types (or all of them, to be discussed) as exceptions. For instance, using hypothetical syntax:

CREATE TYPE my_exception AS EXCEPTION (

     message VARCHAR2(50)

);

And then

RAISE my_exception('Error!');

Lukas EderPierre Yottiblessed DBASven W.Peter Hraško
6 votes

Active · Last Updated

«1

Comments

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    I don't really see where the problem is. I also do not really understand the proposal.

    It feels like Lukas and Alli are very used to what Java does. And that is where the confusion is coming from.
    Sometimes it makes sense to take a concept that exists in one language also to the other language. Sometimes not.

    What is the business case for changing the way that exceptions work?

    Or to say it differently. What is wrong with

    raise_application_error(-20017,'Error!');

    or alternatively (not syntax checked yet)

    declare   e_myerror exception;  pragma exception_init(e_myError, -20017); begin   ....  raise e_myerror;....exception     when e_myerror then     ....end;
    BEDE
  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown
    edited Apr 12, 2018 10:22AM

    Encapsulation - for exceptions it makes sense that the package that raises an exception to the outside world also makes this exception public. So if the special exception is not handled then yes the exception should be declared in the specification.

    Btw. Oracle does that too in some of the oracle packages (e.g. https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_util.htm#ARPLS73200 ).

    Alternatively you can use the exception number as the "identifier" and declare a second exception in your package B and with the pragma match that exception to the same number.

    But I'm not sure, if this is what Lukas aims for. I had the impression that he wishes for a more detailed exception object.  Which might be interesting.

    William Robertson
  • William Robertson
    William Robertson London, UKMember Posts: 9,579 Bronze Crown

    Agreed, I kind of like the idea of being able to declare exceptions with custom object properties, but I'm still not quite seeing what we could do with them. (Automatically log a message, perhaps?) I had a go at something similar years ago, but I never found a use for it.

    If this was possible, perhaps Oracle could provide a non-final super type that user-defined exception objects would be required to be subtypes of.

  • Jon Theriault
    Jon Theriault Raleigh, NC, USAMember Posts: 16 Blue Ribbon

    I'd like to have exceptions that are UDTs.  I could create one for an ordering system that could provide a user presentable error (Unable to place order.  Please call our 800 number for assistance) but have something useful to anyone who may be calling the program from another system ( sku_number = 12345, reason_code = NO_STOCK) etc.

    The biggest problem we'd have is the number of other languages that can connect to Oracle as a client.  Not all of them have the concept of abstract data types.  The Oracle Call Interface (OCI) itself is C and therefore wouldn't have ADTs.  OCI has a way to handle ADTs but it's awkward and for something as basic as error handling might have a lot of development overhead needed. 

    We'd also be drifting pretty far from Ada which still seems to be handling errors as PL/SQL does.  If Ada ever updated how they handle it that would probably be the right time to revisit exceptions as UDTs.

    Peter Hraško
  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    I'd like to have exceptions that are UDTs.  I could create one for an ordering system that could provide a user presentable error (Unable to place order.  Please call our 800 number for assistance) but have something useful to anyone who may be calling the program from another system ( sku_number = 12345, reason_code = NO_STOCK) etc.

    The biggest problem we'd have is the number of other languages that can connect to Oracle as a client.  Not all of them have the concept of abstract data types.  The Oracle Call Interface (OCI) itself is C and therefore wouldn't have ADTs.  OCI has a way to handle ADTs but it's awkward and for something as basic as error handling might have a lot of development overhead needed. 

    We'd also be drifting pretty far from Ada which still seems to be handling errors as PL/SQL does.  If Ada ever updated how they handle it that would probably be the right time to revisit exceptions as UDTs.

    That is an interesting thought.

    We could define exceptions that have multiple properties like error_number, developer_error_message (for debugging purposes) and user_error_message (for display purposes).

    Peter Hraško
  • Lukas Eder
    Lukas Eder St. Gallen, SwitzerlandMember Posts: 128 Bronze Badge

    I don't really see where the problem is. I also do not really understand the proposal.

    It feels like Lukas and Alli are very used to what Java does. And that is where the confusion is coming from.
    Sometimes it makes sense to take a concept that exists in one language also to the other language. Sometimes not.

    What is the business case for changing the way that exceptions work?

    Or to say it differently. What is wrong with

    raise_application_error(-20017,'Error!');

    or alternatively (not syntax checked yet)

    declare   e_myerror exception;  pragma exception_init(e_myError, -20017); begin   ....  raise e_myerror;....exception     when e_myerror then     ....end;

    You really don't see how this is limiting? I mean, number-based error encoding and stringly-typed error payloads? Doesn't sound very type safe and encourages encoding state in that string, which will be parsed again at the receiver site with tons of source of errors.

    I really want to convey more information, and also encapsulated information from the throwing site to the catching site. Otherwise, exceptions are only useful for local scope, not to be used as part of any public API. After all, the "something happened" kind of error isn't all too useful.

    Peter Hraško
  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    You really don't see how this is limiting? I mean, number-based error encoding and stringly-typed error payloads? Doesn't sound very type safe and encourages encoding state in that string, which will be parsed again at the receiver site with tons of source of errors.

    I really want to convey more information, and also encapsulated information from the throwing site to the catching site. Otherwise, exceptions are only useful for local scope, not to be used as part of any public API. After all, the "something happened" kind of error isn't all too useful.

    Sometimes simple is better. And honestly I currently don't feel too limited. On the contrary. Like I said, we come from different environments, for me all those try{} catch {} blocks seem like a major waste of development effort which can be solved way more elegantly if the Java world would handle exceptions in a similar way to what plsql does. (programmers-hell: Resolving Java Exception Hell )

    Also remember that in plsql it is an error stack that is raised.

    We seem to differ about what should happen after the exception is encountered.

    For me the typical reaction would be => Log the error and stop all further processing.

    Whereas you seem to prefer => add as much info to the error object so that you can later disassemble that error and react on it.

    That sounds like it can be done using normal code modularisation or function calls to me, not like catching (unknown) errors. The public api seems to be a decisive difference. Thats why I was asking for a typical business case.

  • Lukas Eder
    Lukas Eder St. Gallen, SwitzerlandMember Posts: 128 Bronze Badge

    Sometimes simple is better. And honestly I currently don't feel too limited. On the contrary. Like I said, we come from different environments, for me all those try{} catch {} blocks seem like a major waste of development effort which can be solved way more elegantly if the Java world would handle exceptions in a similar way to what plsql does. (programmers-hell: Resolving Java Exception Hell )

    Also remember that in plsql it is an error stack that is raised.

    We seem to differ about what should happen after the exception is encountered.

    For me the typical reaction would be => Log the error and stop all further processing.

    Whereas you seem to prefer => add as much info to the error object so that you can later disassemble that error and react on it.

    That sounds like it can be done using normal code modularisation or function calls to me, not like catching (unknown) errors. The public api seems to be a decisive difference. Thats why I was asking for a typical business case.

    if the Java world would handle exceptions in a similar way to what plsql does

    Ehm, no . I agree that Java doesn't have elegant exceptions. Especially checked exceptions were an experiment well worth doing, but quite controversial. I recently did a poll about them and the results were surprising to me. People like the concept very much, but maybe not the execution. There are other, more sophisticated concepts in other languages, including monads (Try monad, like Haskell, Scala, etc.) or union types (e.g. functions returning ResultType|ExceptionType1|ExceptionType2, like Ceylon, Typescript). I'm pretty sure most people wouldn't want to have PL/SQL's exception system, once they've tried out various approaches. It seems to be just like Java's unchecked exceptions, minus the subtype polymorphism (debatable), minus the encapsulation (very useful).

    Also remember that in plsql it is an error stack that is raised.

    Same as in Java

    For me the typical reaction would be => Log the error and stop all further processing.

    Yes, that's reasonable. That's how Java's unchecked exceptions work.

    Whereas you seem to prefer => add as much info to the error object so that you can later disassemble that error and react on it.

    That's reasonable as well. That's how Java's checked exceptions work. For instance, if you want to transfer funds and you have insufficient funds, then do you simply want to log things and stop processing the request? No. You want to react to it, because it's an "expected" exception, not a system error.

    But as mentioned before, the question here is really whether the latter case should be done using the same mechanism as the former. Most people don't agree on this, an no language has found an acceptable solution yet, although I tend to think that the union type approach is the most promising.

    Now, if PL/SQL supported union types, I would be even more happy, but I guess that's asking too much right now ;-)

    Sven W.
  • Marwim
    Marwim LA / BavariaMember Posts: 3,667 Gold Trophy

    No need for globals to pass a message or parameters -> https://livesql.oracle.com/apex/livesql/file/content_CCDPAOR8IXKYVUT9YHMCZ7W3U.html

    One table to manage all your exceptions, no manual editing of the exception package required.

    The example might even be improved to generate messages based on the user language.

  • Lukas Eder
    Lukas Eder St. Gallen, SwitzerlandMember Posts: 128 Bronze Badge

    No need for globals to pass a message or parameters -> https://livesql.oracle.com/apex/livesql/file/content_CCDPAOR8IXKYVUT9YHMCZ7W3U.html

    One table to manage all your exceptions, no manual editing of the exception package required.

    The example might even be improved to generate messages based on the user language.

    Well, much better than an entire table to give some additional context, why not just put the context right where the exception is declared? :-)