 |
|
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.
Furthermore, ORA-00972 can be caused by column,
alias, or table names which are too long.
|
