0

Hello people,

I declared a field in my table : my_field VARCHAR(20), but I don't want it to contain any number: can you tell me how I can do that using a trigger or using a constraint ?

Thank you in advance for any help

4
Contributors
5
Replies
11
Views
5 Years
Discussion Span
Last Post by mohamedsayed
0

The easiest way is to control this by the application that will be inserting and/or updating the column.
ie. Oracle Forms???

A more difficult way (but not too difficult) is to use a Before Insert trigger.
Loop through your digits (0..9)
and do an InStr() on your input string, checking for your digits.
Fail the trigger if you find any numbers.

Edited by hfx642: n/a

0

The easiest way is to control this by the application that will be inserting and/or updating the column.
ie. Oracle Forms???

A more difficult way (but not too difficult) is to use a Before Insert trigger.
Loop through your digits (0..9)
and do an InStr() on your input string, checking for your digits.
Fail the trigger if you find any numbers.

Thank you very much for your help, I used the trigger as you advised me

0

Or you can put a constraint on the field to restrict the input. In your case, you'd check if the input contains numbers, if yes. then it won't be inserted.

ALTER TABLE TableName ADD CONSTRAINT ConstraintName CHECK(YourField........  )

Instead of the dots up there, you replace them with a function or something that checks if the input is CHAR or is not INT.

0

create or replace trigger t2
before insert on table_name
for each row
declare
v_name varchar2(20);
begin
for i in 0..9 loop
if instr(:new.column_name,i)>0 then
Raise_application_error(-20001,'you can not insert
numeric datatype in varchar datatype');
end if;
end loop;
end;

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.