/*
this was created from v$reserved words using create table as select.
my_reserved_words is just being used to demo this use case. The real
function will be in a package and use v$reserved_words.
*/

  CREATE TABLE MY_RESERVED_WORDS 
   (	"KEYWORD" VARCHAR2(30 BYTE),  
	"LENGTH" NUMBER,  
	"RESERVED" VARCHAR2(1 BYTE),  
	"RES_TYPE" VARCHAR2(1 BYTE),  
	"RES_ATTR" VARCHAR2(1 BYTE),  
	"RES_SEMI" VARCHAR2(1 BYTE),  
	"DUPLICATE" VARCHAR2(1 BYTE),  
	"CON_ID" NUMBER 
   )

Insert into MY_RESERVED_WORDS (KEYWORD,LENGTH,RESERVED,RES_TYPE,RES_ATTR,RES_SEMI,DUPLICATE,CON_ID) values ('SELECT',6,'Y','N','N','N','N',0)

commit

/*
what i'm trying to accomplish. when a string is passed, does it contain
a keyword? if so, return true if it does not contain a keyword return false.
we will assume the string can and will be passed in mixed case. therefore
we want to put an upper function on the string. This function always seems
to return false.
*/
create or replace function fReserved(sString IN VARCHAR2) RETURN BOOLEAN IS 
iCnt INTEGER; -- this is a count of the number of rows returned.  
BEGIN 
	SELECT count(*) 
	INTO iCnt 
	FROM my_reserved_words 
	WHERE UPPER(sString) LIKE '''%'||'''||keyword ||'''||'%'''; 
	-- if iCnt > 0 then we matched a reserved word in the string 
	-- return true, otherwise return false. 
	IF iCnt > 0 THEN 
		RETURN TRUE; 
	ELSE 
		RETURN FALSE; 
	END IF; 
END fReserved; 

DECLARE 
BEGIN 
	-- test simple SELECT keyword. this fails because of 
	-- the appended '%' on each side of keyword in the function. 
	IF fReserved('SELECT') THEN 
		SYS.DBMS_OUTPUT.PUT_LINE('PASSED SELECT KEYWORD'); 
	ELSE 
		SYS.DBMS_OUTPUT.PUT_LINE('FAILED SELECT KEYWORD'); 
	END IF; 
	-- test lower case select keyword.  
	IF fReserved('select') THEN 
		SYS.DBMS_OUTPUT.PUT_LINE('PASSED select KEYWORD'); 
	ELSE 
		SYS.DBMS_OUTPUT.PUT_LINE('FAILED select KEYWORD'); 
	END IF; 
	-- test partial SEL this should return false.  
	-- we only want to match full keywords. 
	-- this passes 
	IF NOT fReserved('SEL') THEN 
		SYS.DBMS_OUTPUT.PUT_LINE('PASSED SEL KEYWORD'); 
	ELSE 
		SYS.DBMS_OUTPUT.PUT_LINE('FAILED SEL KEYWORD'); 
	END IF; 
	-- test embedded SELECT keyword. i'm not sure why this  
	-- test fails. 
	IF fReserved('THIS IS A TEST OF SELECT EMBEDDED') THEN 
		SYS.DBMS_OUTPUT.PUT_LINE('PASSED EMBEDDED SELECT KEYWORD'); 
	ELSE 
		SYS.DBMS_OUTPUT.PUT_LINE('FAILED EMBEDDED SELECT KEYWORD'); 
	END IF; 
	-- test embedded select keyword. i'm not sure why this 
	-- test fails. 
	IF fReserved('THIS IS A TEST OF select EMBEDDED') THEN 
		SYS.DBMS_OUTPUT.PUT_LINE('PASSED EMBEDDED select KEYWORD'); 
	ELSE 
		SYS.DBMS_OUTPUT.PUT_LINE('FAILED EMBEDDED select KEYWORD'); 
	END IF; 
	-- test embedded sel. this should return false. we only  
	-- want to match full keywords. this test passes. 
	IF NOT fReserved('THIS IS A TEST OF sel EMBEDDED') THEN 
		SYS.DBMS_OUTPUT.PUT_LINE('PASSED EMBEDDED sel KEYWORD'); 
	ELSE 
		SYS.DBMS_OUTPUT.PUT_LINE('FAILED EMBEDDED sel KEYWORD'); 
	END IF; 
END;