Re: Oracle Exceptions: Remote DB

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