Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Srikanth.

Asked: September 28, 2010 - 2:29 pm UTC

Last updated: June 12, 2019 - 1:14 am UTC

Version: 10.2.1

Viewed 100K+ times! This question is

You Asked

I have a question about "Identifier too long" error. I understand if I am trying to create a column name that is too long and oracle complains on it. but if I do a select and alais it my own text, why is this a problem ?

Here is a sample:


 SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TEST_ID                                   NOT NULL NUMBER(38)
 SEEKER_ID                                 NOT NULL VARCHAR2(11)
 TEST_DATE                                          DATE
 TEST_TYPE_CD                              NOT NULL NUMBER(38)
 OFFICE_ID                                          VARCHAR2(6)

SQL> select count(*) from test;

  COUNT(*)                                                                      
----------                                                                      
    1                                                                      

SQL> select distinct office_id from test;

OFFICE                                                                          
------                                                                          
KYHHHH                                                                          

1 row selected.

SQL> select distinct office_id "my office name is very long..What to do" from test;
select distinct office_id "my office name is very long..What to do" from test
                          *
ERROR at line 1:
ORA-00972: identifier is too long 


SQL> spool off;



Thanks,

and Connor said...

because an identifier is an identifier is an identifier


create view v as select distinct office_id "my office name is very long..What to do" from
test;


that would create a view with an identifier way too long.

Also - every client API on the planet is expecting identifiers that conform to the documented limits. You would break "describe" in every client API with that (if it worked). Pretty much every tool would crash and burn with such an identifier.

Changing the length will probably ultimately happen - but it is going to be painful when it does - for a long time.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ORA-00972 Identifier Too Long

Srikanth Turlapati, September 29, 2010 - 3:26 pm UTC

Thanks for the quick response.
But All I want to do is select from the database and give it a meaningful name [I might export this to Excel or send this to some client via Email, it doesn't matter]. I can understand if Database has restriction while putting it in, but while displaying records I was hoping it should not have any problem. But Oracle can be so stingy sometimes :-)

Come to think of it, why Oracle has a 30 character limit on object names [I know it should have some limit, but why 30 character limit] who came up with that number? just curious
Tom Kyte
September 29, 2010 - 4:19 pm UTC

the problem is the client code - for over 30 years - all identifiers have been 30 characters. Can you imagine what would happen to client code if that all of a sudden changed, do you see how many things would suddenly BREAK completely?

30 is more than the standard calls for - it was set by the ANSI/ISO committee a long long time ago.

why would it break?

karteek, October 29, 2010 - 4:06 am UTC

Tom,

You said "do you see how many things would suddenly BREAK completely?"

Why would existing apps would break if the limit is pushed to, say 50. I guess, I did not get your point correctly. Can you help me understand? My feeling is existing (coded with limit 30) would continue as-is, and new code will choose bigger length - and why would there be any conflict?
Tom Kyte
November 01, 2010 - 5:09 am UTC

tell you what.

take your MOST USED application/set of data. the most important set of data ever.

Now, change its attributes in some way shape or form (an attribute name is just data in a table to me). Go ahead, take the most common data element in your data model and alter it from a 30 character field to a 50 character field and make sure to pop in lots of 50 character values.

Now, sit back and watch your applications EXPLODE.


think about it. How many applications *query* the data dictionary (most of them do). Your 'frameworks' do, APEX does, almost everything does at some point.

Matt Van Mter, July 10, 2013 - 1:58 pm UTC

I ran into this oracle error when converting a valid/working mysql query to oracle 10.x. Nearly every article or KB regarding ORA 00972 indicates this error is due to a column identifier in excess of 30 characters.

I found that is NOT always the case! In my case, my SELECT statements had a WHERE clause like this:
WHERE
Field_Name="A String of text goes here"

The problem was not the length of the Field_Name, but rather due to the fact that I mistakenly used double quotes instead of single quotes in my query. After I modified my query to appear as shown below, it worked:
WHERE
Field_Name='A String of text goes here'

Hopefully other people will see this and be saved some of the pain that I went through!

The URL in question is http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2684299700346560644
Tom Kyte
July 16, 2013 - 3:37 pm UTC

yes it was due to a max identifier length.


when you use "A string of text goes here" - that was an IDENTIFIER. It is an ansi standard way of quoting an identifier - allowing you to use mixed case and other special characters. for example:



ops$tkyte%ORA11GR2> select * from dual where "not valid" = 'x';
select * from dual where "not valid" = 'x'
                         *
ERROR at line 1:
ORA-00904: "not valid": invalid identifier




there is no column "not valid" in the dual table so it failed. It could not resolve that identifier. That is not a string, it is an IDENTIFER.


ops$tkyte%ORA11GR2> create table t ( "not valid" int );

Table created.

ops$tkyte%ORA11GR2> select * from t where "not valid" = 0;

no rows selected

ops$tkyte%ORA11GR2>




so what you learned is that in SQL there are strings, they are enclosed in a quote, and there are identifiers - they are optionally enclosed in double quotes.

Saritha, July 10, 2013 - 4:05 pm UTC

this is very helpful as matt was saying it is not always with the identifier length the query errored out but sometimes with " double quotes also it shows the same error message.
Tom Kyte
July 16, 2013 - 3:39 pm UTC

and that is because anything in double quotes IS AN IDENFIFIER!!!!! not a string.

How about MSSQL databases over db link?

√ėyvind Rogne, August 22, 2017 - 11:55 am UTC

Im extracting data using a database link from a MSSQL database where one table is 31 characters long. The MSSQL table is case sensitive, so I am forced to use the "" characters.

select * from "ThisLongTableNameIsTooLongToUse"@myDBLink

returns

ORA-00972: identifier is too long

If I am reading this article correctly, there is no way to extract data from the table?
Chris Saxon
August 22, 2017 - 1:26 pm UTC

There are a couple of workarounds. MOS note 1433102.1 gives the following:

In 11.2 version, it is possible to select a remote table with columns with more than 30 characters if you prefix the table with the owner (in our example, owner is "dbo").
But if you select only the long column, you'll get the error ORA-00972.

To give you an example:
SQL> desc "dbo"."table_with_long_column"@dg112msql;
Name Null? Type
----------------------------------------- -------- ----------------------------
TESTING_THIRTY_CHARACTERS_DG4GTW CHAR(30)

SQL> select * from "dbo"."table_with_long_column"@dg112msql;

TESTING_THIRTY_CHARACTERS_DG4G
------------------------------
abc
def

SQL> select "TESTING_THIRTY_CHARACTERS_DG4MSQL" from "dbo"."table_with_long_column"@dg112msql;
select "TESTING_THIRTY_CHARACTERS_DG4MSQL" from "dbo"."table_with_long_column"@dg112msql
*
ERROR at line 1:
ORA-00972: identifier is too long


Or you can upgrade to 12.2 which supports 128 byte long identifiers ;)

