1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Beginner having a problem with trigger

Discussion in 'SQL PL/SQL' started by Martin34857, Oct 28, 2009.

  1. Martin34857

    Martin34857 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hello, I am new to Oracle and SQL in general and I am having problems with this trigger:

    CREATE OR REPLACE TRIGGER myTrigger
    AFTER INSERT OR UPDATE OF aField ON myTable

    DECLARE
    myVariable NUMBER(2);

    BEGIN

    SELECT COUNT(keyColumn) INTO myVariable
    FROM myTable
    WHERE TO_CHAR(myTableDate,'mm') = (SELECT TO_CHAR(SYSDATE,'mm') FROM DUAL);

    IF myVariable > 4 THEN
    RAISE_APPLICATION_ERROR(20605, 'Here is an error.')
    END IF;
    END;
    /

    Every time the trigger is executed, this error happens:
    ORA-06502: PL/SQL: numeric or value error: number precision too large
    ORA-06512: at line 10

    How do I resolve this problem?

    Thank you.
     
  2. Martin34857

    Martin34857 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thank you for your reply.

    Sorry, forgot to mention - my query yields 8, 10 - small numbers - so that is not the problem.

    Furthermore, the problem persists if I have, for example,

    myVariable NUMBER(38);
     
  3. Sikkandar.S.P

    Sikkandar.S.P Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Chennai

    Hi - Have you fixed your problem? Or, Still you have? If yes, Can you Please explain clearly by copy pasting your SQL Plus session?