ED HAYES

We have a problem at work which required us to only allow alphanumeric values in a DFF.  No spaces, punctuation, or special characters.  Here is what I came up with:

Function (WordPress will change quotes to smart quotes, you will need to change back)

FND PLSQL “declare

l_value varchar2( 150 ) := :!value ;
l_valid NUMBER := NULL ;
BEGIN
SELECT (LENGTH(TRIM(TRANSLATE(REPLACE(l_value,’ ‘,’.’), ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’, ‘ ‘))))
INTO l_valid
FROM dual;
IF (l_valid IS NOT NULL) THEN
fnd_message.set_name( ‘FND’, ‘FND_GENERIC_MESSAGE’ ) ;
fnd_message.set_token( ‘MESSAGE’, ‘Value must be alpha-numeric’ );
fnd_message.raise_error ;
END IF ;
END;
“

Regular Expression Version:

FND PLSQL “declare
l_value varchar2( 150 ) := :!value ;
BEGIN
IF REGEXP_SUBSTR(l_value, ‘^[a-zA-Z0-9]+$’) IS NULL THEN
fnd_message.set_name( ‘FND’, ‘FND_GENERIC_MESSAGE’ ) ;
fnd_message.set_token( ‘MESSAGE’, ‘(‘||l_value||’) must be alphanumeric.’);
fnd_message.raise_error ;
END IF ;
END;
“