By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE InvalidCharacter ( invalidchar ) AS
SELECT 'A' FROM DUAL UNION ALL
SELECT 'C' FROM DUAL UNION ALL
SELECT 'E' FROM DUAL;
3 rows affected
CREATE FUNCTION invalid_checker(inword IN varchar2)
RETURN boolean
IS
hasinvalid INTEGER ;
BEGIN
SELECT 1
INTO hasInvalid
FROM (
SELECT SUBSTR(inword, LEVEL, 1) AS ch
FROM DUAL
CONNECT BY LEVEL <= LENGTH(inword)
) c
INNER JOIN InvalidCharacter i
ON ( c.ch = i.invalidchar )
FETCH FIRST ROW ONLY;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;
/
DECLARE
words SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
'BDF',
'ACE',
'BDE',
'ace'
);
BEGIN
FOR i IN 1 .. words.COUNT LOOP
IF invalid_checker( words(i) ) THEN
DBMS_OUTPUT.PUT_LINE( words(i) || ' invalid' );
ELSE
DBMS_OUTPUT.PUT_LINE( words(i) || ' valid' );
END IF;
END LOOP;
END;
/
1 rows affected
dbms_output:
BDF valid
ACE invalid
BDE invalid
ace valid