Help!?

A reader, January 09, 2018 - 9:43 am UTC

Could you PLEASE explain to me why this select command is too long!? Where is it!? Thank you!

if (OraCmd.Parameters.Contains("@p1")) OraCmd.Parameters["@p1"].Value = tbNumber.Text;
else OraCmd.Parameters.Add("@p1", tbNumber.Text);
OraCmd.CommandText = @"select
uadism.num_dism as ""allowanceNumber"",
uadism.dat_dism as ""allowanceDate"",
uadism.num_reg as ""bIdentifier"",
uadism.nam_cust as ""bName"",
uadisd.lin_dism as ""SequenceNumber,
uadisd.num_invm as ""InvoiceNumber"",
uadisd.dat_invm as ""InvoiceDate"",
uadisd.nam_item as ""Description"",
uadisd.qty_item as ""Quantity"",
codd.content as ""Unit"",
uadisd.prs_dism as ""UnitPrice"",
uadisd.amt_dism as ""Amount"",
uadisd.tax_dism as ""Tax"",
uadisd.tax_type as ""TaxType""
from uadism,uadisd,deld,codd
where
uadism.num_dism = :p1 and
uadism.num_dism = uadisd.num_dism and
deld.num_del = uadisd.num_del and
deld.lin_del = uadisd.lin_del and
codd.code_id = 'CODUNI' and
codd.code = deld.unt_poc
order by uadism.num_dism,uadisd.lin_dism";
Connor McDonald
January 10, 2018 - 12:11 am UTC

You are missing quotes after SequenceNumber

Zach Braff, June 11, 2019 - 8:38 pm UTC

Thank you for the explanations. I recently upgraded from 12.1 to 18.5, but I still can't use identifiers (column names) greater than 30 characters. (I don't really need to, but it got me wondering why)

SQL> create table testid ("LongestBillingBillProjectRecordStagingKey" varchar2);
create table testid ("LongestBillingBillProjectRecordStagingKey" varchar2)
                     *
ERROR at line 1:
ORA-00972: identifier is too long


SQL> create table testid (LongestBillingBillProjectRecordStagingKey varchar2);
create table testid (LongestBillingBillProjectRecordStagingKey varchar2)
                     *
ERROR at line 1:
ORA-00972: identifier is too long


SQL> desc dba_tab_columns;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(128)
 TABLE_NAME                                NOT NULL VARCHAR2(128)
 COLUMN_NAME                               NOT NULL VARCHAR2(128)
 DATA_TYPE                                          VARCHAR2(128)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(128)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                          NUMBER
 DEFAULT_LENGTH                                     NUMBER
 DATA_DEFAULT                                       LONG
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(32767)
 HIGH_VALUE                                         RAW(32767)
[...]

Connor McDonald
June 12, 2019 - 1:14 am UTC

This from 18.0

SQL> create table testid ("LongestBillingBillProjectRecordStagingKey" varchar2(10));

Table created.


Check your 'compatible' parameter. It might still be set to a low value