Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    4

    Unanswered: Stored Procedure help

    have to write a stored procedure , which will check for the RI Violation in the schema and for the parent-child relationship violation ,it will put the error message in the log table.
    So my log table looks like:
    parent_table
    child_table
    parent_col
    child_col
    insert_date
    update_date


    So , say if there is some entry missing for parent table for an existing value in child table , that info should go to the above log table .


    So please help me regarding this.

    Thanks

    Amy

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >So please help me regarding this.
    There is no help/hope for you.
    If RI really existed, there is NO way to get a child row insert when no parent row exists. Preventing an orphan child row from being inserted is the whole purpose for RI!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2004
    Posts
    4

    RI Violation

    Hi Anacedent
    I know the concept of RI constraints.
    I think my question is kinda unclear .
    All I want to do is : if there is some RI constraint violation, I shuld just insert the corresponding tables and their cols info in the log table

    Thanks

    Amy

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can define an exception corresponding to ORA-02291 (integrity constraint (x.x) violated - parent key not found), and trap that:
    Code:
    PROCEDURE insert_child (...) IS
      e_fk_violated EXCEPTION;
      PRAGMA EXCEPTION_INIT (e_fk_violated, -2291);
    BEGIN
      INSERT INTO child (...) values (...);
    EXCEPTION
      WHEN e_fk_violated THEN
         my_log_procedure(...);
         RAISE;
    END;
    Note the RAISE after the logging of the error - this is mandatory so that the calling program knows that an error occured. Alternatively, you could use RAISE_APPLICATION_ERROR with a bespoke error message.

    Also, if you intend to do this often you may want to define the exception with its PRAGMA once in a package, and then refer to it like this:
    Code:
    PROCEDURE insert_child (...) IS
    BEGIN
      INSERT INTO child (...) values (...);
    EXCEPTION
      WHEN exceptions_pkg.e_fk_violated THEN
         my_log_procedure(...);
         RAISE;
    END;

  5. #5
    Join Date
    Aug 2004
    Posts
    4

    Re: Stored Procedure Help

    HI Andrew

    Thanks a lot for the solution .
    But in my case , I have more than 30 tables in the schema and out of those, 20 have parent-child relationship defined on thm . Now wht should I do in this case to check for these tables on run and then here corres columns and thn finally putting the information for tables and their corresponding col information into the Log table.

    I am kinda newbie in this field thats why I need proper help .

    Your help is appreciated

    Thanks
    With Regards
    Amy Mills

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I would suggest that your log table should be something like:

    create table fk_violation_log
    ( log_id integer primary key
    , child_table varchar2(30)
    , constraint_name varchar2(30)
    , insert_date date
    , value varchar2(100)
    );

    You can get the constraint_name by parsing the value of SQLERRM, and then create a record something like:

    insert into fk_violation_log (log_id, child_table, constraint_name, insert_date, value )
    values (log_seq.nextval, 'EMP', v_constraint_name, SYSDATE, v_deptno );

    You don't need to store the names of the columns, because these can be found in the data dictionary (ALL_CONS_COLUMNS view).

    Sorry I don't have time right now to produce fully working code!

  7. #7
    Join Date
    Aug 2004
    Posts
    4

    Re

    Hi Andrew

    Thanks a lot . I think I will do it now as I already have the log table structure with me .
    I really appreciate your help.

    Regards
    Amy Mills

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •