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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] ORA-00972 identifier is too long alias column name

Examples related to sql

Examples related to oracle

Examples related to alias