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.

SQL> CREATE TABLE /XXX(x number);
CREATE TABLE /XXX(x number)
*
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

CREATE TABLE MYTABLE(x number);

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

4 thoughts on “Best practice : use double quotes, even in DBMS_STATS”

  3. Martin Preiss

    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…

Comments are closed.