Home » SQL & PL/SQL » SQL & PL/SQL » Exception Handling
Exception Handling [message #270474] Thu, 27 September 2007 02:37 Go to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member

While executing below procedure foreign key relation ship relationship is violated.
How to handle this error?



create or replace procedure delete_dept (v_deptno number) as
dept_exists_exception exception;
begin
delete dept where deptno =v_deptno;
raise dept_exists_exception;
exception
when dept_exists_exception then
dbms_output.put_line('cannot delete department');
end delete_dept;
/
Re: Exception Handling [message #270475 is a reply to message #270474] Thu, 27 September 2007 02:42 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What is the problem? Correctly read your procedure and handle the exception as usual.
Re: Exception Handling [message #270481 is a reply to message #270474] Thu, 27 September 2007 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I advice to:
read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

And to read:
Application Developer's Guide - Fundamentals
PL/SQL User's Guide and Reference
There are sections on exception and how to handle them, just search this word in index.

Regards
Michel
Re: Exception Handling [message #270509 is a reply to message #270474] Thu, 27 September 2007 04:02 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In my opinion, you don't have to do anything about it. Oracle is smart enough to tell you that foreign key constraint was violated. Anyone who is about to delete the department the way you do it (PL/SQL procedure invoked from SQL*Plus or similar client software, because DBMS_OUTPUT's result wouldn't be visible elsewhere) would surely understand the default Oracle message.
Re: Exception Handling [message #270513 is a reply to message #270475] Thu, 27 September 2007 04:13 Go to previous messageGo to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
Make use of these data:
Dept Table:

DeptNo DeptName

10 Engg



Emp Table

Empno Deptno

111 10


By passing "10" as deptno to the procedure will give "child record found" error. I want to raise an user defined error instead of system defined error.



Re: Exception Handling [message #270515 is a reply to message #270513] Thu, 27 September 2007 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too bad you don't want my help.

Regards
Michel
Re: Exception Handling [message #270516 is a reply to message #270513] Thu, 27 September 2007 04:32 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I want to raise an user defined error instead of system defined error.


Why go to the hassle? Please re-read what Michel posted, follow the links and when you have questions, post back, following the posing guidelines
Re: Exception Handling [message #270518 is a reply to message #270515] Thu, 27 September 2007 04:36 Go to previous message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
I will read your links and get back to you. Thanks for your advice.
Previous Topic: Varray Issue in 10.2.0.3
Next Topic: Send a mail with automatic
Goto Forum:
  


Current Time: Sun Dec 04 12:32:51 CST 2016

Total time taken to generate the page: 0.06458 seconds