CREATE OR REPLACE PROCEDURE is_it_reserved (
word_in IN VARCHAR2,
show_error_in IN BOOLEAN DEFAULT FALSE)
AUTHID DEFINER
IS
c_view CONSTANT VARCHAR2 (1000)
:= q'[select keyword from v$reserved_words
where keyword = upper(:wrd) and reserved = 'Y']' ;
c_block CONSTANT VARCHAR2 (1000)
:= 'declare '
|| word_in
|| ' integer; begin '
|| word_in
|| ' := 1; end;' ;
l_word VARCHAR2 (128);
BEGIN
BEGIN
EXECUTE IMMEDIATE c_view INTO l_word USING word_in;
DBMS_OUTPUT.put_line (word_in || ' is a reserved word in PL/SQL (v)');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (
word_in || ' is NOT a reserved word in PL/SQL (v)');
WHEN OTHERS
THEN
/* No access to view, so go with dynamic PL/SQL block */
EXECUTE IMMEDIATE c_block;
DBMS_OUTPUT.put_line (
word_in || ' is NOT a reserved word in PL/SQL (b)');
END;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (word_in || ' is a reserved word in PL/SQL (b)');
IF show_error_in
THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END IF;
END;
Procedure created.
Strut Your Stuff, IS_IT_RESERVED!
BEGIN
is_it_reserved ('SELECT');
is_it_reserved ('BEGIN');
is_it_reserved ('NO_DATA_FOUND');
END;
SELECT is a reserved word in PL/SQL (b)
BEGIN is a reserved word in PL/SQL (b)
NO_DATA_FOUND is NOT a reserved word in PL/SQL (b)