Results 1 to 2 of 2

Thread: Please Help!!!

  1. #1
    Join Date
    Dec 2002
    Posts
    6

    Question Unanswered: Please Help!!!

    Hi all,

    I am new to Oracle and I would very much appreciate any help people may have with the following problem!

    Firstly, I have created the following using Oracle SQLPlus:

    CREATE TYPE employee_type AS OBJECT
    (idnumber NUMBER,
    fname VARCHAR2(15),
    lname VARCHAR2(15));

    CREATE TABLE employee AS TABLE OF employee_type;
    (idnumber PRIMARY KEY);

    This worked fine. However, when I created the following trigger:

    CREATE TRIGGER check_employee_fname
    AFTER INSERT OR UPDATE OF fname ON employee
    FOR EACH ROW
    BEGIN
    IF (:new.fname < 2) THEN
    RAISE_APPLICATION_ERROR(-20000, 'please enter a FULL fname');
    END IF;
    END;

    and tried to enter in the following values:

    INSERT INTO employee(idnumber, fname, lname)
    VALUES(003, 'T', 'Kenna');

    I got the following error message:

    INSERT INTO employee(idnumber, fname, lname)
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at "MYACCOUNT.CHECK_EMPLOYEE_FNAME", line 2
    ORA-04088: error during execution of trigger 'MYACCOUNT.CHECK_EMPLOYEE_FNAME'

    Please, please could someone tell me what I am doing wrong and, what I should do to put it right!!

    Thanks,
    David.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Please Help!!!

    Originally posted by davidcronin
    IF (:new.fname < 2) THEN ...
    fname is VARCHAR2, so this comparison is invalid. It's like saying:

    IF 'Fred' < 2 THEN ...

    I think you meant:

    IF LENGTH(:NEW.fname) < 2 THEN...

Posting Permissions

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