Saturday, March 28, 2020

ORA-00972: identifier is too long


In Oracle, the maximum length for the name of an object (Table, View, Procedure, Package, Function, Materialized View etc.) is 30 characters. Likewise, the name of a column of a table or view should also not exceed 30 characters. In the following I am providing a few examples where we will see ORA-00972 error.

Creating a table with a name longer than 30 characters.
SQL> create table MYTABLENAMEISLONGERTHANTHIRTYCHARACTERS(id number);
create table MYTABLENAMEISLONGERTHANTHIRTYCHARACTERS (id number)
             *
ERROR at line 1:
ORA-00972: identifier is too long

Creating a table with a column with column name exceeding 30 characters.
SQL> create table T (MYCOLUMNNAMEISLONGERTHANTHIRTYCHARACTERS number);
create table T(MYCOLUMNNAMEISLONGERTHANTHIRTYCHARACTERS number)
               *
ERROR at line 1:
ORA-00972: identifier is too long

Creating a procedure having name with more than 30 characters long.
SQL> create or replace procedure MYPROCEDURENAMEISLONGERTHANTHIRTYCHARACTERS is
  2  begin;
  3  1:=2;
  4  end;
  5  /
create or replace procedure P234567890123456789012345678901 is
                            *

Creating a view having name more than 30 characters long.
SQL> create or replace view MYVIEWNAMEISLONGERTHANTHIRTYCHARACTERS as select * from dba_tables;
create or replace view MYVIEWNAMEISLONGERTHANTHIRTYCHARACTERS as select * from dba_tables
                       *
ERROR at line 1:
ORA-00972: identifier is too long

In a SQL or a DML, if you mention a table, view or column name longer than 30 characters, or an alias for them that is longer than 30 character, this would also return same error message.
SQL> select MYCOLUMNNAMEISLONGERTHANTHIRTYCHARACTERS from dual;
select MYCOLUMNNAMEISLONGERTHANTHIRTYCHARACTERS from dual
       *
ERROR at line 1:
ORA-00972: identifier is too long


SQL> select id from MYTABLENAMEISLONGERTHANTHIRTYCHARACTERS;
select id from MYTABLENAMEISLONGERTHANTHIRTYCHARACTERS
               *
ERROR at line 1:
ORA-00972: identifier is too long


No comments:

Post a Comment

Popular Posts - All Times