Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    3

    Question Unanswered: Weird Ora-06502 error

    Hi All,

    I have the following trigger:

    create or replace trigger TRG_ACCOUNT before insert or
    update on ACCOUNT for each row

    BEGIN
    IF INSERTING THEN
    :new.CREATE_USER_ID := USER;
    :new.UPDATE_USER_ID := USER;
    :new.CREATE_DATE := SYSDATE;
    :new.UPDATE_DATE := SYSDATE;
    ELSE
    :new.UPDATE_USER_ID := USER;
    :new.CREATE_USER_ID := ld.create_user_id;
    :new.create_date := ld.create_date;
    :new.update_date := SYSDATE;
    END IF;
    END;

    It works in Oracle 10g environment for about 8-10 hours, then it fails by throwing Ora-06502 error:

    ]ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "TCAMSYS.TRG_ACCOUNT".

    We disabled and reenabled it. We dropped and recreated it. Oracle cannot replicate the error since it was not consistent. We guessed buffer cache or memory issues.

    Has anybody experienced similar issues before? Any help is greatly appreciated.

    Nash

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Where did these (ld.xxx) come from:
    Code:
    ...
    :new.CREATE_USER_ID := ld.create_user_id;
    :new.create_date := ld.create_date;
    ...

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2006
    Posts
    3
    Sorry it should be

    :new.CREATE_USER_ID := old.create_user_id;
    :new.create_date := old.create_date;

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    what is "USER" ??
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Yes, instead of "USER" you should use: SYS_CONTEXT('USERENV', 'SESSION_USER').

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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