0

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?

  • 1
    I know of no way to make oracle enforce this, but you could always audit ddl and review. Or create a procedure to run on a daily basis and report any names you want to disallow. It would not proactivly prevent, but at least allow you to catch their creation in a timely fashion and quickly 're-educate' the creator. One might ask why people are allowed to create tables without a DBA review in the first place. – EdStevens Oct 30 '18 at 21:32
  • "allowed to create tables without a DBA review" - because they are Java developers and the magic of Hibernate takes care of all the "unnecessary maintenance" of database management :) – Phil Oct 31 '18 at 10:05
1

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.

  • I assumed that reserved words and keywords were the same but from docs.oracle.com/database/121/SQLRF/ap_keywd002.htm#SQLRF55622 I see that there is a clear difference. Still the use of keywords "may lead to unpredictable results". In my situation the only thing that jumps out (with the limited analysis of the customer site) is that the table has a column called "password" and I assumed that this could cause an error. – Phil Oct 31 '18 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.