System-level way to enforce that reserved words may not be used for Oracle column names

oraclereserved-word

Is there a way in Oracle 12c to enforce that reserved words / keywords may not be used as column names? I am aware that it is not good practice to use keywords in this way. However from what we have observed, tables are able to be created with a column name of the reserved word "password" in our local Oracle, but the customer found that the table was was not being created on their Oracle environment.

A complication here is that the tables are being created by a Java application which uses Hibernate to create the tables and these are not simply DDL statements. I have seen the related question: Oracle: Add Reserved Word as Column Name and I realise that the use of reserved words is permitted inside quotes, but I was wondering is there an system level "strict" mode that prevents the creation of tables with reserved words?

Best Answer

You could create a system trigger like this:

create or replace trigger no_reserved_words
   BEFORE CREATE OR ALTER ON SCHEMA
DECALRE

  CURSOR Cols IS
  SELECT r.*
  FROM V$RESERVED_WORDS r 
      JOIN ALL_TAB_COLUMNS ON keyword = UPPER(column_name)
  where RESERVED = 'Y'
      and owner = ora_dict_obj_owner
      and table_name = ora_dict_obj_name;

BEGIN
   IF ora_dict_obj_type = 'TABLE' THEN
      FOR aCol in Cols LOOP
         RAISE_APPLICATION_ERROR(-20010, aCol.keyword ||' not allowed as column name');    
      END LOOP;
   END IF;
END;

BEGIN

However, there are several problems. As user with ADMINISTER DATABASE TRIGGER system privilege (e.g. DBA) ignores the RAISE_APPLICATION_ERROR, it he can do it anyway.

Another problem is: What so you consider as "reserved word"? Check this example:

SELECT *
FROM V$RESERVED_WORDS r 
WHERE keyword IN ('PASSWORD','NAME', 'DATE', 'SYSDATE')


+-------------------------------------------------------------+
|KEYWORD |LENGTH|RESERVED|RES_TYPE|RES_ATTR|RES_SEMI|DUPLICATE|
+-------------------------------------------------------------+
|NAME    |4     |N       |N       |N       |N       |N        |
|SYSDATE |7     |N       |N       |N       |Y       |N        |
|PASSWORD|8     |N       |N       |N       |N       |N        |
|DATE    |4     |Y       |N       |N       |N       |N        |
+-------------------------------------------------------------+

You see DATE is clearly reserved, but PASSWORD has the same attributes like NAME which should be no problem, I assume.

Related Question