95

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?

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
mcha
  • 2,938
  • 4
  • 25
  • 34
  • Hi, in my case ._ after the dot is longer that 30 character, Can you solved your problem? How? – erod Dec 15 '21 at 16:10

6 Answers6

128

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

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 31
    out of curiosity, does anyone know where this limitation comes from, and how come the limit has never been relaxed ? It really strikes me that none of the million-dollar paying customers of Oracle *ever* requested such a feature (now don't get me wrong, I am pretty sure there are some deep technical reason, but still..) – phtrivier Jun 21 '10 at 14:38
  • 15
    Okay, I could have asked SO : http://stackoverflow.com/questions/1378133/why-are-oracle-table-column-index-names-limited-to-30-characters – phtrivier Jun 21 '10 at 14:40
  • I also encountered this situation. I have a table column with name length=32. But why on earth is a table column name allowed to be more than 30 characters, while at the same time an identifier is not? These limits should go hand-in-hand, right? How can this situation arise at all? – Vering May 26 '14 at 06:23
  • @Vering Is it more than 30 characters or more than 30 bytes? There is a weird bug where an identifier can be slightly more than 30 bytes if the last character is multi-byte. – Jon Heller Aug 12 '15 at 18:32
  • @JonHeller: I'm quite sure it was 30 / 32 characters – Vering Aug 14 '15 at 18:30
  • I guess this is a legacy issue. To avoid backward compatible issue, it's not updated. But 30 is too short today. – Leon Feb 05 '18 at 03:54
13

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;
LarsTech
  • 80,625
  • 14
  • 153
  • 225
mike cummings
  • 131
  • 1
  • 2
5

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
Cyril Gandon
  • 16,830
  • 14
  • 78
  • 122
Saptarshi
  • 125
  • 2
  • 3
5

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.

John
  • 51
  • 1
  • 1
0

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
NiTiN
  • 1,022
  • 1
  • 16
  • 25
-1

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.

GreatApps4YOU
  • 11