Oracle 12.2 New Feature: Longer object/column names

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;