Results 1 to 13 of 13
  1. #1
    Join Date
    May 2002
    Posts
    12

    Unhappy Unanswered: Exception Handling

    I am in the midst of converting our Microsoft stuff to Oracle. I have not used Oracle in three years and am way rusty...

    In Microsoft, when we raise a non fatal error, we can trap for it, return the error number and error message to the front-end application, and decide whether or not to return out of the stored procedure or whether to continue processing. We do this a lot for warning messages to the user. In Oracle, when we do raise_application_error, it seems that I do not have this option. It just returns out. Is there a way I can send warning numbers and warning messages to our front-end application without stopping the stored procedure processing?

    Thanks for your help!

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    i do not fully understand the question. You can handle exceptions in Oracle stored procedure like this :

    begin
    ...
    exception
    when other
    then
    do_something;
    set_result_out_parameter_to_value;
    end;

    In this way, this called procedure is stopped but any other in procedures you call in your form get executed because the error is handled. By setting a resultout parameter you can even see if it succeeded or not.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    May 2002
    Posts
    12
    Thanks for your response.

    Basically, in our Microsoft application (C++/ODBC) that we are trying to convert to Oracle, we have two types of messages: Warnings and Errors.

    For Errors, the error number and error message are returned to the front-end application via RAISERROR, and all database processing stops:
    if @@error != 0
    begin
    RAISERROR...
    return @@error
    end
    print 'Processing continues...' <-- This would never get printed.


    For Warnings, the error number and error message are return to the front-end application via RAISERROR, and database processing does NOT stop:
    if @@error != 0
    begin
    RAISERROR...
    -- no return statement
    end
    print 'Processing continues...' <-- This would get printed.


    How can I do this in Oracle? Sometimes, in our stored procedures, we may have several Warning messages. If I understand your suggestion correctly, we would need several result out parameters in order to capture all of the Warnings inside a single procedure.

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    do you want to have this behaviour using oracle forms or stored procedures in the database ?
    When using stored procedures, what should be an error and what should be a warning ?

    In the code of a procedure you can declare your own exceptions and use this when a check is done to verify something. Then in the exception handler, u can do what ever needs to be done, set the value for one or more outparameters and if it is an error state RAISE and if it's a warning don't state RAISE. the RAISE keyword raises the handled exception again to propagate it to the higher level.

    Hope this is what u mean.
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    May 2002
    Posts
    12
    Hi.

    We are using stored procedures. No Oracle forms. We have user defined errors and warnings. Error numbers within a certain range are considered hard errors and would halt processing. Error numbers within another range are considered warnings and would not halt processing.

    For example, if a user tries to sell something, a stored procedure would call another procedure to check whether or not he owns it. If he does not own it, it would send a warning message via raise but allow the sell to continue.

    We are going to explore the possibility of using the output parameter to flag warnings. Thanks for your suggestion. If you have any other ideas, I'd love to hear them.

  6. #6
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    i have another idea. You are using user defined errors. Suppose you store them in a table with a column idetifying when it must occur and a flag whether or not it is an error or a warning. Write an API (stored function) to retrieve the error and the flag for error or warning.
    That way you can return the flag and in the calling procedure you can trap that error or warning with an exception handler, which raises an error or not.

    Good luck.
    Edwin van Hattem
    OCP DBA / System analyst

  7. #7
    Join Date
    Dec 2003
    Location
    McLean, VA
    Posts
    9
    You could always concatenate multiple error messages into an
    OUT VARCHAR
    parameter.

  8. #8
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Things are slightly different on Oracle with regards to continuing processing following an error.

    Whenever an exception is raised, Oracle will break to the innermost exception handler, and you can nest exception handlers. Most SP, functions etc have a single BEGIN / EXCEPTION / END block.

    You can also define your own exception types to aid readability.

    Viz:

    PHP Code:
    FUNCTION FRED IS
    BEGIN
       
    -- normal function code
    EXCEPTION WHEN OTHERS THEN
       
    -- do something
    END

    Something a little more complex may be...

    PHP Code:
    FUNCTION FRED IS
       eSomethingWrong EXCEPTION
    ;
       
    eSomethingElseWrong EXCEPTION;
    BEGIN
       BEGIN
          SELECT col INTO Value FROM Table WHERE condition
    ;
       
    EXCEPTION 
          WHEN NO_DATA_FOUND THEN 
              Value 
    := 0; -- we choose to continue processing
          WHEN TOO_MANY_ROWS THEN
              RAISE eSomethingWrong
    ;
          
    WHEN OTHERS THEN
              RAISE
    ; -- propagate the exception upwards
       END
    ;

       -- 
    Rest of function
       
    BEGIN
          
    -- another nested exception block ....
       
    END;
     
       -- 
    Rest of function
    EXCEPTION
       WHEN eSomeThingWrong THEN
           
    -- we raised this
       WHEN eSomeElseThingWrong THEN
           
    -- we could have raised this
       WHEN OTHERS THEN
           
    -- 
    END
    So by nesting BEGIN/END blocks with exception handlers you can choose to continue processing or not. You can also use custom exceptions for readability.

    With regards to passing messages back to the user whilst continuing processing, you can either...
    - concatentate them into a string
    - log them to a file
    - send them immediately to the user via DBMS_ALERT

    I prefer DBMS_ALERT as you can also use them for progress bars client side, the client doesn't have to poll an error table etc.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  9. #9
    Join Date
    May 2002
    Posts
    12
    Bill,

    Thanks so much for your reply. That definitely was helpful. Do you know if it's possible to access DBMS_ALERT via ODBC?

  10. #10
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I'm afraid I have very little experience of ODBC so I couldn't say how you would implement it to be honest.

    The DBMS_ALERT package has a fairly detailed description of it's use with some examples - they're worth reading.

    Look at DBMS_ALERT.WAITONE or DBMS_ALERT.WAITANY for receiving the messages client side.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  11. #11
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Billm - nice to see you back in the mix. Seems like it was a long visit to the pub. :-)
    NOTE: Please disregard the label "Senior Member".

  12. #12
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Todd,

    Yes, I'm getting a little less active in my old age :-)

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  13. #13
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Billm - Still planning on checking out your product and site one of these days. Hope that is going well. Keep supporting your local pub.
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •