Home » SQL & PL/SQL » SQL & PL/SQL » use defined exception (Oracle 9i)
use defined exception [message #563813] Fri, 17 August 2012 05:53 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

Can any body please tell me when we use user defined exception.
Please provide one business scenario for user defined exception.
Please help me.

Thanks in advance.
Re: use defined exception [message #563833 is a reply to message #563813] Fri, 17 August 2012 06:46 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You use a user defined exception when you want to pass an exception (for example from a procedure stored in the database) to the calling program (for example your user GUI frontend) that isn't a "standard" exception.

For example the frontend could call a "cancel this document" procedure in the DB, and the DB could raise an "the document is already cancelled" exception that is then displayed by the frontend.
Re: use defined exception [message #563834 is a reply to message #563833] Fri, 17 August 2012 06:54 Go to previous messageGo to next message
Littlefoot
Messages: 19610
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's a (stupid) example based on Scott's schema; I want that ALL employees have a manager.
SQL> create or replace trigger trg_biu_emp
  2    before insert or update
  3    on emp
  4    for each row
  5  declare
  6    exc_no_mgr exception;
  7  begin
  8    if :new.mgr is null then
  9       raise exc_no_mgr;
 10    end if;
 11
 12  exception
 13    when exc_no_mgr then
 14      raise_application_error(-20001, 'You have to specify employee''s manager');
 15  end;
 16  /

Trigger created.

SQL> insert into emp (empno, ename) values (99, 'Littlefoot');
insert into emp (empno, ename) values (99, 'Littlefoot')
            *
ERROR at line 1:
ORA-20001: You have to specify employee's manager
ORA-06512: at "SCOTT.TRG_BIU_EMP", line 10
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_EMP'


SQL> select empno, ename from emp where mgr is null;

     EMPNO ENAME
---------- ----------
      7839 KING

SQL> update emp set comm = 1000 where ename = 'KING';
update emp set comm = 1000 where ename = 'KING'
       *
ERROR at line 1:
ORA-20001: You have to specify employee's manager
ORA-06512: at "SCOTT.TRG_BIU_EMP", line 10
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_EMP'


SQL>
Re: use defined exception [message #563836 is a reply to message #563834] Fri, 17 August 2012 07:07 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
I've never found a need for user-defined exceptions. Raise_application_error is far simpler way of sending errors back to a client.
Re: use defined exception [message #563839 is a reply to message #563836] Fri, 17 August 2012 07:24 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I always thought about sending back non-standard-codes with raise_application_error as one way to do "user defined exceptions" quickly.

After all, it raises an exception back to the calling procedures that I have "defined" (at least in the locial sense, if not in the code-sense of defining it in code and using PRAGMA EXCEPTION_INIT to assign it a number.


Re: use defined exception [message #563854 is a reply to message #563813] Fri, 17 August 2012 10:01 Go to previous message
Michel Cadot
Messages: 59127
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 good readings for you:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Previous Topic: ORA-14074: partition bound must collate higher than that of the last partition
Next Topic: Get a specific record in duplicates
Goto Forum:
  


Current Time: Thu Sep 18 11:43:58 CDT 2014

Total time taken to generate the page: 0.12906 seconds