PL/SQL Exception for Integrity Constraint Violation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: PL/SQL Exception for Integrity Constraint Violation

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    109

    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well you can define your own exceptions associated with ORA-XXXXX errors

  3. #3
    Join Date
    Mar 2001
    Posts
    109
    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

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    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

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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 12: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"

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828
    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
  •  



Click Here to Expand Forum to Full Width