DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Problem with trigger: ORA-01722: invalid number

Hybrid View

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    Problem with trigger: ORA-01722: invalid number

    Hi,

    my table DV:
    DV_ID VARCHAR2(32) PRIMARY KEY

    DV_ID
    ONE
    XXX
    YYY
    TOM

    Now I'd like to create a trigger that avoid insert the underscore character in dv_id string and raise:
    'NOT UNDERSCORE IN DV_ID STRING'

    I created this trigger:

    CREATE OR REPLACE TRIGGER CHECK_DV BEFORE INSERT OR UPDATE ON DV FOR EACH ROW
    declare

    counter number;

    Begin
    SELECT COUNT(*)
    INTO counter
    FROM DV
    WHERE dv_id=instr(dv_id,'_');

    IF counter > 0 THEN
    RAISE_APPLICATION_ERROR (-20002, 'NOT UNDERSCORE IN DV_ID STRING');
    END IF;

    End;

    but when I try:

    INSERT INTO DV VALUES ('XX_YY');

    I get this error:
    ORA-01722: invalid number
    ORA-06512: at CHECK_DV, line 24
    ORA-04088: error during execution of trigger CHECK_DV

    How can I avoid this error?

    Thanks!

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by raf
    SELECT COUNT(*)
    INTO counter
    FROM DV
    WHERE dv_id=instr(dv_id,'_');
    Your DV_ID column is of type VARCHAR2, while function INSTR() return numeric result. So you are comparing string to number, so no wonder you are reciveing "ORA-01722: invalid number".

    Besides, your whole trigger logic is wrong. Try something like this:
    Code:
    CREATE OR REPLACE TRIGGER CHECK_DV BEFORE INSERT OR UPDATE ON DV FOR EACH ROW
    Begin
      IF INSTR(:NEW.dv_id, '_' > 0 THEN
        RAISE_APPLICATION_ERROR (-20002, 'NOT UNDERSCORE IN DV_ID STRING');
      END IF;
    
    End;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width