Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Integrity Constraint
> Hi,
>
> I need to check that a valid entry is being made to a field in a table.
> I am running Oracle 8.0.4
>
> The field is a varchar2(11) and the data should be exactly 10 characters
> long, the first 9 being numeric and the last character may be numeric or a
> character.
> Unfortunately, checking this at the client application is not an option.
> The checking needs to be done by the database.
>
> Could anyone give me an idea of how to achieve this please.
>
You have to use a trigger on that table which is called by insert and update (at least of this column)
try this:
create or replace trigger trg_on_table
before
insert or
update of <column you want to inspect: col>
on <table you want to inspect: tab>
for each row
declare
i integer;
ch integer;
begin
if length(:new.col) <> 10 then
raise_apllication_error (-20000, 'It must to be 10 characters'); end if;
for i in 1..9 loop
ch := ascii(substr(:new.col, i, 1)); if ch < 48 or ch > 57 then raise_application_error (-20000, 'The first 9 characters must be numeric'); end if;
I didnt try this trigger, but it should show you the way to solve this problem
Let me know if this will help you, please.
Regards, Stephan
--
Dipl.-Inf. (FH) Stephan Born | beusen Consulting GmbH fon: +49 30 549932-0 | Landsberger Allee 392 fax: +49 30 549932-29 | 12681 Berlin mailto:stephan.born_at_beusen.de | Germany --------------------------------------------------------------- PGP-Key verfügbar | PGP-Key available ---------------------------------------------------------------Received on Mon Nov 29 1999 - 02:19:47 CST