Best practice : use double quotes, even in DBMS_STATS

Whenever you create a table, it is better to use double quotes to avoid invalid identified.

ERROR at line 1:
ORA-00903: invalid table name

SQL> CREATE TABLE "/XXX"(x number);

Table created.

Even in DBMS_STATS you should use double quotes

SQL> exec dbms_stats.gather_table_stats(user,'/XXX')
BEGIN dbms_stats.gather_table_stats(user,'/XXX'); END;

ERROR at line 1:
ORA-20001: /XXX is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1

SQL> exec dbms_stats.gather_table_stats(user,'"/XXX"')

PL/SQL procedure successfully completed.

It is also a good practice to not use table name like “/XXX”, “FROM” or “ROWID”. But if you use dynamic SQL, be sure your code does not bug on invalid identifier.

It is pretty seldom that Oracle introduces new reserved words, as it breaks code, so if you do


you can be pretty sure that neither MYTABLE nor X will be reserved in 12c or 13c…

4 Replies to “Best practice : use double quotes, even in DBMS_STATS”

  1. almost ten years ago I had the idea to create some tables based on SAP definitions and with the corresponding names including a leading zero – like “0MATERIAL”. It is one of the few naming decisions I will never forget – and it was certainly not one of my better ideas…

Leave a Reply

Your email address will not be published.