Re: Oracle Exceptions: Remote DB

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.

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 ;
/

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