Using Oracle 7.3.4 on UnixWare 7.0.1
Is it possible to use/call a function in a check constraint???
After defining the function at the bottom (which works when called from
sqlplus or plsql),
given a table Person with column PersonType,
none of the following will work.
alter table Person add constraint PerPersonTypeValueCheck
check ( PerValidPersonType( PersonType ) != 0 );
alter table Person add constraint PerPersonTypeValueCheck
check ( select PerValidPersonType( PersonType ) from dual );
Any suggestions as to how to achieve the equivalent in some other
fashion???
- PerValidPersonType()
--
- WARNING: This function must be declared before any used in any check
or trigger constraint..
--
- This function returns:
--
prompt create function PerValidPersonType
create or replace function PerValidPersonType ( s in varchar2 ) return
number
is
retval number := 1;
slength integer := length( s );
begin
- retval := 1;
- slength := length( s );
- NULL value is valid
if slength IS NULL
then
return 1;
end if;
- s may contain any combination of the char below.
- thus hitting a char position with something else implies tilt
for i in 1..slength
loop
if SUBSTR(s,i,1) NOT in ( 'c', 'd', 'o', 'p', 's' )
then
retval := 0;
exit; -- exit loop. we found a reject.
end if;
end loop;
return retval;
end PerValidPersonType;
/
Thanks
R.Parr
Temporal Arts
Received on Thu Mar 04 1999 - 11:53:12 CST
