 |
|
ORA-00972: identifier is too long tips
Oracle Error Tips by Burleson Consulting
|
Oracle docs note this about ORA-00972:
ORA-00972:
identifier is too long
-
Cause:
An identifier with more than 30
characters was specified.
-
Action:
Specify at most 30 characters.
Many users find themselves encountering ORA-00972 when
attempting to access a table that has a large column length.
MOSC offers information regarding ORA-00972
in which trying to
get data from DBMS tables with 31 or more characters in the column name,
throws ORA-00972. This can happen even if the
ORA_OBJECT_NAME domain is defined as
VARCHAR(30) as opposed to VARCHAR(31) in
RDB_PREPARE_NATCONN.SQL, such as:
Domain name ORA_OBJECT_NAME is defined as VARCHAR(31) (default):
SQL> SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='JTAB';
COLUMN_NAME
------------------------------
ID
C123456789012345678901234567890 <--- 31 chars
SQL> SELECT * FROM JTAB;
SELECT * FROM JTAB
*
ERROR at line 1:
ORA-00972: identifier is too long
SQL> DESCRIBE JTAB
ERROR:
ORA-00972: identifier is too long
Here, though the
domain name ORA_OBJECT_NAME is stated as
VARCHAR(30), such as is commonly done in accordance with a common workaround,
the queries still fail, even if the field does truncate to 10 character
correctly.
SQL> SELECT COLUMN_NAME FROM ALL_TABL_COLUMNS WHERE TABLE_NAME = 'JTAB';
COLUMN_NAME
------------------------------
ID
C12345678901234567890123456789 <--- 30 chars
SQL> SELECT * FROM JTAB;
SELECT * FROM JTAB
*
ERROR at line 1:
ORA-00972: identifier is too long
SQL> DESCRIBE JTAB
ERROR:
ORA-00972: identifier is too long
Keep in mind that
the reason ORA-00972 has been occurring is that most clients do not support 31
character names.
You can also be reminded
here that simple syntax
errors can be at the root of ORA-00972.
Furthermore, ORA-00972 can be caused by column,
alias, or table names which are too long.
|
