Re: Oracle Exceptions: Remote DB

mcstock wrote:

> comments inline
>
> "Frank" <fbortel_at_nescape.net> wrote in message
> news:bq7454$nf9$1_at_news3.tilbu1.nb.home.nl...
> | mcstock wrote:
> |
> | [SNIP!]
> |
> | I stand corrected, however it is not an example of the error
> | being passed back to the caller.
> | I had defined (with pragma exception_init), handled and
> | unhandled exceptions mixed up.
> |
> | However: your err_handler has a (commented out) potential
> | room for error:
> |
> | ...
> | else
> | raise_application_error(-20000, ip_msg );
> | -- or
> | -- raise ip_err;
> |
> | Bad Idea: you can only raise User Defined Errors
> | when in the range -20000 .. -20999, unless you
> | specify pragma exception_init for all errors.
>
> oops! ixnay on the raise ip_errnay (untested code always comes back to bite
> you, when i checked my code where i thought i had done that, i immedieately
> saw you point)
>
> yes, you can only raise UDE's, and this must be done with
> RAISE_APPLICATION_ERROR
>
> so the RAISE would only be allowable if a named exception was defined in the
> declare section of the common error handler, and then associated with an
> Oracle error code -- i don't think i'd want to do that for each re-raised
> exception
>
> |
> | CREATE OR REPLACE procedure select_bad_emp
> | is
> | r1 employees%rowtype;
> | begin
> | select *
> | into r1
> | from employees
> | where employee_id = -1;
> | exception
> | when no_data_found then null;
> | when others
> | then raise_application_error(-20002,'Emp Error: '||sqlerrm, false);
> | end ;
> | /
>
>
> thanks for reminding me of the 3rd parameter
>
>
> | CREATE OR REPLACE procedure demo
> | is
> | begin
> | select_bad_emp;
> | -- Now there's something you want to do here, let's display the fact
> | -- we're back in the caller
> | dbms_output.put_line('I am the walrus');
> | exception
> | when others
> | then raise_application_error(-20099,'Does it get Here? '||SQLERRM);
> | end ;
> | /
> | SQL> exec demo
> | I am the walrus
> | PL/SQL procedure successfully completed.
> | SQL>
> |
> | This is correct behaviour! The error was handled: do nothing!
> | We return back to the caller, which resumes normal processing.
> |
> | Now, as for passing it up, let's see what happens if we don't
> | handle the specific error:
> | CREATE OR REPLACE procedure select_bad_emp
> | is
> | r1 employees%rowtype;
> | begin
> | select *
> | into r1
> | from employees
> | where employee_id = -1;
> | exception
> | when dup_val_on_index then
> | null;
> | end;
> | /
> |
> | exec demo
> | ERROR at line 1:
> | ORA-20099: Does it get Here? ORA-01403: no data found
> |
> | So, the UNhandled error is passed back up.
> | We return back to the caller, which starts error processing.
> |
> | The same could have been
> | accomplised with a handled error (back to example 1):
> |
> | CREATE OR REPLACE procedure select_bad_emp
> | is
> | r1 employees%rowtype;
> | begin
> | select *
> | into r1
> | from employees
> | where employee_id = -1;
> | exception
> | when others
> | then null;
> | RAISE;
> | end;
> |
> | SQL> exec demo
> | BEGIN demo; END;
> | *
> | ERROR at line 1:
> | ORA-20099: Does it get Here? ORA-01403: no data found
> |
> | We return back to the caller, which starts error processing, because
> | the error was handled (NULL;), and re-raised.
> | That is when it gets propagated to the caller, even when handled.
> |
> | Hope this makes it clear - to me it does, now
> | --
> | Regards, Frank van Bortel
> |
>
> back to the point, the whole idea was to minimize and generalize exception
> handling code with a common error handler
>
> so thanks for the correction on my offhand suggestion on using RAISE with a
> numeric argument (wrong, wrong, wrong)
>
> but otherwise, the suggested common error handler works as advertised, with
> the basic approach being
>
> begin
> -- some wonderful code here
> exception
> when others
> then some_package.err_handler(sqlcode, sqlerrm);
> end;
>
> including this as a miminum standard this will guarantee that all exceptions
> get handled, but has the drawback of not being able to reraise the orignal
> exception -- the default cause would require a code in the range of -20000
> to -20999
>
> for exceptions that should be handled in a more specific way, something like
> the following may be appropriate:
>
> begin
> -- some wonderful code here
> exception
> when example_1
> then some_package.err_handler(sqlcode, sqlerrm,false);
> raise;
> when example_2, example_3
> then raise
> when others
> then some_package.err_handler(sqlcode, sqlerrm);
> end;
>
> example_1 would get standard error processing such as logging, but the
> 'false' parameter would indicate not to call RAISE_APPLICATION_ERROR --
> allowing the (correctly used) RAISE to reraise the error without
> transforming the message or replacing the sqlcode
>
> example_2 and example_3 simple pass the exception up the stack with no
> special processing -- this is only necessary to prevent these exception from
> being handled by the default (when others), and this is what i was thinking
> could be done in the common handler when i incorrectly suggested using RAISE
> IP_ERR
>
> -- Mark (i stand corrected) Stock
>
>
>
>

You didn't suggest that ;-) - so you were right.

-- 
Regards, Frank van Bortel