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?
-
Hi, in my case <tableName>.<propetyName>_<columnName> after the dot is longer that 30 character, Can you solved your problem? How?erod– erod2021-12-15 16:10:11 +00:00Commented Dec 15, 2021 at 16:10
Add a comment
|
6 Answers
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).
6 Comments
phtrivier
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
Okay, I could have asked SO : stackoverflow.com/questions/1378133/…
Vering
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?
Jon Heller
@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.
Vering
@JonHeller: I'm quite sure it was 30 / 32 characters
|
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;
1 Comment
mike cummings
The markup changed my example it should be something equals single quote single quote red single quote single quote
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
Comments
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.
Comments
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
Comments
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.
