Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Unanswered: Trigger Problem - ORA-01403: no data found

    Good Afternoon,

    I am trying to run the following update statement:

    UPDATE inspection_task
    SET threshold =
    (CASE
    WHEN (SYSDATE > due_date + window_size)
    THEN '1'
    WHEN (SYSDATE > due_date + window_size - 21)
    THEN '2'
    WHEN (SYSDATE > due_date - window_size)
    THEN '3'
    WHEN (SYSDATE > due_date - window_size - 21)
    THEN '4'
    ELSE '6'
    END
    )
    WHERE threshold != 5;

    The INSPECTION_TASK table has the following procedure and trigger:

    CREATE OR REPLACE PACKAGE BODY audit_pkg
    AS
    PROCEDURE check_val (
    l_cname IN VARCHAR2,
    l_user IN VARCHAR2,
    l_date IN TIMESTAMP,
    l_iocntlnum IN INTEGER,
    l_itcntlnum IN INTEGER,
    l_itncntlnum IN INTEGER,
    l_itnvernum IN INTEGER,
    l_faccode IN VARCHAR2,
    l_dflcode IN VARCHAR2,
    l_new IN VARCHAR2,
    l_old IN VARCHAR2
    )
    IS
    BEGIN
    IF ( l_new <> l_old
    OR (l_new IS NULL AND l_old IS NOT NULL)
    OR (l_new IS NOT NULL AND l_old IS NULL)
    )
    THEN
    INSERT INTO audit_trail
    (audit_trail_cntl_number, inspection_obj_cntl_number,
    inspection_task_cntl_number, itinerary_cntl_number,
    itinerary_version_number, attribute_name,
    attribute_data_type, old_value, new_value, updated_by,
    updated_date, audit_object_type, fac_id, dfl_code
    )
    VALUES (seq_audit_trail.NEXTVAL, l_iocntlnum,
    l_itcntlnum, l_itncntlnum,
    l_itnvernum, UPPER (l_cname),
    NULL, l_old, l_new, l_user,
    l_date, NULL, l_faccode, l_dflcode
    );
    END IF;
    END;

    PROCEDURE check_val (
    l_cname IN VARCHAR2,
    l_user IN VARCHAR2,
    l_date IN TIMESTAMP,
    l_iocntlnum IN INTEGER,
    l_itcntlnum IN INTEGER,
    l_itncntlnum IN INTEGER,
    l_itnvernum IN INTEGER,
    l_faccode IN VARCHAR2,
    l_dflcode IN VARCHAR2,
    l_new IN TIMESTAMP,
    l_old IN TIMESTAMP
    )
    IS
    BEGIN
    IF ( l_new <> l_old
    OR (l_new IS NULL AND l_old IS NOT NULL)
    OR (l_new IS NOT NULL AND l_old IS NULL)
    )
    THEN
    INSERT INTO audit_trail
    (audit_trail_cntl_number, inspection_obj_cntl_number,
    inspection_task_cntl_number, itinerary_cntl_number,
    itinerary_version_number, attribute_name,
    attribute_data_type, old_value,
    new_value, updated_by,
    updated_date, audit_object_type, fac_id, dfl_code
    )
    VALUES (seq_audit_trail.NEXTVAL, l_iocntlnum,
    l_itcntlnum, l_itncntlnum,
    l_itnvernum, UPPER (l_cname),
    NULL, TO_CHAR (l_old, 'dd-mon-yyyy hh24:mi:ss'),
    TO_CHAR (l_new, 'dd-mon-yyyy hh23:mi:ss'), l_user,
    l_date, NULL, l_faccode, l_dflcode
    );
    END IF;
    END;

    PROCEDURE check_val (
    l_cname IN VARCHAR2,
    l_user IN VARCHAR2,
    l_date IN TIMESTAMP,
    l_iocntlnum IN INTEGER,
    l_itcntlnum IN INTEGER,
    l_itncntlnum IN INTEGER,
    l_itnvernum IN INTEGER,
    l_faccode IN VARCHAR2,
    l_dflcode IN VARCHAR2,
    l_new IN NUMBER,
    l_old IN NUMBER
    )
    IS
    BEGIN
    IF ( l_new <> l_old
    OR (l_new IS NULL AND l_old IS NOT NULL)
    OR (l_new IS NOT NULL AND l_old IS NULL)
    )
    THEN
    INSERT INTO audit_trail
    (audit_trail_cntl_number, inspection_obj_cntl_number,
    inspection_task_cntl_number, itinerary_cntl_number,
    itinerary_version_number, attribute_name,
    attribute_data_type, old_value, new_value, updated_by,
    updated_date, audit_object_type, fac_id, dfl_code
    )
    VALUES (seq_audit_trail.NEXTVAL, l_iocntlnum,
    l_itcntlnum, l_itncntlnum,
    l_itnvernum, UPPER (l_cname),
    NULL, l_old, l_new, l_user,
    l_date, NULL, l_faccode, l_dflcode
    );
    END IF;
    END;
    END audit_pkg;


    CREATE OR REPLACE TRIGGER FOMS.TRG_BUDR_INSPECTION_TASK
    BEFORE DELETE OR UPDATE
    ON FOMS.INSPECTION_TASK
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE

    facCode varchar2(255);
    dflCode varchar2(8);


    begin

    select FACILITY_CODE,
    DFL_CODE
    into facCode,
    dflCode
    from FOMS.INSPECTION_OBJ
    where INSPECTION_OBJ_CNTL_NUMBER = :old.inspection_obj_cntl_number;


    audit_pkg.check_val('INSPECTION_TYPE_CODE',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.INSPECTION_TYPE_CODE,
    :old.INSPECTION_TYPE_CODE);



    audit_pkg.check_val('CHECKS_DUE_CODE',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.CHECKS_DUE_CODE,
    :old.CHECKS_DUE_CODE);


    audit_pkg.check_val('ANNUAL_IND',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.ANNUAL_IND,
    :old.ANNUAL_IND);


    audit_pkg.check_val('PRIORITY_CODE',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.PRIORITY_CODE,
    :old.PRIORITY_CODE);


    audit_pkg.check_val('REIMBURSEMENT_NUMBER',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.REIMBURSEMENT_NUMBER,
    :old.REIMBURSEMENT_NUMBER);


    audit_pkg.check_val('DUE_DATE_CALC_STEP',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.DUE_DATE_CALC_STEP,
    :old.DUE_DATE_CALC_STEP);


    audit_pkg.check_val('DUE_DATE',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.DUE_DATE,
    :old.DUE_DATE);


    audit_pkg.check_val('WINDOW_SIZE',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.WINDOW_SIZE,
    :old.WINDOW_SIZE);


    audit_pkg.check_val('WILL_CALL_FLAG',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.WILL_CALL_FLAG,
    :old.WILL_CALL_FLAG);


    audit_pkg.check_val('AWAITING_PROCEDURES_IND',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.AWAITING_PROCEDURES_IND,
    :old.AWAITING_PROCEDURES_IND);


    audit_pkg.check_val('REMARKS',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.REMARKS,
    :old.REMARKS);


    audit_pkg.check_val('STATUS',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.STATUS,
    :old.STATUS);


    I keep receiving the following error:

    ORA-01403: no data found|ORA-06512: at "FOMS.TRG_BUDR_INSPECTION_TASK",line9|ORA-04088: error during execution of trigger 'FOMS.TRG_BUDR_INSPECTION_TASK
    Has anyone ever seen this error or know what may be causing it? Thank you so much for any help that you may be able to offer!

    Sincerely,

    Chris Johnson
    Oklahoma City, OK
    Last edited by johnsonch; 12-08-05 at 15:34.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I fail to see any trigger here.

  3. #3
    Join Date
    Dec 2005
    Posts
    3

    That would help, huh!

    Been a long day already...sorry about that! Here's the trigger!

    CREATE OR REPLACE TRIGGER FOMS.TRG_BUDR_INSPECTION_TASK
    BEFORE DELETE OR UPDATE
    ON FOMS.INSPECTION_TASK
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE

    facCode varchar2(255);
    dflCode varchar2(8);


    begin

    select FACILITY_CODE,
    DFL_CODE
    into facCode,
    dflCode
    from FOMS.INSPECTION_OBJ
    where INSPECTION_OBJ_CNTL_NUMBER = :old.inspection_obj_cntl_number;


    audit_pkg.check_val('INSPECTION_TYPE_CODE',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.INSPECTION_TYPE_CODE,
    :old.INSPECTION_TYPE_CODE);



    audit_pkg.check_val('CHECKS_DUE_CODE',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.CHECKS_DUE_CODE,
    :old.CHECKS_DUE_CODE);


    audit_pkg.check_val('ANNUAL_IND',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.ANNUAL_IND,
    :old.ANNUAL_IND);


    audit_pkg.check_val('PRIORITY_CODE',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.PRIORITY_CODE,
    :old.PRIORITY_CODE);


    audit_pkg.check_val('REIMBURSEMENT_NUMBER',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.REIMBURSEMENT_NUMBER,
    :old.REIMBURSEMENT_NUMBER);


    audit_pkg.check_val('DUE_DATE_CALC_STEP',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.DUE_DATE_CALC_STEP,
    :old.DUE_DATE_CALC_STEP);


    audit_pkg.check_val('DUE_DATE',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.DUE_DATE,
    :old.DUE_DATE);


    audit_pkg.check_val('WINDOW_SIZE',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.WINDOW_SIZE,
    :old.WINDOW_SIZE);


    audit_pkg.check_val('WILL_CALL_FLAG',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.WILL_CALL_FLAG,
    :old.WILL_CALL_FLAG);


    audit_pkg.check_val('AWAITING_PROCEDURES_IND',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.AWAITING_PROCEDURES_IND,
    :old.AWAITING_PROCEDURES_IND);


    audit_pkg.check_val('REMARKS',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.REMARKS,
    :old.REMARKS);


    audit_pkg.check_val('STATUS',
    :new.LAST_UPDATED_BY,
    :new.LAST_UPDATED_DATE,
    :old.INSPECTION_OBJ_CNTL_NUMBER,
    :old.INSPECTION_TASK_CNTL_NUMBER,
    null,
    null,
    facCode,
    dflCode,
    :new.STATUS,
    :old.STATUS);

  4. #4
    Join Date
    Jan 2004
    Posts
    370
    Quote Originally Posted by johnsonch
    select FACILITY_CODE,
    DFL_CODE
    into facCode,
    dflCode
    from FOMS.INSPECTION_OBJ
    where INSPECTION_OBJ_CNTL_NUMBER = ld.inspection_obj_cntl_number;

    The select into ... statement will result in an ORA-1403 if it does not return any rows.


    .

  5. #5
    Join Date
    Dec 2005
    Posts
    3
    That was the problem! I broke it out into two select into statements, wrapped them in exception handling, and it solved it. It now looks like this:

    Begin
    select FACILITY_CODE
    into facCode
    from FOMS.INSPECTION_OBJ
    where INSPECTION_OBJ_CNTL_NUMBER = ld.inspection_obj_cntl_number;
    exception when others then
    facCode := NULL;
    End;

    Begin
    select DFL_CODE
    into dflCode
    from FOMS.INSPECTION_OBJ
    where INSPECTION_OBJ_CNTL_NUMBER = ld.inspection_obj_cntl_number;
    exception when others then
    dflCode := NULL;
    End;


    Thanks so much!

    Chris

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Another way to solve NO-DATA-FOUND without writing exception handlers (not that I'd recommend it!, but sometimes - especially when you're in a hurry and for testing purposes - this might be helpful) could be use of any aggregate function, such as:
    Code:
    SELECT MAX (facility_code), MAX (dfl_code)
      INTO facCode, dflCode
      FROM foms.inspection_obj
     WHERE inspection_obj_cntl_number = :OLD.inspection_obj_cntl_number;

Posting Permissions

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