ORA-00972标识符别名列名太长

我有一个疑问是这样的:

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

它返回错误 ORA-00972标识符太长，是否有任何提示使它工作而不使别名更短？

谢谢

305731 次浏览
小开
最佳答案

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).

小开

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
小开

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.

小开

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;
小开

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.

小开

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

提交答案