CODEWITHSUNDEEP
sqloracle-database
Alexandre Rocha
Alexandre Rocha

Reputation: 101

How do I get the number of characters of a varchar

I'm try to do a trigger that limit the number of the characters when is inserting or updating

CREATE OR REPLACE TRIGGER trigpersonfone
 BEFORE INSERT OR UPDATE OF phone ON PhonePerson
 FOR EACH ROW
   BEGIN
      IF :NEW.phone.LENGTH < 8 THEN 
         DBMS_OUTPUT.put_line('The phone cannot have less then 8 numbers');
      END IF;
  END;
/

I'm using the oracle live and this ide don't show a good error log. Can someone tell me whats is wrong in my sql code? Thanks

Upvotes: 0

Views: 106

Answers (2)

sticky bit
sticky bit

Reputation: 37472

:NEW.phone.LENGTH

doesn't work. This is no object having a LENGTH member. Use the LENGTH() function.

LENGTH(:NEW.phone)

Also DBMS_OUTPUT.PUTLINE() won't abort the DML. Phone numbers with less than 8 characters still can be happily inserted. Maybe you want to use RAISE instead.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Use a check constraint, not a trigger:

alter table PhonePerson add constraint chk_phoneperson_phone check (length(phone) >= 8);

Upvotes: 5

Related Questions