Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: 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
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    INSTR returns an integer representing the location of the character in the string, so your WHERE clause is wrong and should be:

    WHERE instr(dv_id,'_') > 0;

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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, 'UNDERSCORE NOT ALLOWED IN DV_ID STRING');
    END IF;

    End;
    Last edited by beilstwh; 05-22-06 at 10:15.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In fact:
    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;
    In fact, scrap the trigger altogether in favour of a much more efficient constraint:
    Code:
    alter table dv add constraint NOT_UNDERSCORE_IN_DV_STRING
    check (instr(dv_id,'_') = 0;

Posting Permissions

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