-
PL/SQL Exception for Integrity Constraint Violation
Hi,
Does anyone know how to catch the exception for integrity constraint violation in PL/SQL? Is there an Exception for Integrity constraint violation? I couldn't find one from PL/SQL's predefined Exceptions. I tried to use OTHERS but it cannot catch integrity constraint violation.
Thanks for any help.
zm
-
well you can define your own exceptions associated with ORA-XXXXX errors
-
Can you please explain this?
I defined child_exists exception in a procedure and works OK.
declare
child_exists exception;
pragma exception_init( child_exists, -2292 );
begin
delete from TABLE1 where oid = xxxx;
exception
when child_exists then
dbms_output.put_line('Cannot delete BINARY_CONTENT as child exists');
WHEN others THEN
null;
end;
/
But when I do the same thing in a ROW type trigger, I cannot catch child_exists exception.
create or replace trigger DEL_BINARY_CONTENT after delete on ATTRIBUTE_VALUE for each row
declare
child_exists exception;
pragma exception_init( child_exists, -2292 );
begin
delete from TABLE1 where oid = xxxx;
exception
when child_exists then
dbms_output.put_line('Cannot delete BINARY_CONTENT as child exists');
WHEN others THEN
null;
end;
/
I don't know why.
zm
-
trigger will not output line the message...
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Hi
You acn have a work around..
create or replace trigger DEL_BINARY_CONTENT after delete on ATTRIBUTE_VALUE for each row
declare
child_exists exception;
pragma exception_init( child_exists, -2292 );
begin
delete from TABLE1 where oid = xxxx;
exception
when child_exists then
insert into test
values('Cannot delete BINARY_CONTENT as child exists')
WHEN others THEN
null;
end;
then you can query the test table and capture all your exacptions
regards
Hrishy
-
Originally posted by hrishy
Hi
You acn have a work around..
create or replace trigger DEL_BINARY_CONTENT after delete on ATTRIBUTE_VALUE for each row
declare
child_exists exception;
pragma exception_init( child_exists, -2292 );
begin
delete from TABLE1 where oid = xxxx;
exception
when child_exists then
insert into test
values('Cannot delete BINARY_CONTENT as child exists')
WHEN others THEN
null;
end;
then you can query the test table and capture all your exacptions
regards
Hrishy
Hrishy:
U can perform an insert in trigger but it dosent allow u to commit
it throws an error....
Code:
SQL> create or replace trigger test before alter on schema
2 begin
3 insert into scott.test(created_by) values('hell');
4 commit;
5 end;
6 /
Trigger created.
SQL> alter table test1 modify id4 number;
alter table test1 modify id4 number
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at line 3
SQL>
and also the code in ur trigger theres no commit and obivously in the script they wud have set WHENEVER ERROR ROLLBACK command...and aagain u r in fix........
work around wud be to call the PROC in the trigger....and in PROC u can have commit ...
Abhay.
Last edited by abhaysk; 03-14-2003 at 01:30 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Hi Abhay
Yep he can call a proccedure..that does that or i have a package that can write to a os flat file..kind of based on util_file ..that i use for debuging..but i guess doing it in a proceedure in this case makes more sense..
regards
Hrishy
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|