Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Location
    San Mateo, Rizal, Philippines
    Posts
    18

    Unanswered: Problem on Trigger

    Gurus,

    Please help me on this. An error occured on our application regardng trigger. See below.

    [Oracle][ODBC][Ora]ORA-06502: PL/SQL: numeric or value error
    ORA-06512 AT "SCI_DEV.CCS_ACCO_BRI", line 25
    ORA-04088: error during execution of trigger 'SCI_DEV.CCS_ACCO_BRI'
    ORA-06512 at 'SCI_DEV.CLS_SO_BYSOAC", line 763
    ORA-06512 at 'SCI_DEV.CLS_SO_BYSOAC", line 1909
    ORA-06510: PL/SQL unhandled user-defined exception
    ORA-06512 at 'SCI_DEV.CLSSO_BYSOAC", line 4
    ORA-06512 at line 1
    -clsSOBYSoac

    ----------------------------------
    The line 25 falls on the new.ACCO_CHECK_DIGIT computation.
    I manually computes the check digit and so far.
    ACCO_CHECK_DIGIT = 10. But I still don't know what causes the eror.
    We don't have the program only the application and database to check.
    On the user side, this error occures when saving the Sales Order thus creating an account number.

    Can you please give me an idea on how to check the problem or solve this problem?


    Me-an
    --------------------------------
    THIS IS A TRIGGER program
    DECLARE
    BEGIN
    declare
    cursor c_dual
    is
    select sysdate
    , username
    , acco_seq.nextval
    , to_char(sysdate,'yyyy')||lpad(acco_no_seq.nextval, 11,'0')
    from dual;
    l_sysdate date;
    l_user varchar2(30);
    l_id number;
    l_acco varchar2(15);
    begin
    open c_dual;
    fetch c_dual
    into l_sysdate
    , l_user
    , l_id
    , l_acco;
    close c_dual;
    :new.ACCO_ID:= l_id;
    :new.ACCO_NO:= l_acco;
    :new.ACCO_CHECK_DIGIT := mod ((to_number(substr(l_acco,1,1)) * 11) +
    (to_number(substr(l_acco,2,1)) * 10) +
    (to_number(substr(l_acco,3,1)) * 9) +
    (to_number(substr(l_acco,4,1)) * 8) +
    (to_number(substr(l_acco,5,1)) * 7) +
    (to_number(substr(l_acco,6,1)) * 6) +
    (to_number(substr(l_acco,7,1)) * 5) +
    (to_number(substr(l_acco,8,1)) * 4) +
    (to_number(substr(l_acco,9,1)) * 3) +
    (to_number(substr(l_acco,10,1)) * 2), 11);
    if :new.ACCO_CHECK_DIGIT = 0 or :new.ACCO_CHECK_DIGIT = 10 then
    :new.ACCO_CHECK_DIGIT := 1;
    elsif :new.ACCO_CHECK_DIGIT = 1 then
    :new.ACCO_CHECK_DIGIT := 0;
    else
    :new.ACCO_CHECK_DIGIT := 11 - :new.ACCO_CHECK_DIGIT;
    end if;
    :new.CREATED_DATE := l_sysdate;
    :new.USER_CREATED := l_user;
    :new.MODIFIED_DATE := l_sysdate;
    :new.USER_MODIFIED := l_user;
    end;
    END;

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    I think You cannot assigh value (variable) to :new.ACCO_ID:= l_id;

    I guess that is giving you an error.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hing is correct!
    U can not assign values to :new
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Feb 2004
    Location
    Eternity
    Posts
    31
    I donot think thats the real problem as you can do such assignment in Forms designer.(Thats what I guess is being used)
    And yeah I executed the same trigger with some minor changes to run in my local env and it works..
    So try putting some debug messages(U r using Forms right??) and trace the value in stages.

    Thanx
    Vijay

  5. #5
    Join Date
    Dec 2003
    Location
    San Mateo, Rizal, Philippines
    Posts
    18

    Re: Problem on Trigger

    There is no error before. It started just on Jan 2004.


    Originally posted by mean2229
    Gurus,

    Please help me on this. An error occured on our application regardng trigger. See below.

    [Oracle][ODBC][Ora]ORA-06502: PL/SQL: numeric or value error
    ORA-06512 AT "SCI_DEV.CCS_ACCO_BRI", line 25
    ORA-04088: error during execution of trigger 'SCI_DEV.CCS_ACCO_BRI'
    ORA-06512 at 'SCI_DEV.CLS_SO_BYSOAC", line 763
    ORA-06512 at 'SCI_DEV.CLS_SO_BYSOAC", line 1909
    ORA-06510: PL/SQL unhandled user-defined exception
    ORA-06512 at 'SCI_DEV.CLSSO_BYSOAC", line 4
    ORA-06512 at line 1
    -clsSOBYSoac

    ----------------------------------
    The line 25 falls on the new.ACCO_CHECK_DIGIT computation.
    I manually computes the check digit and so far.
    ACCO_CHECK_DIGIT = 10. But I still don't know what causes the eror.
    We don't have the program only the application and database to check.
    On the user side, this error occures when saving the Sales Order thus creating an account number.

    Can you please give me an idea on how to check the problem or solve this problem?


    Me-an
    --------------------------------
    THIS IS A TRIGGER program
    DECLARE
    BEGIN
    declare
    cursor c_dual
    is
    select sysdate
    , username
    , acco_seq.nextval
    , to_char(sysdate,'yyyy')||lpad(acco_no_seq.nextval, 11,'0')
    from dual;
    l_sysdate date;
    l_user varchar2(30);
    l_id number;
    l_acco varchar2(15);
    begin
    open c_dual;
    fetch c_dual
    into l_sysdate
    , l_user
    , l_id
    , l_acco;
    close c_dual;
    :new.ACCO_ID:= l_id;
    :new.ACCO_NO:= l_acco;
    :new.ACCO_CHECK_DIGIT := mod ((to_number(substr(l_acco,1,1)) * 11) +
    (to_number(substr(l_acco,2,1)) * 10) +
    (to_number(substr(l_acco,3,1)) * 9) +
    (to_number(substr(l_acco,4,1)) * 8) +
    (to_number(substr(l_acco,5,1)) * 7) +
    (to_number(substr(l_acco,6,1)) * 6) +
    (to_number(substr(l_acco,7,1)) * 5) +
    (to_number(substr(l_acco,8,1)) * 4) +
    (to_number(substr(l_acco,9,1)) * 3) +
    (to_number(substr(l_acco,10,1)) * 2), 11);
    if :new.ACCO_CHECK_DIGIT = 0 or :new.ACCO_CHECK_DIGIT = 10 then
    :new.ACCO_CHECK_DIGIT := 1;
    elsif :new.ACCO_CHECK_DIGIT = 1 then
    :new.ACCO_CHECK_DIGIT := 0;
    else
    :new.ACCO_CHECK_DIGIT := 11 - :new.ACCO_CHECK_DIGIT;
    end if;
    :new.CREATED_DATE := l_sysdate;
    :new.USER_CREATED := l_user;
    :new.MODIFIED_DATE := l_sysdate;
    :new.USER_MODIFIED := l_user;
    end;
    END;

  6. #6
    Join Date
    Dec 2003
    Location
    San Mateo, Rizal, Philippines
    Posts
    18

    Error in Trigger

    No debug will be used since we bought the applications since 1999. And we have no more support because the company has closed down. Program calling the trigger I don't know where.

    I get the trigger program from oracle enterprise manager based on the error.

    There is no error encountered last year. It started only this year.

    Me-an

  7. #7
    Join Date
    Feb 2004
    Location
    Eternity
    Posts
    31
    Try checking the currval of the sequence acco_no_seq.

  8. #8
    Join Date
    Dec 2003
    Location
    San Mateo, Rizal, Philippines
    Posts
    18

    Problem in Trigger

    I have done that also.
    The current value of acco_no_seq and acco_seq = 14848.


    Originally posted by spvijay
    Try checking the currval of the sequence acco_no_seq.

Posting Permissions

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