Hi,

Maybe you have wanted to use longer object names to be more descriptive. The Oracle RDBMS now supports up to 128 positions for both object and column names! The current naming length convention is 30. The other naming rules still apply such as:

  1. Starts with a character
  2. No spaces
  3. Underbars are acceptable
  4. Special characters are too…just be careful out there!

Please don’t get carried away. I know using Toad’s schema browser, I can filter objects by short acronyms perhaps.

We don’t need index names like ‘Index_on_EMP_table_drop_after_1_use_as_this_was_just_a_test’…and the index never gets renamed or dropped. I can see this happening.

So Oracle12.2 on, you can give your tables/indexes/materialized views/columns/PLSQL packages/procedures/functions much longer and more meaningful names.

Oracle Employee Chris Saxon shared this code with us at OOW16…to display indexes associated with tables and crop the names shorter to fit on your display.

Select TABLE_NAME,

           Listagg(INDEX_NAME, ‘,’ on overflow truncate)

            within group

           (order by INDEX_NAME) INDS

From USER_INDEXES

Group by TABLE_NAME;

Oracle employee Connor McDonald expanded on the topic with this code that would prevent longer object names if you do not have DBA privileges. Perhaps you can use this code to modify/monitor/enforce your company naming conventions.

create or replace trigger ddl_trigger

before create or alter on demo.SCHEMA

declare

   l_obj   varchar2(128);

   l_dba   int;

begin

   l_obj := ora_dict_obj_name;

 

   select count(*)

   into l_dba

   from dba_role_privs

   where grantee = USER

   and granted_role = 'DBA';

 

   if l_dba = 0 and length(l_obj) > 30 then

      raise_application_error(-20000,'Identifier "'||l_obj||'" is too long');

   end if;

end;

 

Dan Hotka

Author/Instructor/CEO