ORA-00972 identifier is too long alias column name

Question

i have a query like :

SELECT column as averyveryveryverylongalias (more than 30 characters)
   FROM Table_name

it returns the error ORA-00972 identifier is too long , is there any tip to make it work without making the alias shorter?

Thanks

Answer 1

I'm using Argos reporting system as a front end and Oracle in back. I just encountered this error and it was caused by a string with a double quote at the start and a single quote at the end. Replacing the double quote with a single solved the issue.

Answer 2

The error is also caused by quirky handling of quotes and single qutoes. To include single quotes inside the query, use doubled single quotes.

This won't work

select dbms_xmlgen.getxml("Select ....") XML from dual;

or this either

select dbms_xmlgen.getxml('Select .. where something='red'..') XML from dual;

but this DOES work

select dbms_xmlgen.getxml('Select .. where something=''red''..') XML from dual;

Answer 3

The object where Oracle stores the name of the identifiers (e.g. the table names of the user are stored in the table named as USER_TABLES and the column names of the user are stored in the table named as USER_TAB_COLUMNS), have the NAME columns (e.g. TABLE_NAME in USER_TABLES) of size Varchar2(30)...and it's uniform through all system tables of objects or identifiers --

 DBA_ALL_TABLES         ALL_ALL_TABLES        USER_ALL_TABLES
 DBA_PARTIAL_DROP_TABS  ALL_PARTIAL_DROP_TABS USER_PARTIAL_DROP_TABS
 DBA_PART_TABLES        ALL_PART_TABLES       USER_PART_TABLES 
 DBA_TABLES             ALL_TABLES            USER_TABLES           
 DBA_TABLESPACES        USER_TABLESPACES      TAB
 DBA_TAB_COLUMNS      ALL_TAB_COLUMNS         USER_TAB_COLUMNS 
 DBA_TAB_COLS         ALL_TAB_COLS            USER_TAB_COLS 
 DBA_TAB_COMMENTS     ALL_TAB_COMMENTS        USER_TAB_COMMENTS 
 DBA_TAB_HISTOGRAMS   ALL_TAB_HISTOGRAMS      USER_TAB_HISTOGRAMS 
 DBA_TAB_MODIFICATIONS  ALL_TAB_MODIFICATIONS USER_TAB_MODIFICATIONS 
 DBA_TAB_PARTITIONS   ALL_TAB_PARTITIONS      USER_TAB_PARTITIONS

Answer 4

No, prior to Oracle version 12.2, identifiers are not allowed to exceed 30 characters in length. See the Oracle SQL Language Reference.

However, from version 12.2 they can be up to 128 bytes long. (Note: bytes, not characters).

Answer 5

If you have recently upgraded springboot to 1.4.3, you might need to make changes to yml file:

yml in 1.3 :

jpa: 
  hibernate: 
    namingStrategy: org.hibernate.cfg.EJB3NamingStrategy

yml in 1.4.3 :

jpa: 
  hibernate: 
    naming: physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Answer 6

As others have referred, names in Oracle SQL must be less or equal to 30 characters. I would add that this rule applies not only to table names but to field names as well. So there you have it.